Как посчитать Confidence Interval в SQL

Зачем Confidence Interval

«AOV в апреле — $50». Это точечная оценка. Confidence Interval добавляет к ней разброс: «$50 ± $3» (95% CI). Читается так: «истинный AOV скорее всего лежит между $47 и $53». Без интервала одна цифра выглядит уверенно, но по сути это гадание — вы не знаете, насколько ей можно верить.

Формула

CI = mean ± z × SE
SE = std / sqrt(n)

Для 95% CI: z = 1.96.

Для доли:

CI = p ± z × sqrt(p(1-p) / n)

CI для средних

WITH stats AS (
    SELECT
        AVG(total) AS mean_aov,
        STDDEV(total) AS std,
        COUNT(*) AS n
    FROM orders
    WHERE status = 'paid'
      AND created_at >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
    mean_aov,
    std,
    n,
    mean_aov - 1.96 * std / SQRT(n) AS ci_lower,
    mean_aov + 1.96 * std / SQRT(n) AS ci_upper,
    1.96 * std / SQRT(n) AS ci_radius
FROM stats;

CI для долей

Для конверсии (CR):

WITH stats AS (
    SELECT
        COUNT(*) AS n,
        COUNT(*) FILTER (WHERE event = 'purchase') AS conversions,
        COUNT(*) FILTER (WHERE event = 'purchase')::NUMERIC / NULLIF(COUNT(*), 0) AS p
    FROM funnel_events
    WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
    p,
    n,
    p - 1.96 * SQRT(p * (1 - p) / n) AS ci_lower,
    p + 1.96 * SQRT(p * (1 - p) / n) AS ci_upper,
    1.96 * SQRT(p * (1 - p) / n) AS ci_radius
FROM stats;

Интервал Уилсона точнее на маленьких выборках и при крайних значениях p (близких к 0 или 1):

WITH stats AS (
    SELECT
        COUNT(*) AS n,
        COUNT(*) FILTER (WHERE event = 'purchase') AS k
    FROM funnel_events
    WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
    k::NUMERIC / n AS p,
    (k + 1.96 * 1.96 / 2) / (n + 1.96 * 1.96)
        - 1.96 * SQRT((k::NUMERIC * (n - k) / n + 1.96 * 1.96 / 4) / (n + 1.96 * 1.96)) / (n + 1.96 * 1.96)
        AS wilson_lower,
    (k + 1.96 * 1.96 / 2) / (n + 1.96 * 1.96)
        + 1.96 * SQRT((k::NUMERIC * (n - k) / n + 1.96 * 1.96 / 4) / (n + 1.96 * 1.96)) / (n + 1.96 * 1.96)
        AS wilson_upper
FROM stats;

Bootstrap как альтернатива

Bootstrap не требует предположения о нормальности распределения. Идея — многократно пересобрать выборку из самой себя с возвращением и посмотреть, как гуляет среднее:

WITH samples AS (
    SELECT
        b AS bootstrap_id,
        AVG(total) AS resampled_mean
    FROM (
        SELECT total
        FROM orders
        WHERE status = 'paid'
          AND created_at >= CURRENT_DATE - INTERVAL '30 days'
    ) data
    CROSS JOIN generate_series(1, 1000) AS b
    -- bootstrap в SQL показан упрощённо; в проде считайте на Python или R
)
SELECT
    PERCENTILE_CONT(0.025) WITHIN GROUP (ORDER BY resampled_mean) AS ci_lower,
    PERCENTILE_CONT(0.975) WITHIN GROUP (ORDER BY resampled_mean) AS ci_upper
FROM samples
GROUP BY 1;

Полноценный bootstrap в SQL неудобен: в нём нет встроенной пересборки с возвращением. Поэтому его почти всегда делают вне SQL — на Python или R.

Частые ошибки

Ошибка 1. Считать обычный (нормальный) CI на скошенных данных. Выручка на пользователя почти всегда имеет тяжёлый правый хвост — несколько крупных плательщиков тянут распределение вправо. Нормальный CI на таких данных врёт; берите bootstrap.

Ошибка 2. Нормальный CI при малом n. Если наблюдений меньше 30, вместо z корректнее брать t-распределение — оно шире и честнее на маленьких выборках. На практике при n ≥ 30 разница между t и z уже незаметна, поэтому берут обычный z.

Ошибка 3. Путать доверительный и credible-интервал. Доверительный интервал (confidence) — из частотного подхода, credible interval — из байесовского, и трактуются они по-разному. Смешивать эти два понятия на собесе — классический прокол.

Ошибка 4. Игнорировать зависимость наблюдений. Если данные коррелированы (например, временной ряд), стандартная формула занижает неопределённость, и интервал получается уже, чем есть на самом деле.

Ошибка 5. Строить CI для разности средних как для одной группы. Для разницы двух средних стандартная ошибка считается иначе: sqrt(SE1² + SE2²). Нельзя просто построить отдельный интервал для каждой группы и сравнить их на глаз.

Связанные темы

• Как посчитать uplift в SQL
• Как посчитать sample size в SQL
• CUPED — снижение дисперсии
• Sample Ratio Mismatch (SRM)

FAQ

90% или 95% CI?

95% — стандарт де-факто: именно с ним публикуют результаты A/B-тестов и научные статьи. 90% даёт более узкий интервал, но вы чаще будете ошибаться; 99% — шире и надёжнее, его берут, когда цена ошибки высокая. Если сомневаетесь, какой уровень выбрать, берите 95%.

Что значит «95% CI»?

Это значит: «Если повторить эксперимент 100 раз, примерно в 95 из них истинное значение попадёт в построенный интервал». Это НЕ «с вероятностью 95% истинное значение лежит внутри интервала» — так трактуется credible interval из байесовского подхода, а не доверительный интервал.

Если CI двух групп не пересекаются — разница значима?

Как быстрая прикидка — да: если интервалы не перекрываются, разница почти наверняка значима. Но обратное неверно — интервалы могут слегка пересекаться, а разница всё равно окажется значимой. Точный ответ даёт z-тест или t-тест, а не сравнение интервалов на глаз.

Как построить CI для медианы?

Формула mean ± z × SE для медианы не подходит: она выведена для среднего. Медиану оценивают через bootstrap — пересобираете выборку тысячу раз, берёте медиану в каждой и отсекаете 2.5-й и 97.5-й перцентили получившегося распределения.

Узкий или широкий CI — что лучше?

Узкий интервал означает больше определённости: вы точнее знаете, где лежит истинное значение. Но ширина — это не то, что вы выбираете напрямую: она зависит от размера выборки и разброса данных. Чем больше n и чем меньше стандартное отклонение, тем уже интервал. Так что узкий CI — не самоцель, а следствие того, что данных набралось достаточно.