SQL Studio / Разборы SQL / A/B-тест в SQL
Ответ на задачу: если эксперимент рандомизирует людей, считайте конверсию по людям, а не по строкам событий: доля участников каждой группы, совершивших хотя бы одну покупку после назначения в заданном окне. Несколько оплат одного пользователя должны давать один флаг конверсии. Перед сравнением проверьте пользователей с двумя вариантами и покупки вне окна; SQL-арифметика сама по себе не доказывает статистическую значимость или корректность рандомизации.
Откуда берутся ложные 150%
В примере всем участникам назначение записано 1 сентября 2026 года. У участника №1 две покупки и дублированная техническая запись назначения в A; №7 получил и A, и B, так что его результат нельзя без оговорок относить к одной группе. У №5 покупка была до назначения, у №6 — через восемь суток. Все времена — UTC и одинаково форматированные ISO-строки.
Рассмотрим исполнимый, но ошибочный отчёт: присоединяем все покупки к каждой записи назначения и делим количество событий на количество уникальных назначенных пользователей. Повтор событий и повтор назначения дают ложный эффект.
WITH assignments(user_id, variant, assigned_at) AS (
VALUES (1,'A','2026-09-01 10:00:00'),
(1,'A','2026-09-01 10:00:00'),
(2,'A','2026-09-01 10:00:00'),
(3,'A','2026-09-01 10:00:00'),
(4,'B','2026-09-01 10:00:00'),
(5,'B','2026-09-01 10:00:00'),
(6,'B','2026-09-01 10:00:00'),
(7,'A','2026-09-01 10:00:00'),
(7,'B','2026-09-01 11:00:00')
),
events(event_id, user_id, event_at, event_type) AS (
VALUES (11,1,'2026-09-02 11:00:00','purchase'),
(12,1,'2026-09-03 11:00:00','purchase'),
(13,3,'2026-09-05 11:00:00','purchase'),
(14,4,'2026-09-02 09:00:00','purchase'),
(15,5,'2026-09-01 09:00:00','purchase'),
(16,6,'2026-09-09 10:00:00','purchase'),
(17,7,'2026-09-02 12:00:00','purchase'),
(18,2,'2026-09-01 12:00:00','page_view')
)
SELECT a.variant,
COUNT(DISTINCT a.user_id) AS assigned_users,
COUNT(e.event_id) AS purchase_events,
ROUND(100.0 * COUNT(e.event_id) / NULLIF(COUNT(DISTINCT a.user_id), 0), 2) AS false_conversion_pct
FROM assignments AS a
LEFT JOIN events AS e
ON e.user_id = a.user_id AND e.event_type = 'purchase'
GROUP BY a.variant
ORDER BY a.variant;
| variant | assigned_users | purchase_events | false_conversion_pct |
|---|---|---|---|
| A | 4 | 6 | 150,00% |
| B | 4 | 4 | 100,00% |
В A шесть присоединённых событий относятся вовсе не к шести людям. У пользователя №1 две покупки размножились из-за двух одинаковых записей назначения; пользователь №7 посчитан в обе группы. Уже сам показатель 150% сигнализирует, что мы поделили не те сущности.
Правильная единица — один участник и один флаг
Для этого учебного эксперимента явно выбираем правило: одинаковые повторные записи назначения считаем одной; пользователей, попавших в разные варианты, исключаем из сравнительного расчёта и отдельно отмечаем как нарушение данных. Покупка засчитывается после момента назначения в течение семи суток включительно. На настоящем проекте нельзя молча удалять конфликтных пользователей — сначала нужно выяснить причину, условия рандомизации и влияние исключения на смещение оценки.
WITH assignments(user_id, variant, assigned_at) AS (
VALUES (1,'A','2026-09-01 10:00:00'),
(1,'A','2026-09-01 10:00:00'),
(2,'A','2026-09-01 10:00:00'),
(3,'A','2026-09-01 10:00:00'),
(4,'B','2026-09-01 10:00:00'),
(5,'B','2026-09-01 10:00:00'),
(6,'B','2026-09-01 10:00:00'),
(7,'A','2026-09-01 10:00:00'),
(7,'B','2026-09-01 11:00:00')
),
events(event_id, user_id, event_at, event_type) AS (
VALUES (11,1,'2026-09-02 11:00:00','purchase'),
(12,1,'2026-09-03 11:00:00','purchase'),
(13,3,'2026-09-05 11:00:00','purchase'),
(14,4,'2026-09-02 09:00:00','purchase'),
(15,5,'2026-09-01 09:00:00','purchase'),
(16,6,'2026-09-09 10:00:00','purchase'),
(17,7,'2026-09-02 12:00:00','purchase'),
(18,2,'2026-09-01 12:00:00','page_view')
),
valid_assignment AS (
SELECT user_id, MIN(variant) AS variant,
MIN(assigned_at) AS assigned_at
FROM assignments
GROUP BY user_id
HAVING COUNT(DISTINCT variant) = 1
),
users_with_purchase AS (
SELECT DISTINCT a.user_id
FROM valid_assignment AS a
JOIN events AS e
ON e.user_id = a.user_id
AND e.event_type = 'purchase'
AND e.event_at >= a.assigned_at
AND e.event_at <= datetime(a.assigned_at, '+7 days')
)
SELECT a.variant,
COUNT(*) AS participants_n,
COUNT(p.user_id) AS converted_n,
ROUND(100.0 * COUNT(p.user_id) / NULLIF(COUNT(*), 0), 2) AS conversion_pct
FROM valid_assignment AS a
LEFT JOIN users_with_purchase AS p ON p.user_id = a.user_id
GROUP BY a.variant
ORDER BY a.variant;
| variant | participants_n | converted_n | conversion_pct |
|---|---|---|---|
| A | 3 | 2 | 66,67% |
| B | 3 | 1 | 33,33% |
Здесь A: 2 участника из 3 совершили хотя бы одну покупку, B: 1 из 3. Разница долей — −33⅓ процентного пункта для B относительно A, а относительное изменение — −50%. Эти значения описывают только шестерых допущенных участников. Округлённые 66,67 и 33,33 не следует вычитать ради «точной» оценки: посчитайте разницу из исходных дробей.
Это не статистический вывод. Шесть участников, неизвестная процедура рандомизации, возможные нарушения распределения и отсутствие доверительного интервала не позволяют говорить, что вариант A «доказанно лучше». Здесь проверяется корректность SQL-подсчёта, а не решение о запуске варианта.
Проверка данных до эксперимента: двойные назначения
Вместо того чтобы скрыть пересечение A и B, посчитайте его отдельным запросом на тех же сырых строках.
WITH assignments(user_id, variant, assigned_at) AS (
VALUES (1,'A','2026-09-01 10:00:00'),
(1,'A','2026-09-01 10:00:00'),
(2,'A','2026-09-01 10:00:00'),
(3,'A','2026-09-01 10:00:00'),
(4,'B','2026-09-01 10:00:00'),
(5,'B','2026-09-01 10:00:00'),
(6,'B','2026-09-01 10:00:00'),
(7,'A','2026-09-01 10:00:00'),
(7,'B','2026-09-01 11:00:00')
),
events(event_id, user_id, event_at, event_type) AS (
VALUES (11,1,'2026-09-02 11:00:00','purchase'),
(12,1,'2026-09-03 11:00:00','purchase'),
(13,3,'2026-09-05 11:00:00','purchase'),
(14,4,'2026-09-02 09:00:00','purchase'),
(15,5,'2026-09-01 09:00:00','purchase'),
(16,6,'2026-09-09 10:00:00','purchase'),
(17,7,'2026-09-02 12:00:00','purchase'),
(18,2,'2026-09-01 12:00:00','page_view')
)
SELECT COUNT(*) AS users_with_multiple_variants
FROM (
SELECT user_id
FROM assignments
GROUP BY user_id
HAVING COUNT(DISTINCT variant) > 1
);
| users_with_multiple_variants |
|---|
| 1 |
Число 1 относится к пользователю №7. А одинаковый повтор A для №1 не делает его участником двух вариантов. Такая проверка нужна до любого расчёта p-value или интерпретации lift.
Граничный тест: ровно на седьмые сутки
Отдельный контроль проверяет временную границу: событие в момент назначения + 7 суток включается; на секунду позже и до назначения — не включаются. В нашем определении окно закрыто с обеих сторон; другое определение следует прямо записать в отчёте.
WITH assignment(user_id, assigned_at) AS (
VALUES (9,'2026-09-01 10:00:00')
), events(event_id, user_id, event_at) AS (
VALUES (91,9,'2026-09-08 10:00:00'),
(92,9,'2026-09-08 10:00:01'),
(93,9,'2026-09-01 09:59:59')
)
SELECT e.event_id
FROM assignment AS a JOIN events AS e ON e.user_id = a.user_id
WHERE e.event_at >= a.assigned_at
AND e.event_at <= datetime(a.assigned_at, '+7 days')
ORDER BY e.event_id;
| event_id |
|---|
| 91 |
Самостоятельная проверка
В бесплатной SQL-песочнице добавьте ещё одну покупку участнику №4 в пределах окна. Должно ли увеличиться converted_n группы B? Затем замените время события №16 на точную границу +7 суток и пересчитайте конверсию. Сначала предскажите результат, затем выполните запрос.
Справка: CTE в SQLite и арифметика дат. В отдельном курсе Stepik используется MySQL 8; для границы там обычно применяют DATE_ADD(assigned_at, INTERVAL 7 DAY), а не SQLite datetime(..., '+7 days').
После бесплатной практики можно изучить MySQL 8 последовательно и собрать итоговый аналитический отчёт на отдельном авторском курсе Stepik. Для этих упражнений он не нужен.