A/B-тест в SQL: конверсия, проверка сплита и разбор по сегментам

Коротко

SQL в A/B-тесте отвечает за то, что калькулятор проверить не может: кто попал в группы, сколько их и что считать конверсией. Статистику тоже можно посчитать запросом, но ошибки чаще прячутся в данных, чем в формуле.

• Считайте на таблице «одна строка — один участник». В учебной базе это experiment_exposures: 3 068 строк, 3 068 разных пользователей, никто не попал в обе группы.
• Конверсия: control 306 из 1 527 (20,0%), checklist 467 из 1 541 (30,3%). Разница +10,27 п.п., относительный прирост +51,2%.
• Сплит 1 527 против 1 541 нормален: χ² = 0,064 при пороге 12,12. Но в срезе «страна не указана» все 35 участников оказались в контроле.
• z = 6,55, 95%-й интервал разницы — от +7,22 до +13,31 п.п. В PostgreSQL 16 p-value считается через erfc(), в DuckDB — калькулятором.
• По устройствам: десктоп +19,75 п.п. (интервал от +15,37 до +24,13), мобильные −0,16 п.п. (от −4,25 до +3,92). Решение нужно принимать по сегментам, заявленным до теста.
• Если смотреть на результат каждую неделю и остановиться при первом значимом ответе, это случится после второй недели с оценкой +16,2 п.п. — в полтора раза выше итоговой.

Какие данные нужны, чтобы посчитать A/B-тест в SQL

Минимальная таблица эксперимента — одна строка на участника: кто, в какой группе, когда увидел изменение и достиг ли цели. В учебной базе это experiment_exposures с колонками user_id, experiment_name, variant, exposed_at и converted. Устройство и страна лежат в users и присоединяются по user_id.

Слово «экспозиция» здесь важно. В тест попадает не каждый зарегистрированный, а тот, кому показали экран с экспериментом. В учебной базе это 3 068 из 4 613 пользователей, 66,5%. Остальные 1 545 в эксперименте не участвовали, и считать их контролем нельзя: к этой ошибке мы ещё вернёмся.

Прежде чем считать конверсию, проверьте зерно. Если строк больше, чем пользователей, кто-то попал в тест дважды и будет посчитан дважды. Если пользователь есть в обеих группах, его конверсия уйдёт в обе стороны и сотрёт разницу. Если в таблице несколько экспериментов, их нужно отфильтровать по experiment_name, иначе группы смешаются.

SELECT
  count(*)                        AS exposures,
  count(DISTINCT user_id)         AS users,
  count(DISTINCT experiment_name) AS experiments,
  (SELECT count(*)
   FROM (SELECT user_id
         FROM experiment_exposures
         GROUP BY user_id
         HAVING count(DISTINCT variant) > 1) AS both_groups
  )                               AS users_in_both_groups
FROM experiment_exposures;

-- 3068 | 3068 | 1 | 0

Как посчитать конверсию по группам одним запросом

Когда таблица чистая, сама конверсия — один GROUP BY. Числитель — участники с converted = true, знаменатель — все участники группы. FILTER (WHERE …) работает в PostgreSQL и DuckDB; в MySQL и ClickHouse то же самое пишется через sum(CASE WHEN converted THEN 1 ELSE 0 END).

Обратите внимание на 100.0 в начале формулы. count() возвращает целое, и в PostgreSQL 467 / 1541 даёт 0: деление целых отбрасывает остаток. Эффект эксперимента исчезает на арифметике, а не в данных. Подробнее про эту ловушку — в статье про CAST и типы данных.

SELECT
  variant,
  count(*)                                   AS users,
  count(*) FILTER (WHERE converted)          AS converted_users,
  round(100.0 * count(*) FILTER (WHERE converted)
        / count(*), 1)                      AS conversion_pct
FROM experiment_exposures
WHERE experiment_name = 'onboarding_checklist'
GROUP BY variant
ORDER BY variant DESC;

Проверка сплита: 1 527 против 1 541 — это нормально?

Тест планировали как 50/50, а получили 49,77% и 50,23%. Небольшое расхождение — норма: назначение случайно, и ровно пополам оно не делит. Вопрос в том, насколько расхождение велико для такого числа участников. Для этого считают χ² — сумму квадратов отклонений наблюдаемых размеров групп от ожидаемых, делённых на ожидаемые.

