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

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

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

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

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

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

Следующий запрос демонстрирует это, рассчитывая изменения в просмотрах по дням для страницы Википедии "Уирд Ал" Янковича. Столбец тренда показывает, увеличился ли трафик (положительные значения) или уменьшился (отрицательные значения) по сравнению с предыдущим днём, что помогает выявить необычные всплески или падения активности.

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

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

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

Следующий запрос демонстрирует это, используя клаузу 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 │ ▍                    │
└────────────┴────────────┴────────┴──────────────────────┘

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

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

Расчёты темпов​

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

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

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

Расчёты темпов

Гистограммы