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

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

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

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

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

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

Среднее в 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. Запомни это разделение — его любят спрашивать на собесе.

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

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 другие СУБД

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 с нуля». Перцентили в реальных запросах закрепляются быстрее, чем в теории.

Частые вопросы

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

Среднее — это сумма всех значений, делённая на их количество, поэтому оно учитывает величину каждого значения и один большой выброс тянет его вверх. Медиана делит упорядоченный ряд ровно пополам и учитывает только порядок — выброс сдвигает её максимум на одну позицию. Пример: зарплаты 80, 90, 100, 110 и 1500 тыс. дают среднее 376 тыс., которое почти никто не получает, а медиана 100 тыс. описывает типичного сотрудника.

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

Время ответа сервера почти всегда имеет правый хвост: большинство запросов быстрые, но есть редкие медленные, и среднее их «размазывает» и прячет. Например, при 950 запросах по 100 мс и 50 по 2000 мс среднее будет 195 мс и выглядит отлично, а p95 — около 2000 мс, то есть каждый 20-й пользователь ждёт две секунды. SLA и алерты строят на p95/p99, потому что они описывают опыт худших пользователей, а не «бумажное» среднее.

Как посчитать медиану и перцентили в SQL (PostgreSQL и ClickHouse)?

В PostgreSQL используют percentile_cont(0.5) WITHIN GROUP (ORDER BY column) — аргумент задаёт долю (0.5 — медиана, 0.95 — p95), функция работает как агрегат и сочетается с GROUP BY. В ClickHouse синтаксис другой: quantile(0.95)(column), есть точный вариант quantileExact и quantiles(0.5, 0.95, 0.99)(column) для нескольких перцентилей одним проходом. Важно: quantile в ClickHouse по умолчанию приближённый, а PERCENTILE_CONT в PostgreSQL — точный с интерполяцией.

В чём разница между PERCENTILE_CONT и PERCENTILE_DISC?

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

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