Как посчитать z-score outliers в SQL

Зачем z-score для выбросов

Z-score — самый простой способ пометить значения, которые сильно отклонились от среднего. Подходит для нормально-распределённых рядов: latency, response time, daily counts. Для скошенных (revenue, sessions) — лучше IQR или MAD.

Пороги по эмпирике:

• |z| > 2 — 5% точек (шумный детектор)
• |z| > 3 — 0.27% (стандарт)
• |z| > 4 — 0.006% (только серьёзные выбросы)

Формула

z_i = (x_i − μ) / σ

μ — среднее, σ — стандартное отклонение. Outlier — точка, где |z| > threshold.

Z-score в SQL

WITH stats AS (
    SELECT
        AVG(value) AS mu,
        STDDEV_SAMP(value) AS sigma
    FROM api_latency
    WHERE event_time >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
    a.event_time,
    a.value,
    (a.value - s.mu) / NULLIF(s.sigma, 0) AS z_score,
    CASE
        WHEN ABS((a.value - s.mu) / NULLIF(s.sigma, 0)) > 3 THEN 'outlier'
        WHEN ABS((a.value - s.mu) / NULLIF(s.sigma, 0)) > 2 THEN 'borderline'
        ELSE 'normal'
    END AS verdict
FROM api_latency a
CROSS JOIN stats s
WHERE a.event_time >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY ABS(a.value - s.mu) DESC
LIMIT 50;

По сегментам

Z-score, посчитанный на всей выборке, скрывает локальные выбросы. Сегментация даёт чувствительность:

WITH stats AS (
    SELECT
        endpoint,
        AVG(latency_ms) AS mu,
        STDDEV_SAMP(latency_ms) AS sigma
    FROM api_latency
    WHERE event_time >= CURRENT_DATE - INTERVAL '7 days'
    GROUP BY endpoint
)
SELECT
    a.event_time,
    a.endpoint,
    a.latency_ms,
    (a.latency_ms - s.mu) / NULLIF(s.sigma, 0) AS z_score
FROM api_latency a
JOIN stats s USING (endpoint)
WHERE ABS((a.latency_ms - s.mu) / NULLIF(s.sigma, 0)) > 3
ORDER BY ABS(a.latency_ms - s.mu) DESC;

Для каждого endpoint свой baseline.

Rolling z-score

Для time-series: «отклонение vs последние 28 дней»:

WITH rolling AS (
    SELECT
        event_date,
        value,
        AVG(value) OVER w AS rolling_mu,
        STDDEV_SAMP(value) OVER w AS rolling_sigma
    FROM daily_metric
    WINDOW w AS (
        ORDER BY event_date
        ROWS BETWEEN 28 PRECEDING AND 1 PRECEDING
    )
)
SELECT
    event_date,
    value,
    rolling_mu,
    (value - rolling_mu) / NULLIF(rolling_sigma, 0) AS rolling_z
FROM rolling
WHERE event_date >= CURRENT_DATE - INTERVAL '14 days'
ORDER BY event_date;

Adaptive: tracks shifting baseline.

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

Ошибка 1. Z-score на скошенных данных. Revenue, sessions log-нормальны. Outliers «вверх» завышают среднее → z для middle-точек растёт. Лучше log-transform или MAD.

Ошибка 2. Глобальный μ, σ для time-series с трендом. Если данные растут, новые точки всё чаще оказываются «выбросами». Rolling z адаптируется.

Ошибка 3. σ = 0. Все значения одинаковые → деление на 0. NULLIF(sigma, 0).

Ошибка 4. Удалять, не разобравшись. Outlier ≠ ошибка. Может быть real event (Black Friday, viral пост). Помечайте, не удаляйте автоматически.

Ошибка 5. Один порог для всех метрик. Latency — |z| > 3. Кликов — |z| > 4 (более шумные). Калибруйте.

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

• Как посчитать IQR outliers в SQL
• Как посчитать MAD outliers в SQL
• Как посчитать z-score в SQL
• Как посчитать anomaly detection в SQL

FAQ

Z-score для всего ряда или rolling?

Rolling — для time-series с трендом. Глобальный — для статичных распределений.

Какой threshold выбрать?

|z| > 3 — стандарт. Для шумных данных — 4.

Что если данные не нормальны?

Z-score даёт false positives на скошенных. MAD более robust.

Сколько выбросов норма?

В нормальном ряду 0.27% при |z| > 3. Больше — данные не нормальные.

Outlier vs anomaly?

Outlier — статистическое отклонение точки. Anomaly — semantic: «что-то неправильное». Outlier — техника детекции anomaly.