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

Зачем Z-score

Юзер сделал заказ на $5000. Это outlier или норма? Z-score даёт ответ: «3.5 стандартных отклонения от среднего» — outlier. «0.8 SD» — норма. Z-score стандартизует данные на разных масштабах.

Формула

Z = (x - mean) / std

Z = 0 — точно на average. Z = 2 — 2 SD выше. Z = -1.5 — 1.5 SD ниже.

Базовый расчёт

WITH stats AS (
    SELECT
        AVG(total) AS mean,
        STDDEV(total) AS std
    FROM orders
    WHERE status = 'paid'
)
SELECT
    order_id,
    total,
    (total - s.mean) / NULLIF(s.std, 0) AS z_score
FROM orders o
CROSS JOIN stats s
WHERE status = 'paid'
ORDER BY ABS((total - s.mean) / NULLIF(s.std, 0)) DESC
LIMIT 100;

Outlier detection

|Z| > 3 — обычно outlier. |Z| > 2 — подозрительно.

WITH stats AS (
    SELECT AVG(total) AS mean, STDDEV(total) AS std
    FROM orders WHERE status = 'paid'
),
flagged AS (
    SELECT
        order_id,
        total,
        (total - s.mean) / NULLIF(s.std, 0) AS z,
        CASE
            WHEN ABS((total - s.mean) / NULLIF(s.std, 0)) > 3 THEN 'outlier'
            WHEN ABS((total - s.mean) / NULLIF(s.std, 0)) > 2 THEN 'suspicious'
            ELSE 'normal'
        END AS flag
    FROM orders o
    CROSS JOIN stats s
    WHERE status = 'paid'
)
SELECT
    flag,
    COUNT(*) AS count,
    AVG(total) AS avg_total
FROM flagged
GROUP BY flag
ORDER BY flag;

По группам

Stratified z-score (относительно категории):

WITH group_stats AS (
    SELECT
        category,
        AVG(total) OVER (PARTITION BY category) AS mean_cat,
        STDDEV(total) OVER (PARTITION BY category) AS std_cat,
        total,
        order_id
    FROM orders
    WHERE status = 'paid'
)
SELECT
    order_id,
    category,
    total,
    (total - mean_cat) / NULLIF(std_cat, 0) AS z_score_within_category
FROM group_stats
WHERE ABS((total - mean_cat) / NULLIF(std_cat, 0)) > 2
ORDER BY ABS((total - mean_cat) / NULLIF(std_cat, 0)) DESC;

«$5000 в электронике — норма, в FMCG — outlier».

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

Ошибка 1. Z-score на skewed data. Z-score assumes normal distribution. Revenue — heavily skewed. Используйте log-transform или IQR.

Ошибка 2. Compute mean/std с outliers. Если в данных есть extreme outliers — они искажают mean/std. Используйте robust methods (median + MAD).

Ошибка 3. Не учитывать heteroskedasticity. Variance меняется по группам. Z-score within group лучше.

Ошибка 4. Pop std vs sample std. В SQL STDDEV по умолчанию — sample (n-1). STDDEV_POP — population. Разница маленькая для большого n.

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

• Как посчитать standard deviation в SQL
• Anomaly detection на собесе DS
• Percentile в SQL — шпаргалка
• Как посчитать confidence interval в SQL

FAQ

Z-score > 3 — outlier?

В normal data — yes (~0.27% юзеров). В skewed — часто false positive.

Z-score vs IQR?

Z — для symmetric distributions. IQR — robust для skewed.

Sample или population std?

В large sample — почти одинаково. Sample (default in SQL) — для inference.

Stratified z-score?

Z within category — учитывает heterogeneity. Полезно когда categories разные по природе.

Z-score в дашборде?

Полезно для anomaly alerts: send Slack если |z| > 3 на key metric.