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

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

метрики типа Counter, которые монотонно увеличиваются со временем (например, просмотры страниц или общее число событий)
метрики типа 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;

┌────────day─┬────h─┬────p─┬─trend─┐
│ 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 │
└────────────┴──────┴──────┴───────┘

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

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

Следующий запрос демонстрирует это с помощью конструкции 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(), чтобы автоматически построить адаптивную гистограмму на основе количества интервалов:

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 │ ▍                    │
└────────────┴────────────┴────────┴──────────────────────┘