Медиана и перцентили vs среднее: когда что в SQL

Коротко: среднее (avg) хорошо описывает данные только тогда, когда распределение симметрично и без выбросов. Как только в данных есть длинный хвост — латентность ответа сервера, доход на пользователя, время доставки — среднее завышается несколькими большими значениями и перестаёт отражать «типичного» пользователя. В таких случаях берут медиану (p50) и перцентили (p90, p95, p99). В PostgreSQL их считают через PERCENTILE_CONT, в ClickHouse — через quantile. Это один из самых частых вопросов на собеседовании аналитика: «почему по latency смотрят p95, а не среднее».

Чем медиана отличается от среднего

Среднее (mean, avg) — это сумма всех значений, делённая на их количество. Медиана — это значение, которое делит упорядоченный ряд ровно пополам: половина наблюдений меньше неё, половина больше.

Среднее учитывает величину каждого значения. Один огромный выброс тянет его вверх.
Медиана учитывает только порядок. Выброс сдвигает её максимум на одну позицию — она почти неподвижна.

Простой пример. Зарплаты в команде из 5 человек: 80, 90, 100, 110 и 1500 тыс. ₽ (последний — основатель).

Метрика	Значение	Что говорит
Среднее	(80+90+100+110+1500)/5 = 376	«Средняя зарплата 376 тыс.» — но столько не получает почти никто
Медиана	100	Типичный сотрудник получает около 100 тыс.

Среднее в 376 — формально верное, но вводящее в заблуждение число. Медиана 100 описывает реальность.

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

Перцентиль (квантиль) p — это значение, ниже которого лежит p процентов наблюдений. Медиана — это просто 50-й перцентиль (p50).

p50 (медиана) — половина значений меньше.
p90 — 90% значений меньше, только 10% больше.
p95 — 95% меньше, «хвост» из 5% худших — выше.
p99 — отсекает 1% самых экстремальных значений.

Перцентили читают так: «p95 latency = 800 мс» означает, что 95% запросов отрабатывают быстрее 800 мс, а 5% — медленнее. Это honестнее, чем среднее: оно может быть 150 мс, пока 5% пользователей ждут по 3 секунды и уходят.

Почему по latency смотрят p95 и p99, а не среднее

Время ответа сервера почти всегда имеет правый хвост: большинство запросов быстрые, но есть редкие медленные (холодный кеш, блокировка БД, GC-пауза). Среднее эти медленные запросы «размазывает» и прячет.

Допустим, 1000 запросов: 950 по 100 мс и 50 по 2000 мс.

Среднее = (950×100 + 50×2000)/1000 = (95000 + 100000)/1000 = 195 мс. Выглядит отлично.
p95 = около 2000 мс. Каждый 20-й пользователь ждёт две секунды.

SLA и алерты строят на p95/p99 именно потому, что они описывают опыт худших пользователей, а не «бумажное» среднее. Если у тебя 1 млн запросов в день, p99 — это 10 000 человек, которые столкнулись с тормозами. Их нельзя игнорировать.

Правило для собеседования: для метрик с хвостом (latency, доход, время) — медиана и высокие перцентили; среднее оставляем для симметричных метрик без выбросов.

Как считать медиану и перцентили в PostgreSQL

В PostgreSQL есть две функции упорядоченного агрегата: PERCENTILE_CONT и PERCENTILE_DISC. Обе вызываются через синтаксис WITHIN GROUP (ORDER BY ...).

SELECT
  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,
  avg(response_ms)                                          AS mean
FROM requests
WHERE created_at >= now() - interval '1 day';

Здесь:

Аргумент 0.5 — это доля (0.5 = 50-й перцентиль). Можно передать массив: percentile_cont(ARRAY[0.5, 0.95, 0.99]).
WITHIN GROUP (ORDER BY ...) задаёт колонку, по которой считаем квантиль.

PERCENTILE_CONT vs PERCENTILE_DISC

PERCENTILE_CONT (continuous) — интерполирует между двумя соседними значениями. Для p50 из чисел 10 и 20 вернёт 15. Подходит для непрерывных метрик (время, деньги).
PERCENTILE_DISC (discrete) — возвращает реальное значение из набора, без интерполяции. Для p50 из 10 и 20 вернёт 10. Подходит, когда нужно именно существующее значение (например, ID или категория).

В 90% задач аналитика берут PERCENTILE_CONT. Запомни это разделение — его любят спрашивать на собесе.

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

PERCENTILE_CONT — это агрегат, поэтому он отлично работает с GROUP BY:

SELECT
  endpoint,
  percentile_cont(0.95) WITHIN GROUP (ORDER BY response_ms) AS p95_ms,
  count(*) AS requests
FROM requests
GROUP BY endpoint
ORDER BY p95_ms DESC;

Так находят самые медленные эндпоинты по p95, а не по обманчивому среднему.

Как считать перцентили в ClickHouse

В ClickHouse синтаксис другой — функции семейства quantile. Аргумент — доля, колонка передаётся как параметр функции.

SELECT
  quantile(0.5)(response_ms)  AS p50,
  quantile(0.95)(response_ms) AS p95,
  quantile(0.99)(response_ms) AS p99,
  avg(response_ms)            AS mean
FROM requests
WHERE created_at >= now() - INTERVAL 1 DAY;

