Перейти к основному содержимому
Перейти к основному содержимому

Основные операции с временными рядами

ClickHouse предоставляет несколько способов работы с данными временных рядов, позволяя агрегировать, группировать и анализировать данные в разных временных интервалах. В этом разделе рассматриваются основные операции, которые обычно используются при работе с данными, основанными на времени.

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

Мы собираемся исследовать возможности запросов ClickHouse для временных рядов с набором данных Wikistat (данные просмотра страниц Википедии):

CREATE TABLE wikistat
(
    `time` DateTime,
    `project` String,
    `subproject` String,
    `path` String,
    `hits` UInt64
)
ENGINE = MergeTree
ORDER BY (time);

Давайте заполним эту таблицу 1 миллиардом записей:

INSERT INTO wikistat 
SELECT *
FROM s3('https://ClickHouse-public-datasets.s3.amazonaws.com/wikistat/partitioned/wikistat*.native.zst') 
LIMIT 1e9;

Агрегация по временным интервалам​

Самым популярным требованием является агрегация данных на основе периодов, например, получение общего количества просмотров за каждый день:

SELECT
    toDate(time) AS date,
    sum(hits) AS hits
FROM wikistat
GROUP BY ALL
ORDER BY date ASC
LIMIT 5;

┌───────date─┬─────hits─┐
│ 2015-05-01 │ 25524369 │
│ 2015-05-02 │ 25608105 │
│ 2015-05-03 │ 28567101 │
│ 2015-05-04 │ 29229944 │
│ 2015-05-05 │ 29383573 │
└────────────┴──────────┘

Мы использовали функцию toDate(), которая преобразует указанное время в тип даты. В качестве альтернативы, мы можем группировать по часам и фильтровать по конкретной дате:

SELECT
    toStartOfHour(time) AS hour,
    sum(hits) AS hits    
FROM wikistat
WHERE date(time) = '2015-07-01'
GROUP BY ALL
ORDER BY hour ASC
LIMIT 5;

┌────────────────hour─┬───hits─┐
│ 2015-07-01 00:00:00 │ 656676 │
│ 2015-07-01 01:00:00 │ 768837 │
│ 2015-07-01 02:00:00 │ 862311 │
│ 2015-07-01 03:00:00 │ 829261 │
│ 2015-07-01 04:00:00 │ 749365 │
└─────────────────────┴────────┘

Функция toStartOfHour(), использованная здесь, преобразует указанное время в начало часа. Вы также можете группировать по году, кварталу, месяцу или дню.

Пользовательские временные интервалы​

Мы можем также группировать по произвольным интервалам, например, по 5 минут с помощью функции toStartOfInterval().

Предположим, мы хотим группировать по 4-часовым интервалам. Мы можем указать интервал группировки с помощью команды INTERVAL:

SELECT
    toStartOfInterval(time, INTERVAL 4 HOUR) AS interval,
    sum(hits) AS hits
FROM wikistat
WHERE date(time) = '2015-07-01'
GROUP BY ALL
ORDER BY interval ASC
LIMIT 6;

Или мы можем использовать функцию toIntervalHour():

SELECT
    toStartOfInterval(time, toIntervalHour(4)) AS interval,
    sum(hits) AS hits
FROM wikistat
WHERE date(time) = '2015-07-01'
GROUP BY ALL
ORDER BY interval ASC
LIMIT 6;

В любом случае, мы получим следующие результаты:

┌────────────interval─┬────hits─┐
│ 2015-07-01 00:00:00 │ 3117085 │
│ 2015-07-01 04:00:00 │ 2928396 │
│ 2015-07-01 08:00:00 │ 2679775 │
│ 2015-07-01 12:00:00 │ 2461324 │
│ 2015-07-01 16:00:00 │ 2823199 │
│ 2015-07-01 20:00:00 │ 2984758 │
└─────────────────────┴─────────┘

Заполнение пустых групп​

Во многих случаях мы имеем дело с разреженными данными с отсутствующими интервалами. Это приводит к пустым интервалам. Рассмотрим следующий пример, где мы группируем данные по 1-часовым интервалам. Это выведет следующие статистические данные с некоторыми отсутствующими значениями:

SELECT
    toStartOfHour(time) AS hour,
    sum(hits)
