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

Зачем percentile cutoff

Percentile-based детектор не требует допущений о распределении: «всё, что выше p99 — outlier». Самый простой и продуктово-понятный способ помечать выбросы. Особенно хорош для:

• revenue distributions (log-нормальные)
• latency (long tail)
• web traffic (bot-всплески)

В отличие от z-score и MAD, percentile cutoff даёт фиксированную долю «выбросов» — удобно для дашбордов.

Top/bottom percentile

WITH thresholds AS (
    SELECT
        PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY amount) AS p99,
        PERCENTILE_CONT(0.01) WITHIN GROUP (ORDER BY amount) AS p01
    FROM orders
    WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
    o.user_id,
    o.amount,
    CASE
        WHEN o.amount >= t.p99 THEN 'high_outlier'
        WHEN o.amount <= t.p01 THEN 'low_outlier'
        ELSE 'normal'
    END AS verdict
FROM orders o
CROSS JOIN thresholds t
WHERE o.created_at >= CURRENT_DATE - INTERVAL '30 days'
  AND (o.amount >= t.p99 OR o.amount <= t.p01);

Outliers с двух сторон

Гибкий threshold — 0.005 / 0.995, отсекаем по 0.5% с каждой стороны:

WITH thresholds AS (
    SELECT
        PERCENTILE_CONT(0.005) WITHIN GROUP (ORDER BY value) AS low_cutoff,
        PERCENTILE_CONT(0.995) WITHIN GROUP (ORDER BY value) AS high_cutoff
    FROM session_duration
    WHERE event_date >= CURRENT_DATE - INTERVAL '7 days'
)
SELECT
    s.user_id,
    s.duration,
    CASE
        WHEN s.duration < t.low_cutoff  THEN 'too short'
        WHEN s.duration > t.high_cutoff THEN 'too long'
        ELSE 'normal'
    END AS verdict
FROM session_duration s
CROSS JOIN thresholds t
WHERE s.event_date >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY s.duration DESC;

По сегментам

Сегменты разные → разные p99. Для каждой группы — свой:

WITH thresholds AS (
    SELECT
        device_type,
        PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY page_load_ms) AS p99
    FROM page_load_events
    WHERE event_date >= CURRENT_DATE - INTERVAL '7 days'
    GROUP BY device_type
)
SELECT
    e.event_date,
    e.device_type,
    e.page_load_ms,
    t.p99 AS device_p99
FROM page_load_events e
JOIN thresholds t USING (device_type)
WHERE e.page_load_ms > t.p99
  AND e.event_date >= CURRENT_DATE - INTERVAL '7 days';

iPhone p99 — 800ms, low-end Android — 3500ms. Без сегментации все low-end Android попадут в «outliers».

Winsorization

Не выкидывайте outliers, замените на пороговое значение:

WITH thresholds AS (
    SELECT
        PERCENTILE_CONT(0.01) WITHIN GROUP (ORDER BY revenue) AS p01,
        PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY revenue) AS p99
    FROM users_revenue
    WHERE created_at >= CURRENT_DATE - INTERVAL '90 days'
)
SELECT
    u.user_id,
    u.revenue,
    LEAST(GREATEST(u.revenue, t.p01), t.p99) AS revenue_winsorized
FROM users_revenue u
CROSS JOIN thresholds t;

Winsorization сохраняет ranking, но обрезает экстремумы для среднего и стандартного отклонения.

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

Ошибка 1. Считать «p99 — outlier» по дефолту. 1% точек всегда выше p99 — это просто верхний хвост. Outlier — это аномалия, не каждое значение выше cutoff.

Ошибка 2. Один cutoff для всех периодов. P99 за last 30 days отличается от p99 last 7 days. Калибруйте на свежем окне.

Ошибка 3. Winsorization без объявления. Если winsorization меняет данные, метрики после неё нужно отдельно помечать как «adjusted».

Ошибка 4. Не сегментировать. Глобальный p99 latency перекручен low-end devices.

Ошибка 5. Не учитывать выбор окна. 30 минут на сайте может быть «too long» для landing, но «short» для editor. Контекст важен.

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

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

FAQ

Какой percentile cutoff?

p99 / p01 — стандарт. Для строгой фильтрации — p99.5 / p0.5.

Percentile vs z-score?

Percentile — без допущений. Z-score — требует normal distribution.

Winsorization vs trimming?

Trimming удаляет точки. Winsorization заменяет — сохраняет N.

Cutoff меняется во времени?

Да, при росте/сезоне. Rolling p99 решает.

Сколько выбросов в дата-сете?

При percentile cutoff — точно столько, сколько процентов отрезаете. p99 → 1%.