Ключевые отличия и нюансы ClickHouse:

quantile(level)(column) по умолчанию использует приближённый алгоритм (reservoir sampling) — быстро, но результат может чуть плавать между запусками на больших данных.
quantileExact(level)(column) — точное значение, но дороже по памяти и времени. Берут, когда нужна детерминированность.
quantiles(0.5, 0.95, 0.99)(column) — считает несколько перцентилей за один проход, возвращает массив. Эффективнее, чем три отдельных вызова.
median(column) — синоним quantile(0.5).

-- несколько перцентилей одним проходом
SELECT
  endpoint,
  quantiles(0.5, 0.95, 0.99)(response_ms) AS p_array
FROM requests
GROUP BY endpoint;

Важно: ClickHouse quantile по умолчанию приближённый, а PostgreSQL PERCENTILE_CONT — точный с интерполяцией. На собесе про ClickHouse уточни, нужна ли точность — тогда quantileExact.

Сравнение синтаксиса: PostgreSQL vs ClickHouse vs другие СУБД

СУБД	Медиана / перцентиль	Тип
PostgreSQL	`percentile_cont(0.95) WITHIN GROUP (ORDER BY x)`	Точный, интерполяция
PostgreSQL	`percentile_disc(0.95) WITHIN GROUP (ORDER BY x)`	Точный, без интерполяции
ClickHouse	`quantile(0.95)(x)`	Приближённый
ClickHouse	`quantileExact(0.95)(x)`	Точный
MySQL 8+	оконкой `ROW_NUMBER` + ручной расчёт или `PERCENT_RANK()`	нет нативной функции
BigQuery	`APPROX_QUANTILES(x, 100)[OFFSET(95)]`	Приближённый
Snowflake	`percentile_cont(0.95) WITHIN GROUP (ORDER BY x)`	Точный

MySQL до сих пор не имеет встроенной функции перцентиля — там считают через оконные функции, например с NTILE или PERCENT_RANK(). Это тоже частый вопрос: «как посчитать медиану в SQL без percentile-функции».

Как посчитать медиану без percentile-функции (через оконные функции)

Если СУБД не поддерживает PERCENTILE_CONT, медиану берут через ранжирование. Идея: пронумеровать строки по возрастанию и взять центральную (или среднее двух центральных при чётном количестве).

WITH ranked AS (
  SELECT
    response_ms,
    ROW_NUMBER() OVER (ORDER BY response_ms) AS rn,
    COUNT(*)    OVER ()                      AS total
  FROM requests
)
SELECT AVG(response_ms) AS median
FROM ranked
WHERE rn IN (FLOOR((total + 1) / 2.0), CEIL((total + 1) / 2.0));

Этот приём — ROW_NUMBER() OVER (ORDER BY ...) плюс выбор центральной позиции — стоит уметь писать с нуля. Разобраться с оконными функциями глубже можно в SQL-тренажёре, там десятки задач на ROW_NUMBER, RANK и квантили с автопроверкой.

Когда брать среднее, а когда медиану: чек-лист

Используй среднее (avg), когда:

Распределение примерно симметрично, без длинного хвоста.
Нужна аддитивность: сумма = среднее × количество (выручка, бюджет).
Выбросов нет или они уже отфильтрованы.

Используй медиану / перцентили, когда:

Есть выбросы или длинный правый хвост (latency, доход, время доставки, размер заказа).
Важен «типичный» пользователь, а не среднеарифметический.
Строишь SLA или мониторинг (p95, p99 по времени ответа).
Сравниваешь распределения, устойчивые к шуму.

Дополнительный сигнал: если среднее заметно больше медианы — распределение скошено вправо, выбросы есть, и среднее обманывает. Если они близки — распределение симметрично, можно брать любое.

Частые ошибки с перцентилями

Считают среднее по тяжёлохвостой метрике. Среднее latency или среднее ARPU почти всегда вводит в заблуждение — нужны p50/p95.
Путают PERCENTILE_CONT и PERCENTILE_DISC. Для непрерывных данных — CONT (с интерполяцией), для дискретных существующих значений — DISC.
Усредняют перцентили. Нельзя взять «среднее p95 по дням» — перцентили не аддитивны. Нужно пересчитывать p95 по всему пулу данных за период.
Забывают про приближённость в ClickHouse. Дефолтный quantile плавает; для точных отчётов берут quantileExact.
Сравнивают p95 на выборках разного размера без учёта, что на маленькой выборке высокий перцентиль нестабилен.

Где потренироваться

Перцентили, медиана и выбор метрики против выбросов — обязательная тема собеседования аналитика и дата-инженера. Разбери её на практике:

SQL-тренажёр — задачи на PERCENTILE_CONT, оконные функции и расчёт квантилей с автопроверкой на PostgreSQL и SQLite.
Бесплатный курс «SQL с нуля» — 10 уроков, считаем агрегаты, медиану и перцентили запросами.
Справочник метрик — формулы и SQL по продуктовым и инженерным метрикам.
Вопросы с собеседований — реальные вопросы про среднее vs медиану и latency.
AI мок-собеседование — потренируй ответ «почему по latency смотрят p95» с разбором.

Потренируйся бесплатно: 5 SQL-задач без регистрации и курс «SQL с нуля». Перцентили в реальных запросах закрепляются быстрее, чем в теории.