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

Зачем Confidence Interval

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

Формула

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

Для 95% CI: z = 1.96.

Для proportion:

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

CI для means

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 для proportions

Для CR / conversion rate:

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;

Wilson CI лучше для маленьких samples / extreme p:

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 не требует normality assumption. Создаём samples с repetition:

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
    -- SQL bootstrap упрощённо; в проде используйте 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;

В SQL bootstrap сложно (no random with replacement). Чаще делают вне SQL.

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

Ошибка 1. Использовать normal CI на skewed data. Revenue per user имеет heavy tail. Normal CI не работает. Используйте bootstrap.

Ошибка 2. CI для small n. Если n < 30, лучше t-distribution (но в проде обычно используют normal с n ≥ 30).

Ошибка 3. Confidence vs Credible Interval. CI — frequentist. Credible — bayesian. Не путайте.

Ошибка 4. Игнорировать correlations. Если data correlated (time series), CI underestimates uncertainty.

Ошибка 5. CI для difference of means. SE формула другая: sqrt(SE1² + SE2²). Не просто CI каждой группы.

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

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

FAQ

90% или 95% CI?

95% — стандарт. 90% — narrower (less confident). 99% — wider (more confident).

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

«Если повторим эксперимент 100 раз, в 95 случаях истинное значение окажется в этом интервале». НЕ «95% вероятность, что значение в интервале» (это credible interval).

CI не пересекаются — significant?

Грубо да, но точнее — z-test / t-test.

CI для медианы?

PERCENTILE_CONT 2.5% и 97.5% от bootstrap distribution.

CI narrow или wide — что лучше?

Narrow = больше certainty. Зависит от n и std: больше n → narrower CI.