Привет, Хабр! Я — Максим Шитилов, продуктовый аналитик в каршеринг-сервисе Ситидрайв. Каждый день мы обрабатываем большие объёмы данных, и ClickHouse — один из наших ключевых инструментов. Если вы когда-либо пытались связать события с временными интервалами или рассчитать метрику за определённое окно после события, то наверняка сталкивались с типичной конструкцией на self-join. Вроде бы работает, но запрос становится громоздким, ресурсоёмким и плохо масштабируется.

В этой статье я расскажу, как решать такие задачи проще и эффективнее — с помощью массивов, arrayFilter и arrayMap. Покажу, как отказаться от self-join’ов без потери точности, ускорить обработку и упростить код. Примеры — из реальных бизнес-кейсов: телеметрия, аренды, GMV и события, которые нужно связать между собой по времени. Так как схожих решений на просторах интернета я не нашёл, предлагаю назвать этот подход “Array Join Pattern”. Если метод окажется полезным для сообщества, то такой паттерн легко будет найти другим аналитикам и девам.

КАК СВЯЗАТЬ СОБЫТИЯ И ИНТЕРВАЛЫ БЕЗ ЛИШНЕЙ НАГРУЗКИ?

ClickHouse отлично справляется с агрегациями и фильтрацией, но когда дело доходит до временных окон — всё не так просто. Например, нельзя напрямую написать:

“Посчитай сумму только за следующие 24 часа.”

Обычный подход — это self-join:

• Соединяем каждое событие со всеми возможными интервалами аренды;

• Проверяем условия через WHERE или ON с использованием BETWEEN.

Именно здесь начинаются сложности: объём данных резко растёт, запросы становятся тяжёлыми, а инфраструктура — перегруженной. Всё это усложняет аналитику и делает любые уточнения в логике болезненными.

Решение: массивы + arrayFilter

Вместо того чтобы пытаться скрестить всё со всем — складываем нужные интервалы или события в массивы и локально фильтруем их:

1. Группируем данные в массивы, например,  собираем все заказы машины в массив кортежей (дата заказа, id, GMV).

2. При анализе конкретного события фильтруем массив: оставляем только те элементы, которые попадают в интересующий временной интервал (или иное условие фильтрации).

3. Уже из отфильтрованного массива извлекаем нужную метрику — сумму, минимум, первый элемент и т.д.

ПРИМЕР 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,
        argMax(speed, telemetry_created_at_dttm) 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 в 6.3 раза быстрее, несмотря на то, что читает в 2.4 раза больше строк.

• Память: self-join прожорливее в 8.2 раза по RAM — почти 12.6 ГБ, и это уже опасно на проде: риск переполнения и ООМ.

• Объём прочитанных данных: массивный подход читает больше, потому что агрегирует все заказы в массив заранее. Но это читается одним сканом, без повторных пересечений.

Итого, arrayFilter читает больше, но это не страшно — потому что делает это одним сканом, без тяжёлых пересечений и промежуточных таблиц. Он жертвует диском (немного) — но выигрывает в памяти, времени и стабильности.

Где это реально помогает

• Привязка событий (телеметрия → аренды, заказы → пользователи, инциденты → машины).

• Динамическое суммирование в подвижных окнах (GMV за сутки, количество событий за Х времени, и т.п.).

• Быстрая агрегация локальных окон без построения тяжёлых витрин.

Подводные камни

• Размер массивов: если у объекта тысячи событий, массивы могут разрастись. Следите за ограничениями истории (например, месяц максимум. Но при этом всегда можно разбивать на партиции). 

• Пересечения интервалов: в некоторых редких случаях событие может попасть в несколько интервалов — стоит явно указывать приоритет (например, брать самый первый).

• Погрешности времени: будьте внимательны при сравнениях дат (Date vs DateTime).

Когда вам в следующий раз захочется написать self-join через 10 таблиц и 500 условий, подумайте: а нельзя ли просто сложить данные в массивы и обработать их локально? ClickHouse это позволяет. И делает это очень быстро.