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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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