Аналитические суперсилы ClickHouse: Оконные функции и работа с массивами. Урок 8

Оконные функции 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. Мы не будем приводить весь листинг команд — посмотрим лишь на результаты:

Теперь по «развернутому» результату мы можем легко посчитать, например, какой тег самый популярный, с помощью обычного GROUP BY.

Высший пилотаж работа с массивами в ClickHouse: arrayFilter, arrayMap, arrayReduce

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

• arrayFilter(x -> условие, массив): Фильтрует массив, оставляя только те элементы x, для которых условие истинно.
• arrayMap(x -> выражение, массив): Применяет выражение к каждому элементу x массива, возвращая новый массив.
• arrayExists(x -> условие, массив): Проверяет, есть ли в массиве хотя бы один элемент x, удовлетворяющий условию.

Заключение

Windows функции и функции по работе с массивами в ClickHouse — это то, что отличает базовое использование SQL от продвинутой аналитики.

• Оконные функции позволяют вам проводить контекстуальный анализ, сравнивая строки друг с другом, не теряя при этом детализации.
• Массивные функции дают возможность работать со сложными, вложенными структурами данных элегантно и эффективно, избегая громоздких JOIN‘ов.

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

Использованные референсы и материалы

1. Официальная документация ClickHouse по Оконным функциям.
2. Официальная документация ClickHouse по Массивным функциям.

SQL-блокнот к Уроку 8 бесплатного курса доступен в нашем репозитории на GitHub