Привет, Хабр!
В этой статье разберём, как построить 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, где профессионалы поделятся актуальными методами и подходами. Эти темы помогут вам эффективно решать сложные задачи в реальных проектах: