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

Зачем IQR для выбросов

IQR (Inter-Quartile Range) — устойчивая мера разброса: разница между Q3 и Q1. Не страдает от выбросов так, как стандартное отклонение. Подходит для любых распределений — особенно скошенных типа revenue, response time, basket size. Boxplot основан именно на IQR.

Формула 1.5 × IQR

IQR = Q3 − Q1
lower_bound = Q1 − 1.5 × IQR
upper_bound = Q3 + 1.5 × IQR
outlier: value < lower_bound OR value > upper_bound

Коэффициент 1.5 — стандарт Tukey. Для strict (extreme outliers) — 3.0.

IQR в SQL

WITH quartiles 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 orders
    WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
),
bounds AS (
    SELECT
        q1, q3,
        q3 - q1 AS iqr,
        q1 - 1.5 * (q3 - q1) AS lower,
        q3 + 1.5 * (q3 - q1) AS upper
    FROM quartiles
)
SELECT
    o.user_id,
    o.amount,
    CASE
        WHEN o.amount < b.lower THEN 'low_outlier'
        WHEN o.amount > b.upper THEN 'high_outlier'
        ELSE 'normal'
    END AS verdict
FROM orders o
CROSS JOIN bounds b
WHERE o.created_at >= CURRENT_DATE - INTERVAL '30 days'
  AND (o.amount < b.lower OR o.amount > b.upper)
ORDER BY o.amount DESC
LIMIT 100;

Outliers по сегментам

Свои bounds для каждой группы — иначе «выбросы Premium» становятся всеми Premium-юзерами.

WITH segment_quartiles AS (
    SELECT
        plan,
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY amount) AS q1,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY amount) AS q3
    FROM orders
    JOIN users USING (user_id)
    WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY plan
),
bounds AS (
    SELECT
        plan,
        q1 - 1.5 * (q3 - q1) AS lower,
        q3 + 1.5 * (q3 - q1) AS upper
    FROM segment_quartiles
)
SELECT
    plan,
    COUNT(*) FILTER (WHERE amount < lower OR amount > upper) AS outliers,
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE amount < lower OR amount > upper) * 100.0 / NULLIF(COUNT(*), 0) AS outlier_pct
FROM orders o
JOIN users USING (user_id)
JOIN bounds USING (plan)
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY plan;

IQR vs z-score

WITH stats AS (
    SELECT
        AVG(amount) AS mu,
        STDDEV_SAMP(amount) AS sigma,
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY amount) AS q1,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY amount) AS q3
    FROM orders
    WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
    o.amount,
    ABS((o.amount - s.mu) / NULLIF(s.sigma, 0)) > 3 AS z_outlier,
    o.amount < s.q1 - 1.5 * (s.q3 - s.q1) OR o.amount > s.q3 + 1.5 * (s.q3 - s.q1) AS iqr_outlier
FROM orders o
CROSS JOIN stats s
ORDER BY o.amount DESC
LIMIT 20;

Часто IQR пометит больше точек на скошенных данных (revenue), Z-score — на симметричных.

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

Ошибка 1. IQR на маленьких выборках. < 50 точек → квартили шумят. Используйте robust методы или собирайте больше данных.

Ошибка 2. IQR на категориях. Не имеет смысла для номинальных переменных. Только для ordinal/continuous.

Ошибка 3. Удалять low outliers как «опечатки». Возможно, это refund'ы или test orders. Расследуйте, не вычищайте.

Ошибка 4. Применять 1.5×IQR ко всем доменам. Для финансовых данных лучше 3×IQR (extreme).

Ошибка 5. IQR на multi-modal распределении. Если в данных два кластера (free / paid), IQR теряет смысл. Сегментируйте.

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

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

FAQ

IQR vs z-score?

IQR robust к выбросам, z-score — нет. Для скошенных данных — IQR.

Коэффициент 1.5 или 3?

1.5 — стандартные outliers. 3 — extreme outliers (отдельная категория).

IQR для time series?

Можно на rolling-окне, как z-score. Но обычно time-series выбросы делают через rolling z или MAD.

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

При IQR ~0.7% в normal distribution. На скошенных — больше.

IQR с NULL?

PERCENTILE_CONT игнорирует NULL. Не нужно фильтровать заранее.