Как посчитать data volume anomaly в SQL

Зачем мониторить объём

Если в production-таблицу обычно льётся 100k строк в день, а сегодня попало 20k — пайплайн упал. И наоборот: 1M строк — где-то дубли или сломанная фильтрация. Volume anomaly — самый дешёвый чек, который ловит 70% incidents в ETL.

Daily volume

SELECT
    DATE_TRUNC('day', created_at)::DATE AS day,
    COUNT(*) AS rows_inserted
FROM events
WHERE created_at >= CURRENT_DATE - INTERVAL '60 days'
GROUP BY DATE_TRUNC('day', created_at)
ORDER BY day;

Уже эта колонка ─ дашборд для глаза. Алерты строят на отклонениях.

Rolling baseline

Сравниваем сегодня с 28-дневным средним:

WITH daily AS (
    SELECT
        DATE_TRUNC('day', created_at)::DATE AS day,
        COUNT(*) AS ROWS
    FROM events
    WHERE created_at >= CURRENT_DATE - INTERVAL '60 days'
    GROUP BY DATE_TRUNC('day', created_at)
),
rolling AS (
    SELECT
        day,
        ROWS,
        AVG(ROWS) OVER w AS rolling_avg,
        STDDEV_SAMP(ROWS) OVER w AS rolling_std
    FROM daily
    WINDOW w AS (
        ORDER BY day
        ROWS BETWEEN 28 PRECEDING AND 1 PRECEDING
    )
)
SELECT *
FROM rolling
WHERE day >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY day;

28 PRECEDING AND 1 PRECEDING — окно последних 28 дней, исключая сегодняшний.

Z-score алерт

WITH daily AS (
    SELECT
        DATE_TRUNC('day', created_at)::DATE AS day,
        COUNT(*) AS ROWS
    FROM events
    WHERE created_at >= CURRENT_DATE - INTERVAL '60 days'
    GROUP BY DATE_TRUNC('day', created_at)
),
rolling AS (
    SELECT
        day,
        ROWS,
        AVG(ROWS) OVER w AS rolling_avg,
        STDDEV_SAMP(ROWS) OVER w AS rolling_std
    FROM daily
    WINDOW w AS (ORDER BY day ROWS BETWEEN 28 PRECEDING AND 1 PRECEDING)
)
SELECT
    day,
    ROWS,
    rolling_avg,
    (ROWS - rolling_avg)::NUMERIC / NULLIF(rolling_std, 0) AS z_score,
    CASE
        WHEN ABS((ROWS - rolling_avg) / NULLIF(rolling_std, 0)) > 3 THEN 'CRITICAL'
        WHEN ABS((ROWS - rolling_avg) / NULLIF(rolling_std, 0)) > 2 THEN 'WARNING'
        ELSE 'ok'
    END AS verdict
FROM rolling
WHERE day = CURRENT_DATE - INTERVAL '1 day';

|z| > 3 — событие 0.27% по нормали, обычно реальная аномалия. |z| > 2 — 5%, шумит.

Day-of-week корректировка

DAU в субботу часто ниже будней — простой z-score даст ложный алерт по выходным. Стратификация:

WITH daily AS (
    SELECT
        DATE_TRUNC('day', created_at)::DATE AS day,
        EXTRACT(DOW FROM created_at)::INT AS dow,
        COUNT(*) AS ROWS
    FROM events
    GROUP BY DATE_TRUNC('day', created_at), EXTRACT(DOW FROM created_at)
),
baseline_by_dow AS (
    SELECT
        dow,
        AVG(ROWS) AS dow_avg,
        STDDEV_SAMP(ROWS) AS dow_std
    FROM daily
    WHERE day BETWEEN CURRENT_DATE - INTERVAL '56 days' AND CURRENT_DATE - INTERVAL '1 day'
    GROUP BY dow
)
SELECT
    d.day,
    d.dow,
    d.ROWS,
    b.dow_avg,
    (d.ROWS - b.dow_avg) / NULLIF(b.dow_std, 0) AS z_dow
FROM daily d
JOIN baseline_by_dow b USING (dow)
WHERE d.day = CURRENT_DATE - INTERVAL '1 day';

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

Ошибка 1. Включать сегодня в baseline. Тогда z-score всегда близок к 0. Окно должно быть BETWEEN N PRECEDING AND 1 PRECEDING.

Ошибка 2. Без day-of-week. Алерт каждые выходные. Стратификация обязательна для DAU/event volumes.

Ошибка 3. Z-score на скошенных распределениях. DAU log-нормален. Лучше log-transform или robust методы (MAD).

Ошибка 4. Один порог для всех таблиц. Volatile источники — |z| > 4. Стабильные — |z| > 2.

Ошибка 5. Не учитывать holidays. 1 января, 8 марта — резко отличаются. Holidays-aware baseline или ручной mute.

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

• Как посчитать z-score в SQL
• Как посчитать anomaly detection в SQL
• Как посчитать data freshness в SQL
• Как посчитать data quality score в SQL

FAQ

Z-score или percentile-based?

Z — для нормально-распределённых. Percentile (например, > p99 baseline) — для скошенных.

Окно baseline?

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

Что если данные растут линейно?

Z-score станет alarmистым (вверх). Detrend (исключите trend через regression).

Алерт на просадку и на рост одинаков?

Обычно нет. Просадка = ETL сломан. Рост = дубли или новая фича. Разные runbooks.

Volume vs row count?

Row count — самый простой. Можно также сумма amount, число уникальных user_id.