При 3 068 участниках ожидается 1 534 в каждой группе. Отклонения — 7 в каждую сторону, χ² = (7² + 7²) / 1 534 = 0,064. Проверку SRM принято делать с жёстким порогом α = 0,0005, потому что её запускают на каждом тесте и в каждом срезе. Для двух групп это χ² больше 12,12. До порога далеко; калькулятор SRM даёт для этих чисел p = 0,80.

WITH groups AS (
  SELECT variant, count(*) AS n
  FROM experiment_exposures
  GROUP BY variant
),
expected AS (
  SELECT variant, n, sum(n) OVER () / 2.0 AS e
  FROM groups
)
SELECT
  sum(n) AS total,
  round(100.0 * sum(n) FILTER (WHERE variant = 'control') / sum(n), 2) AS control_share_pct,
  round(sum(power(n - e, 2) / e), 3) AS chi_square
FROM expected;

-- 3068 | 49.77 | 0.064

Где SRM прячется: проверка сплита внутри сегментов

Общий сплит может быть идеальным, а внутри сегмента — сломанным. Если вы собираетесь смотреть эффект по стране или устройству, сплит нужно проверить и там. Запрос тот же, только с GROUP BY по сегменту.

В учебной базе у 35 участников страна не указана, и все 35 оказались в контроле. χ² = 35 при пороге 12,12, p ≈ 3 · 10⁻⁹. На общий результат это почти не влияет: 35 человек из 3 068. Но в продукте такой рисунок обычно означает конкретную поломку, например правило «нет геоданных — показываем старый экран». Сравнивать группы в этом сегменте бессмысленно: сравнивать не с чем.

Армения с 154 против 202 выглядит подозрительно, χ² = 6,47, p = 0,011. При обычном пороге 0,05 это был бы сигнал, при пороге SRM — нет. Если проверить десяток срезов, одно такое число появится почти наверняка случайно, поэтому порог и строже.

SELECT
  coalesce(u.country, 'не указана')               AS country,
  count(*) FILTER (WHERE x.variant = 'control')   AS control,
  count(*) FILTER (WHERE x.variant = 'checklist') AS checklist,
  round(power(count(*) FILTER (WHERE x.variant = 'control') - count(*) / 2.0, 2) / (count(*) / 2.0)
      + power(count(*) FILTER (WHERE x.variant = 'checklist') - count(*) / 2.0, 2) / (count(*) / 2.0),
        2)                                        AS chi_square
FROM experiment_exposures x
JOIN users u ON u.user_id = x.user_id
GROUP BY 1
ORDER BY chi_square DESC;

Как посчитать разницу, прирост и доверительный интервал в SQL

Абсолютная разница — это разность конверсий в процентных пунктах: 30,30% − 20,04% = +10,27 п.п. Относительный прирост — отношение разницы к контролю: +51,2%. Обе величины верны, но отвечают на разные вопросы. Менеджеру, который считает деньги, нужна первая: из каждой тысячи участников до цели дойдут примерно на 103 человека больше. Относительная звучит эффектнее и легко вводит в заблуждение, когда база маленькая.

Остальное — формулы для двух долей, те же, что в калькуляторе значимости. z проверяет гипотезу «разницы нет», поэтому его стандартная ошибка считается по объединённой доле обеих групп. Доверительный интервал описывает величину разницы, и для него ошибка считается по раздельным долям. Если перепутать, интервал и вывод о значимости начнут противоречить друг другу.

