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

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

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

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

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

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

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

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

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

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

Вычисление темпов​

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

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

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

Вычисление темпов

Гистограммы