Медиана и процентили в SQL: PERCENTILE_CONT, PERCENTILE_DISC, NTILE

Среднее врёт. Один богатый клиент поднимает средний чек в 10 раз — медиана остаётся честной. Разберём как считать медиану и процентили в PostgreSQL.

Проблема среднего

-- Данные: заказы на 100, 150, 200, 120, 130, 50000 рублей
SELECT AVG(amount) FROM orders;
-- Результат: ~8783 руб. — бессмысленное число из-за одного выброса

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median FROM orders;
-- Результат: 140 руб. — типичный заказ

PERCENTILE_CONT: непрерывная интерполяция

Возвращает интерполированное значение — может не совпадать с реальными данными:

SELECT
    PERCENTILE_CONT(0.5)  WITHIN GROUP (ORDER BY amount) AS median,      -- 50-й процентиль
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY amount) AS q1,           -- 25-й (нижний квартиль)
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY amount) AS q3,           -- 75-й (верхний квартиль)
    PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY amount) AS p90,          -- 90-й процентиль
    PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY amount) AS p99           -- 99-й процентиль
FROM orders
WHERE status = 'completed';

Если медиана лежит между двумя значениями (130 и 150) — PERCENTILE_CONT вернёт 140 (среднее).

PERCENTILE_DISC: дискретный (реальное значение)

Возвращает ближайшее реальное значение из набора данных:

SELECT
    PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY amount) AS median_disc,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median_cont
FROM orders;
-- DISC: 130 (реальное значение из таблицы)
-- CONT: 140 (интерполировано между 130 и 150)

Когда что использовать:

• PERCENTILE_CONT — для числовых измерений (время, деньги, вес)
• PERCENTILE_DISC — когда нужно реальное значение из данных (например, медианный пользователь)

Несколько процентилей за один запрос

-- Передать массив процентилей
SELECT
    UNNEST(PERCENTILE_CONT(ARRAY[0.1, 0.25, 0.5, 0.75, 0.9, 0.99])
           WITHIN GROUP (ORDER BY amount)) AS value,
    UNNEST(ARRAY[10, 25, 50, 75, 90, 99]) AS percentile
FROM orders;

Процентили по группам

SELECT
    category,
    COUNT(*) AS orders,
    ROUND(AVG(amount)) AS mean,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median,
    PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY amount) AS p90,
    MAX(amount) AS max_order
FROM orders
GROUP BY category
ORDER BY median DESC;

NTILE: разбить на N групп

NTILE(n) разбивает строки на n приблизительно равных групп:

SELECT
    user_id,
    total_spent,
    NTILE(4) OVER (ORDER BY total_spent) AS quartile
FROM user_totals;
-- quartile = 1: нижние 25%, quartile = 4: верхние 25%

-- Процентиль каждого пользователя в общем рейтинге
SELECT
    user_id,
    total_spent,
    NTILE(100) OVER (ORDER BY total_spent) AS percentile_rank
FROM user_totals;
-- percentile_rank = 95 → пользователь в топ 5%

NTILE vs PERCENTILE:

• NTILE — оконная функция, присваивает группу каждой строке
• PERCENTILE_CONT — агрегатная функция, возвращает пороговое значение

PERCENT_RANK и CUME_DIST

SELECT
    user_id,
    total_spent,
    PERCENT_RANK() OVER (ORDER BY total_spent) AS percent_rank,
    -- Доля строк МЕНЬШЕ текущей (0.0 - 1.0)

    CUME_DIST() OVER (ORDER BY total_spent) AS cume_dist
    -- Доля строк <= текущей (0.0 - 1.0)
FROM user_totals;

-- Пользователи в нижнем квартиле по активности
SELECT user_id, sessions
FROM (
    SELECT user_id, sessions,
           CUME_DIST() OVER (ORDER BY sessions) AS cd
    FROM monthly_activity
) t
WHERE cd <= 0.25;

MODE: самое частое значение

SELECT MODE() WITHIN GROUP (ORDER BY city) AS most_common_city
FROM users;

SELECT MODE() WITHIN GROUP (ORDER BY amount) AS most_common_amount
FROM orders;

Interquartile Range (IQR) и поиск выбросов

WITH percentiles AS (
    SELECT
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY amount) AS q1,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY amount) AS q3
    FROM orders
),
iqr AS (
    SELECT q1, q3, q3 - q1 AS iqr FROM percentiles
)
SELECT o.*
FROM orders o
CROSS JOIN iqr
WHERE o.amount < iqr.q1 - 1.5 * iqr.iqr
   OR o.amount > iqr.q3 + 1.5 * iqr.iqr;
-- Строки за пределами [Q1 - 1.5*IQR, Q3 + 1.5*IQR] — статистические выбросы

Сравнение среднего и медианы

Если mean >> median — у вас выбросы (обычно это хороший знак в продажах):

SELECT
    ROUND(AVG(amount), 2)                                            AS mean,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount), 2)   AS median,
    ROUND(AVG(amount) / PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount), 2) AS skew_ratio
FROM orders;
-- skew_ratio > 2: сильная правосторонняя асимметрия (выбросы тянут вверх)

Практический кейс: SLA мониторинг

-- Процентиль времени ответа API за последние 24 часа
SELECT
    endpoint,
    COUNT(*) AS requests,
    ROUND(AVG(response_ms)) AS avg_ms,
    PERCENTILE_CONT(0.5)  WITHIN GROUP (ORDER BY response_ms) AS p50,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY response_ms) AS p95,
    PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY response_ms) AS p99,
    MAX(response_ms) AS max_ms
FROM api_logs
WHERE created_at > NOW() - INTERVAL '24 hours'
GROUP BY endpoint
ORDER BY p99 DESC;
-- p99 > 1000ms → SLA нарушен

Итог

Медиана честнее среднего там, где есть выбросы. p95/p99 — стандарт для мониторинга производительности.