WITH g AS (
  SELECT
    count(*) FILTER (WHERE variant = 'control')                 AS n_c,
    count(*) FILTER (WHERE variant = 'control' AND converted)   AS x_c,
    count(*) FILTER (WHERE variant = 'checklist')               AS n_t,
    count(*) FILTER (WHERE variant = 'checklist' AND converted) AS x_t
  FROM experiment_exposures
),
p AS (
  SELECT *,
         x_c * 1.0 / n_c                 AS p_c,
         x_t * 1.0 / n_t                 AS p_t,
         (x_c + x_t) * 1.0 / (n_c + n_t) AS p_pool
  FROM g
),
se AS (
  SELECT *,
         sqrt(p_pool * (1 - p_pool) * (1.0 / n_c + 1.0 / n_t)) AS se_pooled,
         sqrt(p_c * (1 - p_c) / n_c + p_t * (1 - p_t) / n_t)   AS se_diff
  FROM p
)
SELECT
  round(100 * p_c, 2)                          AS control_pct,
  round(100 * p_t, 2)                          AS checklist_pct,
  round(100 * (p_t - p_c), 2)                  AS diff_pp,
  round(100 * (p_t / p_c - 1), 1)              AS lift_pct,
  round((p_t - p_c) / se_pooled, 2)            AS z,
  round(100 * (p_t - p_c - 1.96 * se_diff), 2) AS ci_low_pp,
  round(100 * (p_t - p_c + 1.96 * se_diff), 2) AS ci_high_pp
FROM se;

-- 20.04 | 30.30 | 10.27 | 51.2 | 6.55 | 7.22 | 13.31

Где взять p-value: PostgreSQL, DuckDB и калькулятор

Для двустороннего z-теста p-value равно erfc(|z| / √2). В PostgreSQL 16 функции erf() и erfc() встроены, и p-value можно добавить к запросу выше последней колонкой. Для z = 6,55 получится 5,8 · 10⁻¹¹. В DuckDB и старых версиях PostgreSQL такой функции нет. Там хватает сравнения |z| с 1,96: если больше, разница значима на уровне 5%.

Считать p-value в SQL полезно, когда тестов много и результат попадает в регулярный отчёт. Для разового решения проще отдать четыре числа калькулятору значимости: 1 527 и 306, 1 541 и 467. Он покажет те же z и интервал и заодно проверит, работает ли нормальное приближение. Если ответы SQL и калькулятора расходятся, ищите ошибку в данных запроса, а не в калькуляторе.

Интервал от +7,22 до +13,31 п.п. говорит больше, чем p-value. Даже в худшем правдоподобном случае чек-лист добавляет больше семи пунктов. Это средний эффект по всем участникам. Проблема в том, что усреднять здесь нечего.

WITH g AS (
  SELECT u.device,
    count(*) FILTER (WHERE x.variant = 'control')                   AS n_c,
    count(*) FILTER (WHERE x.variant = 'control' AND x.converted)   AS x_c,
    count(*) FILTER (WHERE x.variant = 'checklist')                 AS n_t,
    count(*) FILTER (WHERE x.variant = 'checklist' AND x.converted) AS x_t
  FROM experiment_exposures x
  JOIN users u ON u.user_id = x.user_id
  GROUP BY u.device
),
z AS (
  SELECT device,
         (x_t * 1.0 / n_t - x_c * 1.0 / n_c)
           / sqrt((x_c + x_t) * 1.0 / (n_c + n_t)
                  * (1 - (x_c + x_t) * 1.0 / (n_c + n_t))
                  * (1.0 / n_c + 1.0 / n_t)) AS z
  FROM g
)
SELECT device, round(z, 2) AS z, erfc(abs(z) / sqrt(2)) AS p_value
FROM z
ORDER BY device;

-- desktop |  8.61 | 7.4e-18
-- mobile  | -0.08 | 0.94

Почему средний эффект +10,3 п.п. нельзя переносить на всех

Мобильные дизайнеры с самого начала говорили, что на телефоне чек-лист не помещается на первый экран. Это гипотеза с механизмом, и её можно проверить тем же запросом с GROUP BY device. Устройство записано при регистрации, до попадания в тест, и от варианта не зависит: в контроле 48,3% мобильных, в checklist — 47,6%. Значит, сравнивать группы внутри устройства честно.

Результат разбивает средний эффект на два непохожих. На десктопе конверсия выросла с 20,0% до 39,8%, почти вдвое: +19,75 п.п., интервал от +15,37 до +24,13. На мобильных — 20,1% и 19,9%: −0,16 п.п., интервал от −4,25 до +3,92. Общие +10,3 п.п. — это примерно половина десктопного эффекта, размазанная на всех, потому что мобильных в тесте почти половина.