FROM wikistat
WHERE (project = 'ast') AND (subproject = 'm') AND (date(time) = '2015-07-01')
GROUP BY ALL
ORDER BY hour ASC;

┌────────────────hour─┬─sum(hits)─┐
│ 2015-07-01 00:00:00 │         3 │ <- отсутствующие значения
│ 2015-07-01 02:00:00 │         1 │ <- отсутствующие значения
│ 2015-07-01 04:00:00 │         1 │
│ 2015-07-01 05:00:00 │         2 │
│ 2015-07-01 06:00:00 │         1 │
│ 2015-07-01 07:00:00 │         1 │
│ 2015-07-01 08:00:00 │         3 │
│ 2015-07-01 09:00:00 │         2 │ <- отсутствующие значения
│ 2015-07-01 12:00:00 │         2 │
│ 2015-07-01 13:00:00 │         4 │
│ 2015-07-01 14:00:00 │         2 │
│ 2015-07-01 15:00:00 │         2 │
│ 2015-07-01 16:00:00 │         2 │
│ 2015-07-01 17:00:00 │         1 │
│ 2015-07-01 18:00:00 │         5 │
│ 2015-07-01 19:00:00 │         5 │
│ 2015-07-01 20:00:00 │         4 │
│ 2015-07-01 21:00:00 │         4 │
│ 2015-07-01 22:00:00 │         2 │
│ 2015-07-01 23:00:00 │         2 │
└─────────────────────┴───────────┘

ClickHouse предоставляет модификатор WITH FILL для решения этой проблемы. Это заполнит все пустые часы нулями, чтобы мы могли лучше понять распределение во времени:

SELECT
    toStartOfHour(time) AS hour,
    sum(hits)
FROM wikistat
WHERE (project = 'ast') AND (subproject = 'm') AND (date(time) = '2015-07-01')
GROUP BY ALL
ORDER BY hour ASC WITH FILL STEP toIntervalHour(1);

┌────────────────hour─┬─sum(hits)─┐
│ 2015-07-01 00:00:00 │         3 │
│ 2015-07-01 01:00:00 │         0 │ <- новое значение
│ 2015-07-01 02:00:00 │         1 │
│ 2015-07-01 03:00:00 │         0 │ <- новое значение
│ 2015-07-01 04:00:00 │         1 │
│ 2015-07-01 05:00:00 │         2 │
│ 2015-07-01 06:00:00 │         1 │
│ 2015-07-01 07:00:00 │         1 │
│ 2015-07-01 08:00:00 │         3 │
│ 2015-07-01 09:00:00 │         2 │
│ 2015-07-01 10:00:00 │         0 │ <- новое значение
│ 2015-07-01 11:00:00 │         0 │ <- новое значение
│ 2015-07-01 12:00:00 │         2 │
│ 2015-07-01 13:00:00 │         4 │
│ 2015-07-01 14:00:00 │         2 │
│ 2015-07-01 15:00:00 │         2 │
│ 2015-07-01 16:00:00 │         2 │
│ 2015-07-01 17:00:00 │         1 │
│ 2015-07-01 18:00:00 │         5 │
│ 2015-07-01 19:00:00 │         5 │
│ 2015-07-01 20:00:00 │         4 │
│ 2015-07-01 21:00:00 │         4 │
│ 2015-07-01 22:00:00 │         2 │
│ 2015-07-01 23:00:00 │         2 │
└─────────────────────┴───────────┘

Скользящие временные окна​

Иногда мы не хотим иметь дело с началом интервалов (например, началом дня или часа), а с временными окнами. Предположим, мы хотим понять общее количество просмотров за окно, не основываясь на днях, а на 24-часовом периоде, смещенном с 18:00.

Мы можем использовать функцию date_diff() для вычисления разницы между эталонным временем и временем каждой записи. В этом случае столбец day будет представлять разницу в днях (например, 1 день назад, 2 дня назад и т.д.):

SELECT    
    dateDiff('day', toDateTime('2015-05-01 18:00:00'), time) AS day,
    sum(hits)
FROM wikistat
GROUP BY ALL
ORDER BY day ASC
LIMIT 5;

┌─day─┬─sum(hits)─┐
│   0 │  25524369 │
│   1 │  25608105 │
│   2 │  28567101 │
│   3 │  29229944 │
│   4 │  29383573 │
└─────┴───────────┘