Функции анализа временных рядов

Анализ временных рядов в ClickHouse можно выполнять с использованием стандартных агрегатных и оконных функций SQL. При работе с данными временных рядов вы обычно сталкиваетесь с тремя основными типами метрик:

Метрики-счётчики, которые монотонно растут с течением времени (например, просмотры страниц или общее количество событий)
Метрики типа gauge, которые представляют собой моментные измерения и могут как увеличиваться, так и уменьшаться (например, загрузка CPU или температура)
Гистограммы, которые берут выборки наблюдений и распределяют их по корзинам (например, длительности запросов или размеры ответов)

Распространённые шаблоны анализа для этих метрик включают сравнение значений между периодами, вычисление накопительных итогов, определение скоростей изменений и анализ распределений. Этого всего можно добиться с помощью комбинаций агрегатных функций, оконных функций, таких как sum() OVER, и специализированных функций, таких как histogram().

Изменения по периодам

При анализе временных рядов нам часто нужно понимать, как значения изменяются между временными периодами. Это важно как для метрик-датчиков (gauge), так и для метрик-счётчиков (counter). Оконная функция lagInFrame позволяет получить значение за предыдущий период, чтобы вычислить эти изменения.

Следующий запрос демонстрирует это, вычисляя помесячные изменения в количестве просмотров страницы Википедии «Weird Al» Yankovic. Столбец trend показывает, увеличился ли трафик (положительные значения) или уменьшился (отрицательные значения) по сравнению с предыдущим днём, что помогает выявлять необычные всплески или падения активности.

SELECT
    toDate(time) AS day,
    sum(hits) AS h,
    lagInFrame(h) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS p,
    h - p AS trend
FROM wikistat
WHERE path = '"Weird_Al"_Yankovic'
GROUP BY ALL
LIMIT 10;

┌────────день─┬────h─┬────p─┬─тренд─┐
│ 2015-05-01 │ 3934 │    0 │  3934 │
│ 2015-05-02 │ 3411 │ 3934 │  -523 │
│ 2015-05-03 │ 3195 │ 3411 │  -216 │
│ 2015-05-04 │ 3076 │ 3195 │  -119 │
│ 2015-05-05 │ 3450 │ 3076 │   374 │
│ 2015-05-06 │ 3053 │ 3450 │  -397 │
│ 2015-05-07 │ 2890 │ 3053 │  -163 │
│ 2015-05-08 │ 3898 │ 2890 │  1008 │
│ 2015-05-09 │ 3092 │ 3898 │  -806 │
│ 2015-05-10 │ 3508 │ 3092 │   416 │
└────────────┴──────┴──────┴───────┘

Накопленные значения

Метрики-счётчики естественным образом накапливаются со временем. Чтобы проанализировать этот накопительный рост, мы можем вычислить нарастающий итог с помощью оконных функций.

Следующий запрос демонстрирует это, используя выражение sum() OVER, которое создаёт нарастающий итог. Функция bar() обеспечивает наглядное отображение роста.

SELECT
    toDate(time) AS day,
    sum(hits) AS h,
    sum(h) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING) AS c,
    bar(c, 0, 50000, 25) AS b
FROM wikistat
WHERE path = '"Weird_Al"_Yankovic'
GROUP BY ALL
ORDER BY day
LIMIT 10;

┌────────day─┬────h─┬─────c─┬─b─────────────────┐
│ 2015-05-01 │ 3934 │  3934 │ █▉                │
│ 2015-05-02 │ 3411 │  7345 │ ███▋              │
│ 2015-05-03 │ 3195 │ 10540 │ █████▎            │
│ 2015-05-04 │ 3076 │ 13616 │ ██████▊           │
│ 2015-05-05 │ 3450 │ 17066 │ ████████▌         │
│ 2015-05-06 │ 3053 │ 20119 │ ██████████        │
│ 2015-05-07 │ 2890 │ 23009 │ ███████████▌      │
│ 2015-05-08 │ 3898 │ 26907 │ █████████████▍    │
│ 2015-05-09 │ 3092 │ 29999 │ ██████████████▉   │
│ 2015-05-10 │ 3508 │ 33507 │ ████████████████▊ │
└────────────┴──────┴───────┴───────────────────┘

Расчёт скорости