WITH g AS (
  SELECT
    u.device,
    count(*) FILTER (WHERE x.variant = 'control')                   AS n_c,
    count(*) FILTER (WHERE x.variant = 'control' AND x.converted)   AS x_c,
    count(*) FILTER (WHERE x.variant = 'checklist')                 AS n_t,
    count(*) FILTER (WHERE x.variant = 'checklist' AND x.converted) AS x_t
  FROM experiment_exposures x
  JOIN users u ON u.user_id = x.user_id
  GROUP BY u.device
),
p AS (
  SELECT *, x_c * 1.0 / n_c AS p_c, x_t * 1.0 / n_t AS p_t
  FROM g
)
SELECT
  device, n_c, n_t,
  round(100 * p_c, 1)         AS control_pct,
  round(100 * p_t, 1)         AS checklist_pct,
  round(100 * (p_t - p_c), 2) AS diff_pp,
  round(100 * (p_t - p_c - 1.96 * sqrt(p_c * (1 - p_c) / n_c + p_t * (1 - p_t) / n_t)), 2) AS ci_low_pp,
  round(100 * (p_t - p_c + 1.96 * sqrt(p_c * (1 - p_c) / n_c + p_t * (1 - p_t) / n_t)), 2) AS ci_high_pp
FROM p
ORDER BY device;

Как решать по сегментам и не придумать эффект

Срез по устройствам меняет решение, но сам по себе он ещё не доказательство. Прежде чем опираться на сегмент, проверьте три вещи. Сегментный признак должен быть известен до теста и не зависеть от варианта. Гипотезу о сегменте должны были высказать до того, как увидели цифры. И разница между эффектами в сегментах должна быть больше шума.

Последнее считается так же, как разница групп. Разница эффектов: 19,75 − (−0,16) = 19,9 п.п. Её стандартная ошибка — корень из суммы квадратов ошибок двух эффектов: √(2,24² + 2,08²) = 3,06 п.п. Интервал — от 13,9 до 25,9 п.п. Ноль далеко, то есть эффекты на десктопе и мобильных действительно разные.

Теперь для контраста срез, которого не было в плане. По странам эффект скачет от +13,6 п.п. в России до +1,4 п.п. в Беларуси. Соблазнительно написать, что в Беларуси чек-лист не работает. Но там 279 участников, и интервал разницы тянется от −8,9 до +11,7 п.п.: в него помещаются и ноль, и общий эффект. Механизма, который связывал бы страну с чек-листом, никто не называл. Это шум, который выглядит как находка, если смотреть только на точечные оценки.

Осторожность нужна и в хорошем выводе. Для мобильных тест не доказал, что вреда нет: интервал допускает −4,25 п.п. Правильная формулировка — «на мобильных эффекта не видно, и тест не исключает небольшого ухудшения». Если мобильную версию чек-листа переделают, её нужно тестировать заново и считать размер выборки под нужную точность.

Что показывает подглядывание в результаты по неделям

Тест шёл 13 недель: участников записывали с 1 июня по 29 августа. Представьте, что аналитик смотрел на результат каждый понедельник и остановил бы тест при первом значимом ответе. Накопленную разницу на каждую неделю можно восстановить одним запросом: сначала группы по неделям, потом накопительные суммы оконной функцией.

После первой недели разница +10,3 п.п. при 131 участнике, интервал от −4,1 до +24,8: вывода нет. После второй — +16,2 п.п., интервал от +6,5 до +25,8, z = 3,19. Тест «значим», и в отчёт ушла бы оценка в полтора раза выше итоговой. Дальше она сползает к +7,8 п.п. в конце июля и возвращается к +10,3 к концу теста.

Здесь эффект настоящий, поэтому ранняя остановка дала бы не ложный результат, а завышенный. Если бы эффекта не было, регулярные проверки с порогом 0,05 нашли бы его гораздо чаще, чем в 5% случаев. Поэтому длительность и число проверок фиксируют до старта, а для частого мониторинга используют последовательные методы с поправкой.

