Как посчитать Anomaly Detection в SQL

Зачем Anomaly Detection

DAU вчера 50К. Сегодня 80К. Это рост или sensoring баг? Anomaly detection answer: «expected 48-52K, actual 80K → anomaly». Триггер для alerting и investigation.

Методы

Z-score метод

WITH stats AS (
    SELECT
        AVG(value) AS mean,
        STDDEV(value) AS std
    FROM metrics
    WHERE DATE >= CURRENT_DATE - INTERVAL '90 days'
      AND DATE <  CURRENT_DATE - INTERVAL '1 day'  -- baseline без сегодня
)
SELECT
    m.DATE,
    m.value,
    (m.value - s.mean) / NULLIF(s.std, 0) AS z_score,
    CASE
        WHEN ABS((m.value - s.mean) / NULLIF(s.std, 0)) > 3 THEN 'anomaly'
        WHEN ABS((m.value - s.mean) / NULLIF(s.std, 0)) > 2 THEN 'suspicious'
        ELSE 'normal'
    END AS status
FROM metrics m
CROSS JOIN stats s
WHERE m.DATE >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY m.DATE DESC;

IQR метод

WITH percentiles AS (
    SELECT
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY value) AS q1,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY value) AS q3
    FROM metrics
    WHERE DATE >= CURRENT_DATE - INTERVAL '90 days'
)
SELECT
    m.DATE,
    m.value,
    p.q1,
    p.q3,
    p.q3 - p.q1 AS iqr,
    p.q1 - 1.5 * (p.q3 - p.q1) AS lower_bound,
    p.q3 + 1.5 * (p.q3 - p.q1) AS upper_bound,
    CASE
        WHEN m.value < p.q1 - 1.5 * (p.q3 - p.q1) OR m.value > p.q3 + 1.5 * (p.q3 - p.q1)
        THEN 'anomaly'
        ELSE 'normal'
    END AS status
FROM metrics m
CROSS JOIN percentiles p
WHERE m.DATE >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY m.DATE DESC;

Rolling window

Учитывает trend:

WITH rolling AS (
    SELECT
        DATE,
        value,
        AVG(value) OVER (
            ORDER BY DATE
            ROWS BETWEEN 14 PRECEDING AND 1 PRECEDING
        ) AS rolling_mean,
        STDDEV(value) OVER (
            ORDER BY DATE
            ROWS BETWEEN 14 PRECEDING AND 1 PRECEDING
        ) AS rolling_std
    FROM metrics
)
SELECT
    DATE,
    value,
    rolling_mean,
    rolling_std,
    (value - rolling_mean) / NULLIF(rolling_std, 0) AS rolling_z,
    CASE
        WHEN ABS((value - rolling_mean) / NULLIF(rolling_std, 0)) > 3
        THEN 'anomaly'
        ELSE 'normal'
    END AS status
FROM rolling
WHERE DATE >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY DATE DESC;

Rolling — учитывает recent trend, не «застревает» на старом baseline.

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

Ошибка 1. Baseline includes anomaly. Если baseline period содержит anomalies — mean/std искажены. Очистите baseline.

Ошибка 2. Игнорировать сезонность. DAU выходного дня всегда ниже weekday. Сравнивайте same-day-of-week.

Ошибка 3. Z-threshold 3 — не закон. В шумных данных 3 даёт ложные алерты. Adjust по false positive tolerance.

Ошибка 4. Single-метрика monitoring. Anomalies часто multivariate. ML-методы лучше для complex patterns.

Ошибка 5. Алертить без context. «Anomaly!» без декомпозиции бесполезно. Включайте: что за метрика, где конкретно, на сколько отклонилось.

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

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

FAQ

Z-score или IQR?

Z — для normal. IQR — для skewed. В практике начинают с IQR (robust).

Threshold — какой?

Z > 3 — стандарт. В шумных — Z > 4. Adjust по false-positive rate.

Real-time или batch?

Batch — daily. Real-time — для critical metrics (revenue, fraud).

Anomaly detection в production?

Часто переходят на dedicated tools: Prometheus + alertmanager, Datadog, custom ML. SQL хорош для exploration.

Что делать после detection?

1. Validate (баг или real?). 2) Notify owner. 3) Investigate root cause. 4) Document.