Percentile в SQL: шпаргалка

Что такое перцентили

P-perсентиль — значение, ниже которого находится P% данных.

• P50 = медиана (половина выше, половина ниже)
• P25, P75 = квартили
• P95, P99 = часто используют для анализа «долгих» хвостов (время отклика)

Основные функции

Функция	Что делает
PERCENTILE_CONT(p)	Интерполированное значение
PERCENTILE_DISC(p)	Ближайшее значение из данных
NTILE(n)	Разбивает на n одинаковых групп
AVG / MIN / MAX	Для контекста

1. Медиана (PERCENTILE_CONT)

Postgres

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total) AS median
FROM orders;

MySQL 8+ / BigQuery / Snowflake

-- Snowflake / Oracle
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total) AS median
FROM orders;

-- BigQuery
SELECT PERCENTILE_CONT(total, 0.5) OVER () AS median
FROM orders;

-- MySQL 8+ (нестандартно, но работает)
SELECT total AS median
FROM (
    SELECT total, ROW_NUMBER() OVER (ORDER BY total) AS rn, COUNT(*) OVER () AS cnt
    FROM orders
) t
WHERE rn = CEIL(cnt / 2);

2. Квартили (P25, P50, P75)

SELECT
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY total) AS p25,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY total) AS p50,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY total) AS p75
FROM orders;

3. P95, P99 (для response time / longtail)

SELECT
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY response_time_ms) AS p95,
    PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY response_time_ms) AS p99,
    PERCENTILE_CONT(0.999) WITHIN GROUP (ORDER BY response_time_ms) AS p999
FROM api_logs
WHERE created_at >= NOW() - INTERVAL '1 hour';

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

SELECT
    category,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total) AS median,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY total) AS p95
FROM orders
GROUP BY category;

5. PERCENTILE_CONT vs PERCENTILE_DISC

Разница:

• PERCENTILE_CONT(0.5) — интерполирует между значениями
• PERCENTILE_DISC(0.5) — возвращает ближайшее реальное значение

-- для [10, 20, 30, 40]:
PERCENTILE_CONT(0.5)  -- 25 (интерполяция)
PERCENTILE_DISC(0.5)  -- 20 (ближайшее реальное)

Для нечётного числа — оба возвращают то же значение.

Обычно хотят PERCENTILE_CONT для среднего позиционного значения.

6. NTILE — разбить на N групп

-- квартили: 4 группы по 25%
SELECT
    user_id,
    total,
    NTILE(4) OVER (ORDER BY total) AS quartile
FROM orders;

-- децили: 10 групп по 10%
SELECT
    user_id,
    total,
    NTILE(10) OVER (ORDER BY total) AS decile
FROM orders;

Используется для сегментации: «Топ-20% самых платящих» = NTILE(5) = 1.

7. Размер каждого перцентиля

-- сколько заказов в каждом децили
SELECT
    decile,
    COUNT(*) AS orders_cnt,
    MIN(total) AS min_total,
    MAX(total) AS max_total,
    AVG(total) AS avg_total
FROM (
    SELECT total, NTILE(10) OVER (ORDER BY total) AS decile
    FROM orders
) t
GROUP BY decile
ORDER BY decile;

8. Сравнение распределений (A/B тест)

-- сравним распределение сессий в контроле и тесте
SELECT
    group_name,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY session_length) AS p25,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY session_length) AS p50,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY session_length) AS p75,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY session_length) AS p95,
    AVG(session_length) AS avg
FROM ab_test_sessions
GROUP BY group_name;

Если медиана одинакова, а P95 просел в тесте — значит изменилось поведение «heavy users».

9. Детектирование выбросов

-- IQR метод: выбросы за пределами Q1 - 1.5*IQR и Q3 + 1.5*IQR
WITH stats AS (
    SELECT
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY total) AS q1,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY total) AS q3
    FROM orders
)
SELECT o.* FROM orders o, stats s
WHERE o.total < s.q1 - 1.5 * (s.q3 - s.q1)
   OR o.total > s.q3 + 1.5 * (s.q3 - s.q1);

10. MySQL <8 альтернатива

Если в версии нет PERCENTILE_CONT:

-- медиана через подзапрос
SELECT AVG(total) AS median
FROM (
    SELECT total
    FROM orders
    ORDER BY total
    LIMIT 2 -- для чётного числа
    OFFSET (SELECT (COUNT(*) - 1) / 2 FROM orders)
) t;

Или через оконные функции (MySQL 8+):

SELECT AVG(total) FROM (
    SELECT total,
        ROW_NUMBER() OVER (ORDER BY total) AS rn,
        COUNT(*) OVER () AS cnt
    FROM orders
) t
WHERE rn IN (FLOOR((cnt+1)/2), CEIL((cnt+1)/2));

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

Ошибка 1. Использовать AVG вместо медианы

AVG чувствителен к выбросам. Медиана (P50) устойчива.

-- 10 клиентов с чеком 100 ₽ и 1 кит с 1 000 000 ₽
AVG    ≈ 100 091 (обман)
Median ≈ 100 (реально)

Ошибка 2. Забыть WITHIN GROUP

-- ошибка (в Postgres / Snowflake)
PERCENTILE_CONT(0.5) (ORDER BY total)

-- правильно
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total)

Ошибка 3. NULL в PERCENTILE

По умолчанию NULL-значения игнорируются. Если это не то, что нужно, фильтруйте:

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY COALESCE(total, 0))

Ошибка 4. Путать NTILE и PERCENTILE

• NTILE(4) → разбивает данные на 4 группы (номер группы 1-4)
• PERCENTILE_CONT(0.25) → возвращает значение P25

Производительность

• PERCENTILE_CONT — тяжёлый на больших данных (требует полной сортировки)
• NTILE — тоже сортировка
• Индекс на целевой столбец ускоряет

Для больших таблиц иногда используют approximate percentiles:

• APPROX_PERCENTILE в Snowflake
• approx_quantiles в BigQuery

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

• Медиана vs среднее
• Агрегатные функции SQL
• Оконные функции SQL

FAQ

Как посчитать медиану в SQL?

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col) в Postgres / Oracle / Snowflake. В MySQL 5 — через подзапрос.

P95 или P99?

P95 для большинства задач. P99 — для SRE / performance, где важны редкие «плохие» случаи.

Что такое IQR?

Inter-Quartile Range = P75 − P25. Используется для детектирования выбросов и описания распределения.

Можно ли считать percentile по группе?

Да: PERCENTILE_CONT(0.5) WITHIN GROUP (...) OVER (PARTITION BY group_col) или через GROUP BY.

---

Тренируйте SQL — откройте тренажёр с 1500+ вопросами для собесов.