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

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

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

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

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

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

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

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

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

Пользовательские интервалы группировки

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

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

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

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

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

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

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

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

Иногда мы не хотим работать с началом интервалов (например, с началом дня или часа), а с оконными интервалами. Допустим, мы хотим понять общее количество попаданий за окно, а не по дням, а за 24-часовой период, смещенный от 6 вечера.

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