SQL-пайплайны для A/B тестов: коротко

Привет, Хабр!

В этой статье разберём, как построить SQL-пайплайн для анализа A/B тестов с акцентом на чистку данных — дедупликацию, фильтрацию фрода, агрегацию и финальную сводку по пользователям.

Писать будем на PostgreSQL 13+, так как он отлично поддерживает нужные нам конструкции: FILTER, ROW_NUMBER(), CASE, NULLIF, CTE.

Задача: от событий к пользователям

Допустим, есть таблица events со следующими полями:

CREATE TABLE events (     user_id     INT,     event_id    BIGINT,     event_time  TIMESTAMP,     event_type  VARCHAR(50), -- click, conversion и т.д.     source      VARCHAR(50)  -- web, mobile, bot и т.п. );

Цель — собрать корректную пользовательскую статистику, исключив дубли, ботов и события вне нужного диапазона. Классическая ошибка здесь — просто сделать COUNT(*), не проверяя уникальность, валидность и контекст события.

Агрегация по типам событий

На первом этапе собираем количество событий по типам для каждого пользователя. Используем COUNT(DISTINCT ...) вместе с FILTER, чтобы контролировать выборку:

SELECT      user_id,     COUNT(DISTINCT event_id) FILTER (WHERE event_type = 'click') AS click_count,     COUNT(DISTINCT event_id) FILTER (WHERE event_type = 'conversion') AS conversion_count FROM events GROUP BY user_id;

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

Дедупликация через ROW_NUMBER()

Одна из типичных проблем — когда один пользователь совершает несколько кликов подряд, и каждый фиксируется как отдельное событие. Нужно оставить только одно событие на пользователя и тип.

Используем ROW_NUMBER() OVER (PARTITION BY user_id, event_type ORDER BY event_time) для нумерации событий. Почему именно ROW_NUMBER(), а не RANK()? Потому что ROW_NUMBER() гарантирует строгое первое место, даже если несколько записей имеют одинаковое время — RANK() может дать одну и ту же позицию разным строкам, что ломает дедупликацию.

Фильтрация по rn = 1 даёт строгий контроль. И тот же DISTINCT в таком случае уже не нужен, если event_id уникален.

WITH ranked_events AS (     SELECT          user_id,         event_id,         event_time,         event_type,         ROW_NUMBER() OVER (             PARTITION BY user_id, event_type              ORDER BY event_time         ) AS rn     FROM events     WHERE event_type IN ('click', 'conversion') ) SELECT      user_id,     COUNT(CASE WHEN event_type = 'click' AND rn = 1 THEN 1 END) AS dedup_clicks,     COUNT(CASE WHEN event_type = 'conversion' AND rn = 1 THEN 1 END) AS dedup_conversions FROM ranked_events GROUP BY user_id;

Если нужны более строгие критерии, например, только один клик в 10 секунд, можно вместо ROW_NUMBER() использовать LAG() и считать дельты времени.

Анализ по источникам и защита от фрода

Один и тот же пользователь может приходить как с веба, так и от имени бота. Мы хотим понимать, откуда приходят конверсии, и кто их делает.

Пример агрегации с фильтрацией по source:

SELECT      user_id,     COUNT(DISTINCT event_id) FILTER (WHERE event_type = 'click' AND source = 'web') AS web_clicks,     COUNT(DISTINCT event_id) FILTER (WHERE event_type = 'conversion' AND source = 'web') AS web_conversions,     COUNT(DISTINCT event_id) FILTER (WHERE event_type = 'click' AND source = 'bot') AS bot_clicks,     COUNT(DISTINCT event_id) FILTER (WHERE event_type = 'conversion' AND source = 'bot') AS bot_conversions,     COUNT(DISTINCT event_id) FILTER (WHERE event_type = 'conversion' AND source = 'web')         / NULLIF(COUNT(DISTINCT event_id) FILTER (WHERE event_type = 'click' AND source = 'web'), 0) AS web_conversion_rate FROM events GROUP BY user_id;

NULLIF защищает от деления на ноль, если пользователь вообще не кликал.

Полный пайплайн

Соберём весь пайплайн целиком, с чисткой мусора, предположим, что анализируем январь 2025 года:

WITH filtered_events AS (     SELECT *     FROM events     WHERE source NOT IN ('bot', 'crawler')       AND event_time BETWEEN '2025-01-01' AND '2025-01-31' ), ranked_events AS (     SELECT          user_id,         event_id,         event_time,         event_type,         ROW_NUMBER() OVER (             PARTITION BY user_id, event_type              ORDER BY event_time         ) AS rn     FROM filtered_events ), aggregated_events AS (     SELECT          user_id,         COUNT(CASE WHEN event_type = 'click' AND rn = 1 THEN 1 END) AS dedup_clicks,         COUNT(CASE WHEN event_type = 'conversion' AND rn = 1 THEN 1 END) AS dedup_conversions     FROM ranked_events     GROUP BY user_id ) SELECT      u.user_id,     u.username,     ae.dedup_clicks,     ae.dedup_conversions,     CASE          WHEN ae.dedup_clicks > 0               THEN ROUND(ae.dedup_conversions::numeric / ae.dedup_clicks, 4)         ELSE NULL     END AS conversion_rate FROM users u LEFT JOIN aggregated_events ae ON u.user_id = ae.user_id;

Балансировка A/B групп

Чтобы A/B тест был валидным, группы A и B должны быть сбалансированы по ключевым метрикам на старте. Например, по числу активных пользователей, среднему количеству кликов и конверсий до T0.

Пример агрегации по группам до начала эксперимента:

WITH base_events AS (     SELECT *     FROM events     WHERE event_time < '2025-01-15' ), user_metrics AS (     SELECT          user_id,         group_id,         COUNT(DISTINCT CASE WHEN event_type = 'click' THEN event_id END) AS clicks,         COUNT(DISTINCT CASE WHEN event_type = 'conversion' THEN event_id END) AS conversions     FROM base_events     GROUP BY user_id, group_id ), initial_metrics AS (     SELECT          group_id,         COUNT(DISTINCT user_id) AS users_at_T0,         ROUND(AVG(clicks)::numeric, 2) AS avg_clicks,         ROUND(AVG(conversions)::numeric, 2) AS avg_conversions     FROM user_metrics     GROUP BY group_id ) SELECT * FROM initial_metrics;

Если группы A и B в этом отчёте показывают существенные расхождения — нужно либо применять корректировки (например, стратификацию), либо учитывать это при интерпретации результатов.

Заключение

Пайплайн можно вынести в DAG, обернуть в dbt-модель или встроить в CI/CD-аналитику. Главное — понимать, что валидность данных начинается задолго до вывода метрики на дашборд.

Если вы уже строили подобные пайплайны и у вас есть интересным опыт — делитесь опытом в комментариях.

Если вы хотите углубить свои знания в области управления требованиями и работы с заинтересованными сторонами, рекомендуем посетить открытые уроки в Otus, где профессионалы поделятся актуальными методами и подходами. Эти темы помогут вам эффективно решать сложные задачи в реальных проектах:

• 10 апреля — Сквозной процесс управления требованиями.
  Подробнее

• 22 апреля — Основы работы с потребностями заинтересованных сторон и бизнес-проблемами.
  Подробнее