Прирост, значимость и контрольные показатели в SQL
Вычисление прироста конверсии и проверок данных, выявляющих неработающий эксперимент
«Прирост, значимость и контрольные показатели в SQL» — бесплатный урок Coding Interview Prep на CoddyKit. Это урок 4 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения Coding Interview Prep, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс Coding Interview Prep содержит 4 уроков всего.
От метрик к решению
Конверсия для каждого варианта — лишь начало. Вопрос на собеседовании: действительно ли тестовый вариант победил? Это означает вычислить прирост, оценить, является ли разница реальной или случайным шумом, и проверить защитные метрики, которые выявляют некорректный эксперимент.
Вам не придётся запускать полный статистический пакет в SQL, но Вы можете вычислить входные данные и приблизительный показатель значимости, который интервьюеры хотят увидеть.
Сводная таблица для каждого варианта на основе CTE
Всё последующее строится на одной аккуратной сводке: для каждого варианта — число пользователей n, число пользователей с конверсией c и коэффициент конверсии p. Вычислите её один раз в CTE и повторно используйте.
WITH summary AS (
SELECT
variant,
COUNT(DISTINCT user_id) AS n,
COUNT(DISTINCT converted_user) AS c
FROM experiment_flat
GROUP BY variant
)
SELECT
variant, n, c,
1.0 * c / n AS p
FROM summary;Абсолютный и относительный прирост
Существуют два определения прироста, и по умолчанию интервьюеры ожидают относительное:
- Абсолютный прирост = коэффициент конверсии тестового варианта − коэффициент конверсии контрольного варианта (разница в процентных пунктах).
- Относительный прирост = (коэффициент конверсии тестового варианта − коэффициент конверсии контрольного варианта) / коэффициент конверсии контрольного варианта (процентное улучшение).
Фразы «рост на 2 пункта» и «относительный прирост на 20%» могут описывать один и тот же результат. Формулируйте вывод однозначно.
Вычисление прироста с помощью самосведения
Чтобы сравнить два варианта в одной строке, получите контрольный и тестовый варианты рядом с помощью условной агрегации, а затем выполните арифметические действия.
Так Вы избежите ненадёжного самосоединения и сохраните формулу прироста понятной.
WITH s AS (
SELECT variant,
COUNT(DISTINCT user_id) AS n,
COUNT(DISTINCT converted_user) AS c
FROM experiment_flat GROUP BY variant
),
rates AS (
SELECT
MAX(CASE WHEN variant='control' THEN 1.0*c/n END) AS p_ctrl,
MAX(CASE WHEN variant='treatment' THEN 1.0*c/n END) AS p_trt
FROM s
)
SELECT
p_ctrl, p_trt,
p_trt - p_ctrl AS abs_lift,
ROUND(100.0 * (p_trt - p_ctrl) / p_ctrl, 2) AS rel_lift_pct
FROM rates;Почему разница может оказаться шумом
Более высокий коэффициент конверсии тестового варианта может быть результатом случайности выборки. Статистическая значимость отвечает на вопрос: насколько вероятна настолько большая разница, если варианты действительно полностью одинаковы?
Ключевой показатель — стандартная ошибка каждого коэффициента, которая уменьшается с ростом размера выборки. Большие выборки делают небольшие приросты надёжными, а маленькие — заставляют сомневаться даже в больших приростах.
Стандартная ошибка доли
Для коэффициента конверсии p по n пользователям стандартная ошибка равна sqrt(p * (1 - p) / n). Вычисляйте её непосредственно в SQL для каждого варианта.
Так Вы количественно оцениваете колебания каждого коэффициента до их сравнения.
WITH s AS (
SELECT variant,
COUNT(DISTINCT user_id) AS n,
COUNT(DISTINCT converted_user) AS c
FROM experiment_flat GROUP BY variant
)
SELECT
variant, n,
1.0 * c / n AS p,
SQRT( (1.0*c/n) * (1 - 1.0*c/n) / n ) AS std_err
FROM s;Z-оценка для двух долей
Приблизительным показателем значимости служит z-оценка для двух долей: разница между коэффициентами, делённая на стандартную ошибку этой разницы. Абсолютная величина, превышающая примерно 1.96, соответствует распространённому порогу 95%.
Ясно указывайте, что это приближение, а не замена полноценного статистического теста, но в SQL оно отвечает на вопрос «может ли эта разница быть реальной?»
WITH r AS (
SELECT
MAX(CASE WHEN variant='control' THEN 1.0*c/n END) AS p1,
MAX(CASE WHEN variant='control' THEN n END) AS n1,
MAX(CASE WHEN variant='treatment' THEN 1.0*c/n END) AS p2,
MAX(CASE WHEN variant='treatment' THEN n END) AS n2
FROM (
SELECT variant, COUNT(DISTINCT user_id) n,
COUNT(DISTINCT converted_user) c
FROM experiment_flat GROUP BY variant
) s
)
SELECT
p2 - p1 AS abs_lift,
(p2 - p1) / SQRT( p1*(1-p1)/n1 + p2*(1-p2)/n2 ) AS z_score
FROM r;Интерпретация z-оценки
Преобразуйте число в вывод, чтобы интервьюер услышал не только математику, но и понимание бизнеса:
|z| >= 1.96: разница статистически значима примерно при 95% доверии.|z| < 1.96: недостаточно доказательств; прирост может быть шумом.
Оформите это с помощью CASE, чтобы вывести понятную подпись, и всегда рассматривайте статистическую значимость вместе с практическим размером прироста.
SELECT
z_score,
CASE WHEN ABS(z_score) >= 1.96
THEN 'significant at 95%'
ELSE 'not significant' END AS verdict
FROM (
SELECT 2.3 AS z_score
) t;Несоответствие соотношения выборок (SRM)
Первая защитная проверка, о которой спрашивают интервьюеры: действительно ли пользователи распределились так, как было задумано? Эксперимент 50/50, в котором при миллионах пользователей получилось 53/47, — тревожный сигнал: случайное распределение или журналирование работает неправильно.
Сравните наблюдаемые количества с ожидаемым распределением. Большое отклонение делает весь эксперимент недействительным ещё до анализа метрики.
WITH cnt AS (
SELECT variant, COUNT(DISTINCT user_id) AS n
FROM experiment_flat GROUP BY variant
),
tot AS (SELECT SUM(n) AS total FROM cnt)
SELECT
c.variant, c.n,
ROUND(100.0 * c.n / t.total, 2) AS observed_pct,
50.0 AS expected_pct
FROM cnt c CROSS JOIN tot t;Защитные метрики
Защитная метрика — это метрика, которая не должна ухудшаться, даже если основная метрика улучшается. Классические защитные метрики: задержка страницы, доля возвратов, доля отписок и доля ошибок.
Выводите их для каждого варианта рядом с основной метрикой успеха. Тестовый вариант, который повышает конверсию, но вдвое увеличивает число возвратов, не является победой. Вычисление защитных метрик без подсказки показывает умение принимать продуктовые решения.
SELECT
variant,
AVG(load_ms) AS avg_latency_ms,
ROUND(100.0 * SUM(refunded) / COUNT(*), 2) AS refund_rate_pct,
ROUND(100.0 * SUM(errored) / COUNT(*), 2) AS error_rate_pct
FROM experiment_flat
GROUP BY variant;Полный итоговый отчёт
Полный отчёт по эксперименту, который нравится интервьюерам, объединяет в одном результате четыре элемента: коэффициенты для каждого варианта, относительный прирост, вывод о статистической значимости и проверку SRM. Объедините CTE и представьте результат в виде одной таблицы, готовой для принятия решения.
В заключение укажите: результат статистически значим, размер прироста приемлем, защитные метрики в норме, распределение сбалансировано — значит, внедряем или ждём.
WITH s AS (
SELECT variant, COUNT(DISTINCT user_id) n,
COUNT(DISTINCT converted_user) c
FROM experiment_flat GROUP BY variant
),
r AS (
SELECT
MAX(CASE WHEN variant='control' THEN 1.0*c/n END) p1,
MAX(CASE WHEN variant='control' THEN n END) n1,
MAX(CASE WHEN variant='treatment' THEN 1.0*c/n END) p2,
MAX(CASE WHEN variant='treatment' THEN n END) n2
FROM s
)
SELECT
ROUND(100.0*(p2-p1)/p1, 2) AS rel_lift_pct,
CASE WHEN ABS((p2-p1)/SQRT(p1*(1-p1)/n1 + p2*(1-p2)/n2)) >= 1.96
THEN 'significant' ELSE 'not significant' END AS verdict,
CASE WHEN ABS(1.0*n2/(n1+n2) - 0.5) > 0.02
THEN 'SRM warning' ELSE 'split ok' END AS srm_check
FROM r;Быстрая проверка
Тестовый вариант показывает относительный прирост конверсии на 25%, но в каждом варианте всего по 40 пользователей. Каков правильный вывод?
Итоги: прирост, статистическая значимость и защитные метрики
Теперь Вы умеете превращать исходные метрики вариантов в решение:
- Различайте абсолютный прирост (в процентных пунктах) и относительный прирост (в процентах).
- Вычисляйте стандартную ошибку каждого коэффициента и z-оценку для двух долей как приблизительный показатель значимости (|z| >= 1.96 ~ 95%).
- Выполняйте проверку SRM, чтобы убедиться, что распределение соответствует плану.
- Выводите защитные метрики, чтобы успешный результат не скрывал ухудшение.
- Представляйте единый итоговый отчёт, готовый для принятия решения, и всегда рассматривайте статистическую значимость вместе с практическим размером прироста.
На этом завершается анализ воронок и A/B-тестов в SQL.
Часто задаваемые вопросы
Урок «Прирост, значимость и контрольные показатели в SQL» бесплатный?
Да — полный текст урока «Прирост, значимость и контрольные показатели в SQL» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс Coding Interview Prep, подпишись на CoddyKit PRO. Курс Coding Interview Prep содержит 4 уроков всего.
Чему я научусь в уроке «Прирост, значимость и контрольные показатели в SQL»?
Вычисление прироста конверсии и проверок данных, выявляющих неработающий эксперимент Ты практикуешь Coding Interview Prep с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.
Нужен ли мне опыт, чтобы начать Coding Interview Prep?
Предыдущий опыт не требуется. Coding Interview Prep на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 4 из 4.
Сколько времени занимает урок «Прирост, значимость и контрольные показатели в SQL»?
Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.
Можно ли писать и запускать код в этом уроке Coding Interview Prep?
Да. Каждый урок Coding Interview Prep включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.
Все уроки этого курса
- Построение многоэтапной воронки
- Упорядоченные события и временные окна
- Распределение по группам A/B-теста и метрики
- Прирост, значимость и контрольные показатели в SQL