Дорогой self-join для когортного анализа на таблице из миллиардов строк событий — как оптимизировать запрос?

Короткий ответ

Вместо self-join напрямую к таблице сырых событий стоит сначала агрегировать события до уровня (user_id, день активности) в промежуточную таблицу или материализованное представление, а когортные расчёты строить оконными функциями или агрегатами поверх уже уменьшённого набора данных.

Как это работает подробнее

self-join на сырых событиях умножает число строк квадратично относительно активности пользователя (каждое событие джойнится с каждым), что на миллиардах строк даёт комбинаторный взрыв; предварительная агрегация до уникальных пар (user_id, day) на порядки уменьшает объём данных до джойна.

Пример кода

cohort_optimization.sqlКопировать

-- Шаг 1: предрасчёт агрегата (например, материализованное представление), ClickHouse-style
CREATE MATERIALIZED VIEW user_daily_activity AS
SELECT user_id, toDate(event_time) AS activity_date
FROM events
GROUP BY user_id, activity_date;
-- Шаг 2: когортный расчёт уже на маленькой агрегированной таблице
SELECT cohort_date, activity_date,
       uniqExact(user_id) AS active_users
FROM user_daily_activity ua
JOIN (SELECT user_id, min(activity_date) AS cohort_date FROM user_daily_activity GROUP BY user_id) c
  ON c.user_id = ua.user_id
GROUP BY cohort_date, activity_date;

Что использовать на практике

• Практика. в колоночных СУБД (ClickHouse, BigQuery) эффективнее использовать uniqExact/APPROX_COUNT_DISTINCT и агрегатные функции с условиями (countIf) вместо явных джойнов; также полезно партиционировать/кластеризовать исходную таблицу по дате и user_id, чтобы сканирование затрагивало меньше данных, и строить инкрементальные (daily) агрегаты вместо пересчёта всей истории при каждом запросе.

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

• Частые ошибки. джойнят таблицу событий саму с собой по user_id без предварительной агрегации, из-за чего результат промежуточного джойна может быть на порядки больше исходной таблицы; пересчитывают когортные метрики "с нуля" по всей истории при каждом запросе вместо инкрементального обновления уже посчитанных агрегатов.