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

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 дня назад и т.д.):