WITH weekly AS (
  SELECT date_trunc('week', exposed_at)::DATE AS week,
         count(*) FILTER (WHERE variant = 'control')                 AS n_c,
         count(*) FILTER (WHERE variant = 'control' AND converted)   AS x_c,
         count(*) FILTER (WHERE variant = 'checklist')               AS n_t,
         count(*) FILTER (WHERE variant = 'checklist' AND converted) AS x_t
  FROM experiment_exposures
  GROUP BY 1
),
running AS (
  SELECT week,
         sum(n_c) OVER (ORDER BY week) AS n_c,
         sum(x_c) OVER (ORDER BY week) AS x_c,
         sum(n_t) OVER (ORDER BY week) AS n_t,
         sum(x_t) OVER (ORDER BY week) AS x_t
  FROM weekly
),
p AS (
  SELECT week, n_c + n_t AS users_so_far,
         x_t * 1.0 / n_t - x_c * 1.0 / n_c AS diff,
         sqrt((x_c * 1.0 / n_c) * (1 - x_c * 1.0 / n_c) / n_c
            + (x_t * 1.0 / n_t) * (1 - x_t * 1.0 / n_t) / n_t) AS se
  FROM running
)
SELECT week, users_so_far,
       round(100 * diff, 1)               AS diff_pp,
       round(100 * (diff - 1.96 * se), 1) AS ci_low_pp,
       round(100 * (diff + 1.96 * se), 1) AS ci_high_pp
FROM p
ORDER BY week;

Частые ошибки в SQL-расчёте A/B-теста

Ни одна из этих ошибок не даёт сообщения об ошибке. Запрос выполняется, числа выглядят правдоподобно, и только сравнение с проверкой зерна показывает, что считали не то.

Самая дорогая — взять users, присоединить эксперимент через LEFT JOIN и записать всех без группы в контроль через coalesce(variant, 'control'). В учебной базе контроль вырастает до 3 072 человек, конверсия в нём падает до 10,0%, и эффект чек-листа «утраивается» до +20,3 п.п. Люди, которые не видели экран эксперимента, не могли и сконвертироваться в нём.

Вторая по частоте — присоединить events, чтобы что-то досчитать, и не заметить, что строк стало больше. После JOIN с событиями в контроле 11 713 строк вместо 1 527, и конверсия по строкам — 20,2% и 29,7% вместо 20,0% и 30,3%. Активные пользователи с десятками событий получают больший вес. Сдвиг небольшой, поэтому его и не замечают.

• Нет проверки зерна: повторная экспозиция считает человека дважды.
• Пользователь в обеих группах: его конверсия попадает в обе стороны и размывает разницу.
• Не участники теста в знаменателе: назначенные, но не увидевшие экран, или вообще все пользователи.
• Конверсия, случившаяся до экспозиции: действие не могло быть следствием варианта. В учебной базе событий до exposed_at нет, но проверять нужно всегда.
• Незрелые участники: у тех, кто попал в тест в последний день, ещё не было времени сконвертироваться. Для метрик с окном, например «конверсия за 7 дней», последние дни теста исключают.
• Целочисленное деление в PostgreSQL: 467 / 1541 = 0.
• Остановка по первому значимому результату: оценка завышена, а без эффекта растёт доля ложных находок.
• Срезы по всему подряд: из десятка сегментов один покажет «особый» эффект случайно.

Что отдать менеджеру после A/B-теста

Менеджеру не нужны z и χ². Ему нужно решение, число, на котором оно держится, и риск. Хорошая записка по этому тесту умещается в пять строк, и за каждой строкой стоит один из запросов выше.

• Данные: 3 068 участников, сплит 1 527 / 1 541 в норме; 35 участников без страны все попали в контроль — передать разработке.
• Общий результат: конверсия 20,0% → 30,3%, +10,3 п.п., 95%-й интервал от +7,2 до +13,3.
• Сегменты: весь эффект на десктопе (+19,8 п.п.), на мобильных эффекта не видно (−0,2 п.п., интервал ±4 п.п.). Разница между устройствами значима.
• Решение: включить чек-лист на десктопе; на мобильных оставить текущий онбординг.
• Следующий шаг: переделать мобильную версию и протестировать её отдельно с размером выборки под нужный эффект.

Итог

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

Этот эксперимент — сюжет семнадцатой главы SQL-курса: там его разбирают на учебном срезе, и задания проверяются автоматически. Полная таблица experiment_exposures лежит в песочнице курса, открытой без регистрации, — все запросы статьи можно запустить там и сверить с результатами выше.