Привет, Хабр! Я — Максим Шитилов, продуктовый аналитик в каршеринг-сервисе Ситидрайв. Каждый день мы обрабатываем большие объёмы данных, и ClickHouse — один из наших ключевых инструментов. Если вы когда-либо пытались связать события с временными интервалами или рассчитать метрику за определённое окно после события, то наверняка сталкивались с типичной конструкцией на self-join. Вроде бы работает, но запрос становится громоздким, ресурсоёмким и плохо масштабируется.
В этой статье я расскажу, как решать такие задачи проще и эффективнее — с помощью массивов, arrayFilter и arrayMap. Покажу, как отказаться от self-join’ов без потери точности, ускорить обработку и упростить код. Примеры — из реальных бизнес-кейсов: телеметрия, аренды, GMV и события, которые нужно связать между собой по времени. Так как схожих решений на просторах интернета я не нашёл, предлагаю назвать этот подход «Array Join Pattern». Если метод окажется полезным для сообщества, то такой паттерн легко будет найти другим аналитикам и девам.
Как связать события и интервалы без лишней нагрузки?
ClickHouse отлично справляется с агрегациями и фильтрацией, но когда дело доходит до временных окон — всё не так просто. Например, нельзя напрямую написать:
«Посчитай сумму только за следующие 24 часа.»
Обычный подход — это self-join:
Соединяем каждое событие со всеми возможными интервалами аренды;
Проверяем условия через WHERE или ON с использованием BETWEEN.
Именно здесь начинаются сложности: объём данных резко растёт, запросы становятся тяжёлыми, а инфраструктура — перегруженной. Всё это усложняет аналитику и делает любые уточнения в ��огике болезненными.
Решение: массивы + arrayFilter
Вместо того чтобы пытаться скрестить всё со всем — складываем нужные интервалы или события в массивы и локально фильтруем их:
Группируем данные в массивы, например, собираем все заказы машины в массив кортежей (дата заказа, id, GMV).
При анализе конкретного события фильтруем массив: оставляем только те элементы, которые попадают в интересующий временной интервал (или иное условие фильтрации).
Уже из отфильтрованного массива извлекаем нужную метрику — сумму, минимум, первый элемент и т.д.
Пример 1: привязка превышения скорости к аренде
Бизнес-задача:
Машины регулярно высылают пакеты телеметрии: координаты, скорость, запас топлива и прочие важные метрики;
Из этих пакетов необходимо выделять случаи превышения скорости, например, все, кто разогнался больше 140 км/ч за последние 3 недели.
Шаг 1: собираем все заказы в массив к каждой машине
Для начала соберём всю историю аренды каждой машины в один массив. Каждый элемент массива — это кортеж из времени начала аренды, времени окончания аренды и id аренды.
WITH orders_by_car_cte AS (
SELECT
car_id,
groupArray((order_created_at, order_finished_at, order_id)) AS orders_array
FROM orders
WHERE order_created_at >= now() - INTERVAL 21 DAY
GROUP BY car_id
),Шаг 2: Находим превышения скорости в телеметрии
Из всей истории телеметрии находим пятиминутные интервалы, в которых была зафиксирована скорость выше 140 км/ч. Считаем:
максимальную скорость в интервале (argMax)
количество записей в интервале (чтобы понимать относительную продолжительность превышения скорости)
max_speed_cte AS (
SELECT
car_id,
toStartOfInterval(telemetry_created_at_dttm, INTERVAL 5 minute) AS dttm_5min_interval,
max(speed) AS max_speed, -- максимальная скорость за 5-минутный интервал
count() AS speed_packages_cnt
FROM telemetry tm
WHERE telemetry_created_at_dttm >= now() - INTERVAL 21 DAY
AND speed >= 140 -- отсекаем ненужные пакеты телеметрии
GROUP BY 1, 2
)Шаг 3: Соединяем, но без join по времени!
Теперь главное — сопоставим превышения скорости с заказами, но не через self-join, а через работу с массивом.
SELECT
ms.car_id,
dttm_5min_interval,
max_speed,
speed_packages_cnt,
arrayMap(x -> x.3, -- вытаскиваем order_id
arrayFilter(
x -> (toDateTime(dt_interval) BETWEEN x.1 AND x.2),
orders_array
)
)[1] AS order_id -- берём первый попавшийся заказ
FROM max_speed_cte ms
GLOBAL LEFT JOIN orders_by_car_cte obc ON USING (car_id)В этом выражении мы:
Берём массив заказов orders_array;
Фильтруем его так, чтобы остались только те order_id, у которых dt_interval (начало пятиминутки) попадает в интервалы аренды;
Достаём order_id первого подходящего заказа.
То есть: для каждой строки (интервала) внутри строки перебрали все аренды и выбрали ту, в которую попадает dt_interval.
Итог: сервер живёт, DBA не пишут в личку ночью, а аналитик — молодец.
Однако, на этом этапе может возникнуть вполне резонный вопрос:
А как вообще работает arrayFilter? Нет ли такого, что он пробегает по всем заказам и смотрит на их время? В таком случае у нас для каждого интервала dt_interval смотрятся все элементы из orders_array и всё равно получатся квадратичная сложность (то есть self-join)?
Эта функция перебирает весь массив, проверяя, п��дходит ли элемент под условие. Внутри себя она, по сути, делает оптимизированный векторный проход на уровне колонки, чтобы одновременно обработать множество строк.
Да, это означает, что если в массиве тысячи заказов, arrayFilter действительно прогонит их все. Но важно понимать:
Это не то же самое, что JOIN, где у нас на выходе создаётся декартово произведение (миллионы строк).
Здесь всё остаётся в одной строке. Мы просто “фильтруем внутри”.
При очень длинных массивах (например, groupArray 10k+ элементов) это всё же может быть долго. Лучше лимитировать или предфильтровать источники.
Если бы это был Python, то выглядело бы примерно так:
import numpy as np
arr_np = np.arange(10_000_000)
result_np = arr_np[arr_np % 2 == 0]Но ClickHouse делает это массово и эффективно, используя SIMD-инструкции и предвыделенную память (где возможно). Так что: логика — как у цикла, производительность — как у векторной обработки.
Пример 2: gmv за 24 часа после события
Бизнес-задача:
Хотим понять уровень дохода авто после какого-то события (например, мойки, ТО и т.д.). Нужно посчитать сумму GMV заказов, начавшихся в течение следующих 24 часов после события.
Решение:
arrayFilter оставляет заказы в нужном окне времени
arrayMap выдёргивает GMV
arraySum его суммирует
WITH orders_by_car_cte AS (
SELECT
car_id,
groupArray((order_created_at, order_id, toFloat64(coalesce(gmv, 0)))) AS orders_array
FROM orders
WHERE order_created_at >= now() - INTERVAL 1 MONTH
GROUP BY car_id
)
SELECT
ev.car_id,
ev.event_time,
ev.event_type,
round(
arraySum(
arrayMap(
x -> x.3,
arrayFilter(
x -> x.1 BETWEEN ev.event_time AND ev.event_time + INTERVAL 24 HOUR,
orders_array
)
)
), 2
) AS gmv24h
FROM events ev
GLOBAL LEFT JOIN orders_by_car_cte USING (car_id)Давайте сравним производительности
Прогоним оба метода за одинаковый временной интервал (месяц) и посмотрим логи запросов:
Метрика | arrayFilter | self-join |
Время выполнения | 7.07 сек | 44.5 сек |
Прочитано строк | 20.2 млн | 8.4 млн |
Прочитано байт | 1.25 ГБ | 482 МБ |
Использование RAM | 1.52 ГБ | 12.6 ГБ |
Выводы:
Время: arrayFilter в 6.3 раза быстрее, несмотря на то, что читает в 2.4 раза больше строк.
Память: self-join прожорливее в 8.2 раза по RAM — почти 12.6 ГБ, и это уже опасно на проде: риск переполнения и ООМ.
Объём прочитанных данных: массивный подход читает больше, потому что агрегирует все заказы в массив заранее. Но это читается одним сканом, без повторных пересечений.
Итого, arrayFilter читает больше, но это не страшно — потому что делает это одним сканом, без тяжёлых пересечений и промежуточных таблиц. Он жертвует диском (немного) — но выигрывает в памяти, времени и стабильности.
Где это реально помогает
Привязка событий (телеметрия → аренды, заказы → пользователи, инциденты → машины).
Динамическое суммирование в подвижных окнах (GMV за сутки, количество событий за Х времени, и т.п.).
Быстрая агрегация локальных окон без построения тяжёлых витрин.
Подводные камни
Размер массивов: если у объекта тысячи событий, массивы могут разрастись. Следите за ограничениями истории (например, месяц максимум. Но при этом всегда можно разбивать на партиции).
Пересечения интервалов: в некоторых редких случаях событие может попасть в несколько интервалов — стоит явно указывать приоритет (например, брать самый первый).
Погрешности времени: будьте внимательны при сравнениях дат (Date vs DateTime).
Когда вам в следующий раз захочется написать self-join через 10 таблиц и 500 условий, подумайте: а нельзя ли просто сложить данные в массивы и обработать их локально? ClickHouse это позволяет. И делает это очень быстро.
