Как посчитать bootstrap CI в SQL

Зачем bootstrap CI

Bootstrap — универсальный способ получить confidence interval, когда формула неизвестна или допущения параметрических тестов нарушены. Работает на ресемплировании исходной выборки: «если бы я повторил эксперимент 1000 раз». Подходит для метрик типа: ratio (revenue / sessions), percentile, gini.

Идея ресемплирования

1. Из исходной выборки размера N берём с возвращением N элементов → bootstrap-sample.
2. Считаем нужную статистику (mean, median, p95).
3. Повторяем 1–2 B раз (B = 1000–10000).
4. CI = percentile [2.5%, 97.5%] от B полученных значений.

Bootstrap CI среднего

В Postgres с generate_series + ORDER BY random():

WITH source AS (
    SELECT user_id, revenue
    FROM ab_users
    WHERE experiment_id = 'pricing_v2' AND variant = 'B'
),
n_source AS (SELECT COUNT(*) AS n FROM source),
boot_iterations AS (
    SELECT generate_series(1, 1000) AS iteration
),
bootstrap_means AS (
    SELECT
        bi.iteration,
        AVG(s.revenue) AS boot_mean
    FROM boot_iterations bi
    CROSS JOIN LATERAL (
        SELECT revenue
        FROM source
        ORDER BY random()
        LIMIT (SELECT n FROM n_source)
    ) s
    GROUP BY bi.iteration
)
SELECT
    PERCENTILE_CONT(0.025) WITHIN GROUP (ORDER BY boot_mean) AS ci_lower,
    PERCENTILE_CONT(0.5)   WITHIN GROUP (ORDER BY boot_mean) AS boot_median,
    PERCENTILE_CONT(0.975) WITHIN GROUP (ORDER BY boot_mean) AS ci_upper
FROM bootstrap_means;

ORDER BY random() LIMIT N без возвращения — это не строгий bootstrap (subsampling). Для настоящего ресемпла с возвращением нужно генерировать N случайных индексов в [1, N] и тянуть строки по ним — в Postgres это удобнее делать через generate_series + row_number, а ещё лучше — вне SQL (numpy). На практике многие используют subsampling 90% (без возвращения), что близко к bootstrap на больших n.

Bootstrap для разности A-B

Считаем bootstrap-разницу средних:

WITH source_a AS (
    SELECT revenue FROM ab_users WHERE experiment_id = 'pricing_v2' AND variant = 'A'
),
source_b AS (
    SELECT revenue FROM ab_users WHERE experiment_id = 'pricing_v2' AND variant = 'B'
),
boot AS (
    SELECT
        gs AS iteration,
        (SELECT AVG(revenue) FROM (
            SELECT revenue FROM source_b ORDER BY random()
            LIMIT (SELECT COUNT(*) FROM source_b)
        ) b) -
        (SELECT AVG(revenue) FROM (
            SELECT revenue FROM source_a ORDER BY random()
            LIMIT (SELECT COUNT(*) FROM source_a)
        ) a) AS diff
    FROM generate_series(1, 1000) gs
)
SELECT
    PERCENTILE_CONT(0.025) WITHIN GROUP (ORDER BY diff) AS ci_lower,
    PERCENTILE_CONT(0.975) WITHIN GROUP (ORDER BY diff) AS ci_upper,
    AVG(diff) AS mean_diff,
    CASE
        WHEN PERCENTILE_CONT(0.025) WITHIN GROUP (ORDER BY diff) > 0
          OR PERCENTILE_CONT(0.975) WITHIN GROUP (ORDER BY diff) < 0
        THEN 'CI excludes 0 → significant'
        ELSE 'CI contains 0 → NOT significant'
    END AS verdict
FROM boot;

Bias-corrected CI

Простой percentile CI смещён на скошенных распределениях. Bias-corrected (BCa) — стандарт в продакшене, но в чистом SQL громоздко. Минимальная коррекция через z0:

z0 = Φ⁻¹(P(boot_stat < observed_stat))

В Postgres inverse_normal нет — обычно делают вне (Python, R).

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

Ошибка 1. Subsampling вместо bootstrap. ORDER BY random() LIMIT N без возвращения — это subsample 100%, то есть тот же набор. Для настоящего ресемпла нужны повторения.

Ошибка 2. Слишком мало итераций. B=100 — слишком шумит. Минимум 1000, лучше 10000 для стабильного 95% CI.

Ошибка 3. Bootstrap на сильно зависимых данных. Кросс-сессионные метрики, повторные клики — нарушают i.i.d. Используйте block bootstrap или агрегируйте до user-level.

Ошибка 4. CI для percentile через mean. Если bootstrap-метрика — это p99, считайте PERCENTILE_CONT(0.99) внутри итерации, не среднее.

Ошибка 5. Не сохранять seed. Если хотите воспроизводимость — SETSEED(0.42) в начале сессии. Иначе каждый запуск даст немного разный CI.

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

• Как посчитать t-test в SQL
• Как посчитать Mann-Whitney test в SQL
• Что такое bootstrap простыми словами
• Bootstrap в A/B-тестах

FAQ

Сколько итераций B?

1000 для дашборда, 10000 для отчёта в продакшене.

Bootstrap в SQL — это нормально?

Для разовых анализов — да. Для регулярных пайплайнов — лучше вынести в Python (numpy.random + numba).

Bootstrap vs t-test?

Bootstrap не требует нормальности и работает для любой статистики. T-test — быстрее, но только для средних на нормальных-ish данных.

Bias-correction нужна?

На сильно скошенных метриках (p99 revenue) — да. На «обычном» среднем — percentile CI близок к BCa.

Можно ли паралелить?

В SQL — нет встроенной параллели для bootstrap. В DuckDB лучше; в production — Spark / Ray.