Оконные функции ClickHouse и работа с массивами данных. Мы с вами уже прошли большой путь: научились эффективно хранить данные, оптимизировать таблицы, выполнять базовые и сложные запросы и даже интегрироваться с внешними системами. Казалось бы, мы можем практически всё. Но как ответить на такие вопросы:
“Каково время между последовательными действиями каждого пользователя на сайте?”
“Как найти топ-3 самых дорогих товара в каждой категории?”
“Какие теги чаще всего встречаются вместе в статьях?”
Стандартный GROUP BY здесь либо бессилен, либо потребует очень сложных и медленных подзапросов. Для решения таких задач в ClickHouse есть два мощнейших инструмента: оконные функции и функции для работы с массивами. Давайте разберемся, как они открывают новые горизонты аналитики.
Оконные функции в ClickHouse: Аналитика в “скользящем окне”
Представьте, что вам нужно для каждой строки выполнить вычисление, но не по всей таблице, а по группе строк (“окну”), которая как-то связана с текущей. Именно это и делают оконные функции. В отличие от GROUP BY, они не “схлопывают” строки в одну, а возвращают результат для каждой строки, сохраняя ее исходные данные.
Синтаксис OVER()
Магия оконных функций заключается в конструкции OVER(), которая определяет то самое “окно”.
FUNCTION() OVER (PARTITION BY ... ORDER BY ...)
PARTITION BY столбец_1, столбец_2: Это самая важная часть. Она разбивает все строки на независимые группы (партиции). Все вычисления будут происходить строго внутри этих групп. Например,PARTITION BY user_idсоздаст отдельное “окно” для каждого пользователя.ORDER BY столбец_3: Упорядочивает строки внутри каждой партиции. Это критически важно для функций, которым важен порядок, — например, для вычисления разницы с предыдущей строкой.
Ключевые оконные функции
1. Ранжирование: ROW_NUMBER, RANK, DENSE_RANK
Эти функции присваивают порядковый номер строкам внутри окна.
ROW_NUMBER(): Просто нумерует строки по порядку (1, 2, 3, 4…).RANK(): Присваивает одинаковый ранг строкам с одинаковыми значениями вORDER BY, но оставляет “пропуски” в нумерации (1, 2, 2, 4…).DENSE_RANK(): То же, что иRANK, но без “пропусков” (1, 2, 2, 3…).
Пример: Найдем две самые длинные сессии для каждого пользователя.
SELECT * FROM (
SELECT
user_id,
url,
duration_ms,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY duration_ms DESC) as session_rank
FROM access_logs
)
WHERE session_rank <= 2;
Здесь мы для каждого user_id отсортировали его события по убыванию продолжительности и присвоили им ранг. Затем внешним запросом отобрали только первые две записи для каждого пользователя.
2. Смещение: LAG и LEAD
Эти функции позволяют получить доступ к значению из предыдущей (LAG) или следующей (LEAD) строки внутри окна.
Пример: Рассчитаем время между последовательными событиями каждого пользователя.
SELECT
user_id,
timestamp,
event_type,
LAG(timestamp, 1) OVER (PARTITION BY user_id ORDER BY timestamp) AS prev_event_timestamp,
timestamp - prev_event_timestamp AS time_diff_seconds
FROM access_logs
ORDER BY user_id, timestamp;
К сожалению Windows функции до сих пор относятся к экспериментальным опциям ClickHouse и могут не поддерживаться на вашей инсталляции или дистрибутиве.
3. Агрегаты в окне: SUM(), AVG(), COUNT()
Вы можете использовать стандартные агрегатные функции в оконной конструкции, чтобы, например, посчитать “бегущую сумму” (running total).
Пример: Посчитаем нарастающий итог по количеству событий для каждого пользователя.
SELECT
user_id,
timestamp,
event_type,
COUNT(*) OVER (PARTITION BY user_id ORDER BY timestamp) as running_event_count
FROM access_logs;
Функции для работы с массивами: Сила в одной ячейке
ClickHouse позволяет хранить в одной ячейке массив значений (например, Array(String) или Array(UInt64)). Это невероятно удобно для хранения сложных, вложенных структур, таких как теги к статье, свойства товара или список событий в рамках сессии.
Ключевая функция: arrayJoin
Это, пожалуй, самая важная функция для работы с массивами ClickHouse. Она принимает на вход столбец с массивами и “разворачивает” (или “взрывает”) его, создавая отдельную строку для каждого элемента каждого массива. В SQL блокноте #8, ссылки на который помещены для скачивания в наш GitHub репозиторий выделенны для блока статей и практических работа по теме ClickHouse, есть пример 2 который иллюстрирует работу с функциями массива в ClickHouse. Мы не будем приводить весь листинг команд – посмотрим лишь на результаты:
Используем arrayJoin, чтобы создать отдельную строку для каждого тега.
Теперь по “развернутому” результату мы можем легко посчитать, например, какой тег самый популярный, с помощью обычного GROUP BY.
Высший пилотаж работа с массивами в ClickHouse: arrayFilter, arrayMap, arrayReduce
ClickHouse поддерживает функции высшего порядка, которые принимают другие (лямбда) функции в качестве аргументов. Это позволяет выполнять сложнейшие работы с массивами в ClickHouse.
arrayFilter(x -> условие, массив): Фильтрует массив, оставляя только те элементыx, для которыхусловиеистинно.arrayMap(x -> выражение, массив): Применяетвыражениек каждому элементуxмассива, возвращая новый массив.arrayExists(x -> условие, массив): Проверяет, есть ли в массиве хотя бы один элементx, удовлетворяющийусловию.
Заключение
Windows функции и функции по работе с массивами в ClickHouse — это то, что отличает базовое использование SQL от продвинутой аналитики.
- Оконные функции позволяют вам проводить контекстуальный анализ, сравнивая строки друг с другом, не теряя при этом детализации.
- Массивные функции дают возможность работать со сложными, вложенными структурами данных элегантно и эффективно, избегая громоздких
JOIN‘ов.
Освоение этих инструментов откроет для вас новые горизонты в анализе данных и позволит извлекать из них максимум ценности напрямую в ClickHouse. В заключительных статьях нашего цикла мы поговорим о не менее важных темах: мониторинге и администрировании вашего кластера.
Использованные референсы и материалы
- Официальная документация ClickHouse по Оконным функциям.
- Официальная документация ClickHouse по Массивным функциям.
SQL-блокнот к Уроку 8 бесплатного курса доступен в нашем репозитории на GitHub
