Как в Clickhouse создать таблицу с календарем

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

Почему именно таблица календаря?

Таблица календаря - это фундаментальный инструмент для создания отчетов. Она обеспечивает структурированное представление времени, позволяя легко агрегировать и анализировать данные по различным временным параметрам, таким как год, квартал, месяц и день недели.

Скрипт

В решении, которое я Вам предлагаю, используются функции массива ClickHouse и ее возможности манипулирования датами. Давайте разберем скрипт:

— DROP TABLE IF EXISTS default.calendar;
CREATE TABLE default.calendar(
`date` Date NOT NULL
, `year` UInt16 NOT NULL
, `quarter` UInt8 NOT NULL
, `month` UInt8 NOT NULL
, `yearmonth` UInt32 NOT NULL
, year_day UInt16 NOT NULL
, month_day UInt8 NOT NULL
, week_day UInt8 NOT NULL
, month_name String NOT NULL
, week_day_name String NOT NULL
)
ENGINE = MergeTree()
ORDER BY `date`;

INSERT INTO default.calendar
WITH
toStartOfDay(toDate(‘2000–01–01’)) AS start,
toStartOfDay(toDate(‘2041–01–01’)) AS end

SELECT
date
, EXTRACT(YEAR FROM date) AS `year`
, toQuarter(date) AS `quarter`
, toMonth(date) AS `month`
, concat(toString(EXTRACT(YEAR FROM date)), LPAD(toString(toMonth(date)), 2, ‘0’)) AS `yearmonth`
, toDayOfYear(date) AS year_day
, toDayOfMonth(date) AS month_day
, toDayOfWeek(date) AS week_day
, (CASE
WHEN `month` = 1 THEN ‘January’
WHEN `month` = 2 THEN ‘February’
WHEN `month` = 3 THEN ‘March’
WHEN `month` = 4 THEN ‘April’
WHEN `month` = 5 THEN ‘May’
WHEN `month` = 6 THEN ‘June’
WHEN `month` = 7 THEN ‘July’
WHEN `month` = 8 THEN ‘August’
WHEN `month` = 9 THEN ‘September’
WHEN `month` = 10 THEN ‘October’
WHEN `month` = 11 THEN ‘November’
WHEN `month` = 12 THEN ‘December’
END) AS month_name
, (CASE
WHEN week_day = 1 THEN ‘Monday’
WHEN week_day = 2 THEN ‘Tuesday’
WHEN week_day = 3 THEN ‘Wednesday’
WHEN week_day = 4 THEN ‘Thursday’
WHEN week_day = 5 THEN ‘Friday’
WHEN week_day = 6 THEN ‘Saturday’
WHEN week_day = 7 THEN ‘Sunday’
END) AS week_day_name
FROM
(SELECT
arrayJoin(arrayMap(x -> toDate(x), range(toUInt32(start), toUInt32(end), 24 * 3600))) as date);

Как он работает

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

Преимущества скрипта

Запустив этот скрипт, Вы получите заполненную таблицу календаря, полностью готовую к дальнейшему использованию. Ничего вручную делать не нужно!

← Предыдущая статья

Kafka + NiFi + Clickhouse в Docker

Следующая статья →

Прокачиваем видеоаналитику с помощью ClickHouse