Как посчитать distribution drift в SQL

Зачем мониторить drift

ML-модель обучена на распределении прошлого. Если входные фичи дрейфуют (среднее, дисперсия, доли классов), модель деградирует тихо. То же про продуктовые метрики: «средний чек упал» может означать «изменилась структура клиентов», а не «продукт стал хуже». Drift detection — first-line indicator.

PSI: Population Stability Index

Стандарт банков и credit-scoring. Формула на бакеты:

PSI = Σ over buckets of (p_curr − p_base) × ln(p_curr / p_base)

Где p_base — доля наблюдений в бакете на референс-периоде, p_curr — на текущем.

PSI в SQL

Разделим на 10 квантилей по референс-периоду и сравним:

WITH base AS (
    SELECT amount, NTILE(10) OVER (ORDER BY amount) AS bucket
    FROM orders
    WHERE created_at BETWEEN '2026-04-01' AND '2026-04-30'
),
base_thresholds AS (
    SELECT
        bucket,
        MIN(amount) AS lo,
        MAX(amount) AS hi,
        COUNT(*)::NUMERIC / SUM(COUNT(*)) OVER () AS p_base
    FROM base
    GROUP BY bucket
),
current_period AS (
    SELECT amount
    FROM orders
    WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
),
current_distribution AS (
    SELECT
        bt.bucket,
        bt.p_base,
        COUNT(c.amount)::NUMERIC / NULLIF(SUM(COUNT(c.amount)) OVER (), 0) AS p_curr
    FROM base_thresholds bt
    LEFT JOIN current_period c
      ON c.amount BETWEEN bt.lo AND bt.hi
    GROUP BY bt.bucket, bt.p_base
)
SELECT
    SUM((p_curr - p_base) * LN(NULLIF(p_curr, 0) / NULLIF(p_base, 0))) AS psi
FROM current_distribution;

LN на нуле даёт NULL/ошибку — добавляйте NULLIF или используйте формулу со сглаживанием Лапласа (+ 0.0001).

Chi-square для категорий

Для дискретных переменных (canal, country, plan):

WITH base AS (
    SELECT channel, COUNT(*) AS n FROM events
    WHERE event_date BETWEEN '2026-04-01' AND '2026-04-30'
    GROUP BY channel
),
current_period AS (
    SELECT channel, COUNT(*) AS n FROM events
    WHERE event_date >= CURRENT_DATE - INTERVAL '7 days'
    GROUP BY channel
),
total AS (
    SELECT
        (SELECT SUM(n) FROM base) AS base_total,
        (SELECT SUM(n) FROM current_period) AS curr_total
),
expected AS (
    SELECT
        b.channel,
        c.n::NUMERIC AS observed,
        b.n::NUMERIC * t.curr_total / NULLIF(t.base_total, 0) AS expected
    FROM base b
    LEFT JOIN current_period c USING (channel)
    CROSS JOIN total t
)
SELECT
    SUM(POWER(observed - expected, 2) / NULLIF(expected, 0)) AS chi_square_drift
FROM expected;

Если chi_square > критического (df = N_buckets-1) — структура сместилась.

Пороги и реакция

Стандарт по PSI:

• < 0.1 — стабильно
• 0.1 – 0.25 — заметный shift, мониторить
• > 0.25 — significant drift, переобучать модель или пересмотреть feature

SELECT
    psi,
    CASE
        WHEN psi < 0.1  THEN 'stable'
        WHEN psi < 0.25 THEN 'shift — monitor'
        ELSE 'significant drift — retrain'
    END AS verdict
FROM psi_result;

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

Ошибка 1. Бакеты на текущем периоде. PSI считается на бакетах референса. Если бакетизировать по current data — смысл теряется.

Ошибка 2. LN на нуле. Бакет с нулевыми наблюдениями ломает формулу. Сглаживание (+ε) или фильтр.

Ошибка 3. PSI для целевой переменной. PSI принято для фичей. Для target drift есть отдельные методы (concept drift, performance monitoring).

Ошибка 4. Не сравнивать с baseline static. Сравнение «вчера vs сегодня» даёт шум. Сравнивайте с фиксированным reference window (например, training set).

Ошибка 5. Игнорировать seasonal effect. Распределение в декабре отличается от июня. Базис должен быть из той же сезоны.

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

• Как посчитать chi-square test в SQL
• Как посчитать data quality score в SQL
• Как посчитать data volume anomaly в SQL
• Как посчитать anomaly detection в SQL

FAQ

PSI vs KS test?

PSI — на бакетах, для credit-scoring и ML monitoring. KS — на continuous, более точный для маленьких выборок.

Сколько бакетов?

10 — стандарт. Меньше — потеря разрешения, больше — статистически шумит.

Reference window — какой?

Месяц до запуска модели или test-set. Главное — фиксированный.

PSI больше 0.5 — что значит?

Драматический drift, обычно ошибка трекинга или резкий внешний шок.

Можно ли PSI для бинарных фичей?

Да, эквивалентно chi-square на 2 бакета.