При анализе временных рядов часто полезно понимать частоту возникновения событий, то есть число событий в единицу времени. Этот запрос вычисляет частоту просмотров страниц в секунду, деля почасовые суммы на количество секунд в часе (3600). Столбчатая диаграмма помогает выявить часы с пиковым уровнем активности.

SELECT
    toStartOfHour(time) AS time,
    sum(hits) AS hits,
    round(hits / (60 * 60), 2) AS rate,
    bar(rate * 10, 0, max(rate * 10) OVER (), 25) AS b
FROM wikistat
WHERE path = '"Weird_Al"_Yankovic'
GROUP BY time
LIMIT 10;

┌────────────────time─┬───h─┬─rate─┬─b─────┐
│ 2015-07-01 01:00:00 │ 143 │ 0.04 │ █▊    │
│ 2015-07-01 02:00:00 │ 170 │ 0.05 │ ██▏   │
│ 2015-07-01 03:00:00 │ 148 │ 0.04 │ █▊    │
│ 2015-07-01 04:00:00 │ 190 │ 0.05 │ ██▏   │
│ 2015-07-01 05:00:00 │ 253 │ 0.07 │ ███▏  │
│ 2015-07-01 06:00:00 │ 233 │ 0.06 │ ██▋   │
│ 2015-07-01 07:00:00 │ 359 │  0.1 │ ████▍ │
│ 2015-07-01 08:00:00 │ 190 │ 0.05 │ ██▏   │
│ 2015-07-01 09:00:00 │ 121 │ 0.03 │ █▎    │
│ 2015-07-01 10:00:00 │  70 │ 0.02 │ ▉     │
└─────────────────────┴─────┴──────┴───────┘

Гистограммы

Один из популярных сценариев использования данных временных рядов — построение гистограмм на основе отслеживаемых событий. Предположим, мы хотим понять распределение количества страниц в зависимости от общего числа просмотров, учитывая только страницы с числом просмотров более 10 000. Мы можем использовать функцию histogram() для автоматического построения адаптивной гистограммы в зависимости от количества интервалов (bins):

SELECT
    histogram(10)(hits) AS hist
FROM
(
    SELECT
        path,
        sum(hits) AS hits
    FROM wikistat
    WHERE date(time) = '2015-06-15'
    GROUP BY path
    HAVING hits > 10000
)
FORMAT Vertical;

Row 1:
──────
hist: [(10033,23224.55065359477,60.625),(23224.55065359477,37855.38888888889,15.625),(37855.38888888889,52913.5,3.5),(52913.5,69438,1.25),(69438,83102.16666666666,1.25),(83102.16666666666,94267.66666666666,2.5),(94267.66666666666,116778,1.25),(116778,186175.75,1.125),(186175.75,946963.25,1.75),(946963.25,1655250,1.125)]

Затем мы можем использовать arrayJoin(), чтобы преобразовать данные, и bar(), чтобы визуализировать их:

WITH histogram(10)(hits) AS hist
SELECT
    round(arrayJoin(hist).1) AS lowerBound,
    round(arrayJoin(hist).2) AS upperBound,
    arrayJoin(hist).3 AS count,
    bar(count, 0, max(count) OVER (), 20) AS b
FROM
(
    SELECT
        path,
        sum(hits) AS hits
    FROM wikistat
    WHERE date(time) = '2015-06-15'
    GROUP BY path
    HAVING hits > 10000
);

┌─lowerBound─┬─upperBound─┬──count─┬─b────────────────────┐
│      10033 │      19886 │ 53.375 │ ████████████████████ │
│      19886 │      31515 │ 18.625 │ ██████▉              │
│      31515 │      43518 │  6.375 │ ██▍                  │
│      43518 │      55647 │  1.625 │ ▌                    │
│      55647 │      73602 │  1.375 │ ▌                    │
│      73602 │      92880 │   3.25 │ █▏                   │
│      92880 │     116778 │  1.375 │ ▌                    │
│     116778 │     186176 │  1.125 │ ▍                    │
│     186176 │     946963 │   1.75 │ ▋                    │
│     946963 │    1655250 │  1.125 │ ▍                    │
└────────────┴────────────┴────────┴──────────────────────┘

Изменения по периодам​

Накопленные значения​

Расчёт скорости​

Гистограммы​

Изменения по периодам

Накопленные значения

Расчёт скорости

Гистограммы