Как посчитать MAD outliers в SQL

Зачем MAD

MAD (Median Absolute Deviation) — robust альтернатива стандартному отклонению. Не страдает от выбросов: 50% точек могут быть «плохими», MAD всё равно покажет правильный спред. Идеальный детектор для рядов с frequent outliers — ошибки трекинга, manual data entry, события brand spikes.

Формула

MAD = median(|x_i − median(x)|)
modified_z_i = 0.6745 × (x_i − median(x)) / MAD

0.6745 — constant Φ⁻¹(0.75), приводящая MAD к эквиваленту σ для normal distribution. С этим коэффициентом modified z-score сравним с обычным z (порог 3 — стандарт).

MAD в SQL

В Postgres PERCENTILE_CONT(0.5) даёт median. MAD считается двумя проходами:

WITH median_calc AS (
    SELECT
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) AS med
    FROM api_latency
    WHERE event_time >= CURRENT_DATE - INTERVAL '7 days'
),
abs_deviations AS (
    SELECT
        a.event_time,
        a.value,
        ABS(a.value - m.med) AS abs_dev,
        m.med
    FROM api_latency a
    CROSS JOIN median_calc m
    WHERE a.event_time >= CURRENT_DATE - INTERVAL '7 days'
),
mad_calc AS (
    SELECT
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY abs_dev) AS mad,
        MAX(med) AS med
    FROM abs_deviations
)
SELECT
    a.event_time,
    a.value,
    m.med,
    m.mad,
    0.6745 * (a.value - m.med) / NULLIF(m.mad, 0) AS modified_z
FROM abs_deviations a
CROSS JOIN mad_calc m
WHERE ABS(0.6745 * (a.value - m.med) / NULLIF(m.mad, 0)) > 3
ORDER BY ABS(a.value - m.med) DESC
LIMIT 50;

|modified_z| > 3 — стандартный порог, эквивалентный |z| > 3 для нормального распределения.

Modified z-score

Удобно вынести в CASE:

WITH ... -- median и mad, как выше
labelled AS (
    SELECT
        event_time,
        value,
        0.6745 * (value - med) / NULLIF(mad, 0) AS modified_z
    FROM abs_deviations
    CROSS JOIN mad_calc
)
SELECT
    event_time,
    value,
    modified_z,
    CASE
        WHEN ABS(modified_z) > 3.5 THEN 'outlier'
        WHEN ABS(modified_z) > 2.5 THEN 'borderline'
        ELSE 'normal'
    END AS verdict
FROM labelled;

Rolling MAD

Для time-series — rolling median и rolling MAD:

WITH rolling AS (
    SELECT
        event_date,
        value,
        -- rolling median: для каждой точки берём окно
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) OVER w AS rolling_med
    FROM daily_metric
    WINDOW w AS (
        ORDER BY event_date
        ROWS BETWEEN 28 PRECEDING AND 1 PRECEDING
    )
)
SELECT
    event_date,
    value,
    rolling_med,
    ABS(value - rolling_med) AS abs_dev
FROM rolling;

Postgres 14+ позволяет PERCENTILE_CONT(...) OVER w. В более старых — материализация двумя проходами.

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

Ошибка 1. MAD = 0. Если >50% точек одинаковые — MAD = 0, деление ломается. Возьмите среднюю MAD или σ как backup.

Ошибка 2. Забыть 0.6745. Без поправки modified-z не сравнима с обычным z-score. Пороги другие.

Ошибка 3. MAD на multi-modal. Mode «free» и «paid» — median окажется между ними. MAD будет огромен, мало outliers.

Ошибка 4. Применять MAD к категориальным. MAD только для ordinal/continuous.

Ошибка 5. Сравнение с z на normal distribution. На нормали MAD = 0.6745 × σ — поправка нужна. На skewed данных MAD «свой».

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

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

FAQ

MAD vs IQR?

Оба robust. MAD строже (использует median, не Q1/Q3). IQR проще объяснять.

Modified z или просто MAD?

Modified z — для сравнения с z-score эталоном. Просто MAD — как value сам по себе.

Если >50% дубли?

MAD = 0. Используйте IQR или z-score с робастной σ.

Какой порог?

|modified_z| > 3.5 — стандарт у Iglewicz & Hoaglin.

MAD для time-series?

Rolling median + rolling MAD — самый robust способ для streaming-данных.