Как посчитать медиану и перцентили в SQL и почему среднее часто вводит в заблуждение?
- Чем PERCENTILE_CONT отличается от PERCENTILE_DISC?
В PostgreSQL: PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) — медиана с интерполяцией между значениями, PERCENTILE_DISC — ближайшее реальное значение. В ClickHouse — quantile, в BigQuery — APPROX_QUANTILES. Среднее чувствительно к выбросам: один заказ на миллион поднимает средний чек, а медиана описывает «типичного» клиента.
- Для времени ответа, чеков, доходов смотрят медиану и p90/p95/p99
- Перцентили нельзя усреднять между группами — считать по исходным данным
- На больших данных — приблизительные алгоритмы (t-digest)
А ещё Среднее по-прежнему нужно для сумм: выручка = среднее × количество
Подробно
Когда среднее обманывает: распределения с длинным хвостом — доходы, суммы заказов, время ответа. Средний чек 3 000 рублей может означать, что большинство тратит 800, а несколько оптовиков — сотни тысяч.
CONT против DISC: для чётного числа значений CONT вернёт среднее двух центральных (значения может не быть в данных), DISC — одно из реальных значений. Для денег и дискретных величин иногда важно именно реальное значение.
Агрегирование перцентилей: медиана медиан по регионам не равна медиане по всей стране. В витринах хранят либо сырые данные, либо мерджируемые структуры (t-digest, гистограммы).
Пример
SELECT
category,
AVG(amount) AS mean_check,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median_check,
PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY amount) AS p95_check
FROM orders
GROUP BY category;Что хотят услышать
- Функции для медианы и перцентилей.
- Почему среднее вводит в заблуждение.
- Неаддитивность перцентилей.
Проверьте себя
Один верный вариант. Подумайте, потом откройте ответ.
Показать варианты и ответ
- ✗Перцентили групп можно усреднить с весами по размеру групп — получится общий перцентиль по всем данным
- ✓PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x); для чеков и задержек — медиана и p90–p99
- ✗Среднее лучше медианы описывает типичного клиента при выбросах
- ✗AVG(x) и медиана всегда совпадают
Связанные вопросы
Разбор собеседования с sotto
После собеседования sotto разбирает разговор: оценка, каждый вопрос — хорошо, частично или слабо, чего не хватило в ответе и над чем поработать к следующему разу. Первые 30 минут бесплатно.