SQL для A/B-тестов
Зачем это знать
Тест открутился — дальше его нужно посчитать. Во многих командах базовую статистику по A/B считают прямо в SQL или готовят агрегаты, которые потом дожимают в Python. Поэтому умение достать из сырых событий конверсию, разницу, ошибку и значимость одним запросом — обязательный навык для middle product analyst. На собесе это любят проверять живьём: дают табличку experiment и просят «посчитай, значимо ли».
Ниже — разбор всех кусков анализа A/B на SQL: от конверсии по вариантам до CUPED. Предполагается таблица experiment с колонками variant, converted (0/1), revenue, assigned_at и признаками сегментов.
Базовый анализ: конверсия по вариантам
Первый шаг — посчитать размер групп и конверсию в каждой:
SELECT
variant,
COUNT(*) AS users, -- размер группы
SUM(converted) AS conversions, -- число конверсий
AVG(converted) AS cr, -- конверсия (доля)
AVG(converted) * 100 AS cr_pct -- конверсия в процентах
FROM experiment
GROUP BY variant;Здесь AVG(converted) работает как доля успехов, потому что converted — это 0/1. Уже на этом шаге видно, сопоставимы ли размеры групп: если они сильно разъехались, дальше считать смысла нет, пока не разберётесь почему (см. раздел про SRM).
Расчёт lift
Lift — относительный прирост конверсии теста над контролем. Именно его показывают бизнесу, а не абсолютную разницу:
WITH stats AS (
SELECT
AVG(CASE WHEN variant = 'control' THEN converted END) AS control_cr,
AVG(CASE WHEN variant = 'test' THEN converted END) AS test_cr
FROM experiment
)
SELECT
control_cr,
test_cr,
test_cr - control_cr AS abs_diff, -- абсолютная разница
(test_cr - control_cr) / control_cr AS relative_lift, -- относительный прирост
(test_cr - control_cr) / control_cr * 100 AS lift_pct -- прирост в процентах
FROM stats;Важно не путать абсолютную и относительную разницу. Если контроль 10%, а тест 11%, то абсолютная разница — 1 процентный пункт, а относительный lift — целых 10%. В отчёте всегда уточняйте, о какой именно разнице речь.
Стандартная ошибка
Разница конверсий сама по себе ничего не говорит без оценки её погрешности. Стандартная ошибка разницы двух долей считается по классической формуле:
WITH stats AS (
SELECT
variant,
AVG(converted) AS p, -- конверсия в группе
COUNT(*) AS n -- размер группы
FROM experiment
GROUP BY variant
),
pivoted AS (
SELECT
MAX(CASE WHEN variant = 'control' THEN p END) AS p_c,
MAX(CASE WHEN variant = 'control' THEN n END) AS n_c,
MAX(CASE WHEN variant = 'test' THEN p END) AS p_t,
MAX(CASE WHEN variant = 'test' THEN n END) AS n_t
FROM stats
)
SELECT
p_c, p_t,
p_t - p_c AS diff,
SQRT(p_c * (1 - p_c) / n_c + p_t * (1 - p_t) / n_t) AS se, -- стандартная ошибка разницы
(p_t - p_c) / SQRT(p_c * (1 - p_c) / n_c + p_t * (1 - p_t) / n_t) AS z_stat -- z-статистика
FROM pivoted;Стандартная ошибка тем меньше, чем больше выборка. Отсюда простое следствие: маленькая разница на большой выборке может быть значимой, а большая на маленькой — нет.
Z-статистика и p-value
Отношение разницы к её стандартной ошибке даёт z-статистику. Для двустороннего теста p-value равно 2 × (1 − Φ(|z|)), где Φ — функция распределения стандартной нормали.
-- z_stat посчитан выше
-- p-value = 2 × (1 - Φ(|z|))
-- В Postgres нет встроенной функции нормального CDF (NORMAL_CDF),
-- поэтому точное p-value обычно считают уже в Python (scipy.stats.norm).Для быстрой прикидки прямо в SQL точный p-value не нужен: достаточно сравнить z-статистику с критическим значением. Для 95% доверия порог — 1.96:
CASE WHEN ABS(z_stat) > 1.96 THEN 'значимо' ELSE 'не значимо' END AS resultДоверительный интервал
Доверительный интервал разницы нагляднее, чем голое «значимо / не значимо»: он сразу показывает и направление, и размер эффекта.
SELECT
diff - 1.96 * se AS ci_lower, -- нижняя граница 95% ДИ
diff + 1.96 * se AS ci_upper -- верхняя граница 95% ДИ
FROM pivoted;Правило простое: если интервал не содержит ноль — разница значима на уровне 5%. Если содержит — эффект статистически неотличим от нуля, каким бы ни был знак разницы.
Непрерывные метрики
Для непрерывных метрик (выручка, число сессий, а не 0/1) используют не долю, а среднее и стандартное отклонение — по сути t-тест:
WITH stats AS (
SELECT
variant,
AVG(revenue) AS mean_rev, -- среднее
STDDEV(revenue) AS std_rev, -- стандартное отклонение
COUNT(*) AS n
FROM experiment
GROUP BY variant
)
-- Разница средних и её стандартная ошибка (аналог t-теста)
SELECT
MAX(CASE WHEN variant = 'test' THEN mean_rev END) -
MAX(CASE WHEN variant = 'control' THEN mean_rev END) AS diff,
SQRT(
MAX(CASE WHEN variant = 'test' THEN std_rev * std_rev / n END) +
MAX(CASE WHEN variant = 'control' THEN std_rev * std_rev / n END)
) AS se
FROM stats;С выручкой есть подвох: распределение обычно сильно скошено (несколько крупных плательщиков тянут среднее). Поэтому среднее по выручке волатильно, и тут особенно помогает CUPED (см. ниже) или переход к более устойчивым метрикам.
Сегментация
Разрез по сегментам показывает, одинаково ли работает фича для всех. Часто эффект прячется в подгруппах:
SELECT
variant,
platform,
AVG(converted) AS cr,
COUNT(*) AS n
FROM experiment
GROUP BY variant, platform
ORDER BY platform, variant;Так вылавливают гетерогенный эффект: например, фича растит конверсию на iOS, но роняет на Android, а в среднем разница «размывается» в ноль. Но помните про множественную проверку — чем больше сегментов вы перебираете, тем выше шанс поймать ложную находку.
Guardrail-метрики
Смотреть только на основную метрику опасно: улучшив её, можно незаметно сломать что-то ещё. Guardrail-метрики — это те, что не должны просесть.
SELECT
variant,
AVG(converted) AS cr, -- основная метрика
AVG(revenue) AS arpu, -- guardrail: выручка
AVG(refund) AS refund_rate, -- guardrail: возвраты
AVG(session_count) AS avg_sessions -- guardrail: вовлечённость
FROM experiment
GROUP BY variant;Классический пример: тест поднял конверсию, но вместе с ней выросли возвраты — суммарно продукт ушёл в минус. Без guardrail-метрик такой эффект остаётся невидимым.
Динамика во времени
Разбивка по дням помогает поймать эффект новизны (novelty effect), когда прирост держится первые дни, а потом сходит на нет:
SELECT
DATE(assigned_at) AS day,
variant,
COUNT(*) AS users,
AVG(converted) AS cr
FROM experiment
GROUP BY 1, 2
ORDER BY 1, 2;Если построить конверсию по дням и увидеть, что разница между группами быстро затухает, — скорее всего вы поймали реакцию на новизну, а не устойчивый эффект. Такой тест стоит покрутить дольше.
Множественная проверка гипотез
Когда в одном тесте проверяют сразу 10 метрик, вероятность поймать хотя бы одну ложную значимость резко растёт. Простейшая поправка — Бонферрони: делим уровень значимости α на число проверок.
-- 10 метрик → порог становится 0.05 / 10 = 0.005
-- значит нужен |z| > 2.81 вместо 1.96Бонферрони консервативен и на многих метриках режет мощность. Если проверок реально много, чаще берут менее строгие процедуры контроля FDR (например, Бенджамини — Хохберга).
Проверка мощности
После теста полезно прикинуть, какой минимальный эффект (MDE) вообще можно было засечь при набранном размере выборки:
WITH stats AS (
SELECT
variant,
AVG(converted) AS cr,
COUNT(*) AS n
FROM experiment
GROUP BY variant
)
SELECT
MAX(n) AS max_group_size,
-- приблизительный минимально детектируемый эффект при текущем N
1.96 * SQRT(2 * 0.1 * 0.9 / MAX(n)) AS detectable_diff
FROM stats;Смысл в том, чтобы отличить «эффекта нет» от «выборки не хватило». Если MDE оказался, скажем, 2 п.п., а вы искали прирост в 0.5 п.п., то незначимый результат ничего не доказывает — тест был недостаточно мощным.
Sample Ratio Mismatch (SRM)
Если сплит задумывался 50/50, а по факту группы разъехались — это SRM, признак поломки рандомизации или логирования. Сначала смотрят фактические доли:
SELECT
variant,
COUNT(*) AS users,
COUNT(*) * 100.0 / SUM(COUNT(*)) OVER () AS pct -- фактическая доля группы
FROM experiment
GROUP BY variant;Даже небольшой перекос (например, 53/47 на сотнях тысяч пользователей) — тревожный сигнал: значит, распределение по группам зависит от чего-то ещё, и сравнивать метрики нельзя. Формально наличие SRM проверяют критерием хи-квадрат. Тест с подтверждённым SRM исключают из анализа, пока не найдена причина.
CUPED через SQL
CUPED снижает дисперсию метрики за счёт предэкспериментального ковариата (поведение пользователя до теста). Меньше дисперсия — уже доверительный интервал — быстрее набирается значимость.
WITH theta_calc AS (
SELECT
COVAR_POP(post_metric, pre_metric) / VAR_POP(pre_metric) AS theta, -- коэффициент theta
AVG(pre_metric) AS mean_pre
FROM experiment
)
SELECT
variant,
AVG(post_metric - t.theta * (pre_metric - t.mean_pre)) AS cuped_metric
FROM experiment, theta_calc t
GROUP BY variant;Условие применимости: ковариат должен коррелировать с метрикой и быть измерен до старта теста (иначе он сам зависит от воздействия). Чаще всего в роли pre_metric берут ту же метрику за период до эксперимента.
Как это спрашивают на собесе
Типичная формулировка: «Тест открутили. Конверсия контроля 10%, теста 11%. Значимо?» От вас ждут не ответ «да/нет», а рассуждение по шагам:
- Достать фактические числа — размеры групп и конверсии.
- Посчитать стандартную ошибку разницы.
- Получить z-статистику как отношение разницы к ошибке.
- Сравнить с порогом 1.96 (или построить доверительный интервал).
Хороший кандидат по ходу проговаривает, что 1 п.п. разницы — это lift 10%, что без размера выборки значимость не определить, и что параллельно надо глянуть SRM и guardrail-метрики. Это показывает и знание статистики, и владение SQL-синтаксисом.
Частые ошибки
- Смотреть только на основную метрику. Улучшив конверсию, легко просадить выручку или разогнать возвраты. Всегда проверяйте guardrail-метрики.
- Игнорировать сегменты. Гетерогенность эффекта прячет результат: рост в одной подгруппе гасится падением в другой, и в среднем видно ноль.
- Не проверять SRM. Перекос сплита ломает весь анализ и при этом остаётся тихой, невидимой ошибкой, если её специально не искать.
- Неверно трактовать p-value. «p < 0.05 — значит эффект большой» — неправда. Значимость говорит о статистике, а не о практической величине эффекта; для неё смотрите на размер разницы и доверительный интервал.
- Подглядывать в тест раньше времени. Множественные промежуточные проверки без поправок раздувают ложноположительные результаты — либо фиксируйте срок заранее, либо используйте sequential-методы.
Связанные темы
- Как посчитать конверсию в SQL
- A/B-тест простыми словами
- p-value простыми словами
- CUPED простыми словами
FAQ
Обязателен ли Python для анализа A/B?
Для точных расчётов — да: p-value через нормальное или t-распределение, sequential-тесты, бутстрап удобнее считать в Python (scipy, statsmodels). Но базовые вещи — конверсия, lift, стандартная ошибка, z-статистика, доверительный интервал — прекрасно считаются в SQL, и для быстрой проверки этого достаточно.
Можно ли делать sequential-тест в SQL?
Технически можно посчитать границы прямо в запросе, но на практике sequential-анализ (mSPRT, group sequential) почти всегда делают в Python — там проще управлять границами остановки и поправками. SQL остаётся для агрегации данных, которые уходят в статистический движок.
Потянет ли SQL анализ на больших данных?
На миллионах строк SQL работает отлично — это его родная задача. На миллиардах событий агрегацию обычно выносят в специализированные системы (ClickHouse, Spark), но логика запросов остаётся той же: сгруппировать по варианту, посчитать долю, ошибку и разницу.
Как в SQL получить p-value, если нет функции нормального CDF?
В чистом Postgres встроенной функции нормального распределения нет. Варианты: сравнивать z-статистику с критическим значением (1.96 для 95%), аппроксимировать CDF формулой прямо в SQL, либо экспортировать агрегаты в Python и добить там. Для рабочей прикидки хватает сравнения с порогом.
Чем абсолютный прирост отличается от относительного lift?
Абсолютная разница — это разность конверсий в процентных пунктах (11% − 10% = 1 п.п.). Относительный lift — во сколько раз тест лучше контроля ((11 − 10) / 10 = 10%). Бизнесу обычно показывают lift, но в отчёте всегда явно указывайте, о какой из двух величин идёт речь, чтобы не завысить эффект в десять раз.
Тренируйте продуктовую аналитику — откройте тренажёр с 1500+ вопросами для собесов.