Как посчитать медиану суммы заказа по каждому региону с помощью оконной или агрегатной функции percentile?

Короткий ответ

В PostgreSQL можно использовать PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) как агрегатную функцию с GROUP BY region; в BigQuery — аналог PERCENTILE_CONT(amount, 0.5) OVER (PARTITION BY region) как оконную функцию, в ClickHouse — quantile(0.5)(amount).

Как это работает подробнее

Как это работает: медиана — это 50-й перцентиль, значение, относительно которого ровно половина наблюдений меньше и половина больше (с интерполяцией для PERCENTILE_CONT при чётном количестве наблюдений). В отличие от среднего, медиана устойчива к выбросам — один аномально большой заказ не сдвинет медиану так, как сдвинет среднее.

Пример кода

PostgreSQL: медиана по регионамКопировать

SELECT region,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount,
       AVG(amount) AS avg_amount
FROM orders
GROUP BY region;

Что использовать на практике

• На практике: медиану стоит приводить рядом со средним, когда в данных есть выбросы (крупные корпоративные заказы на фоне розничных) — иначе среднее может создать ложное впечатление о «типичном» заказе. Разные СУБД по-разному называют и реализуют функции перцентиля, и важно проверить, какая именно интерполяция используется (PERCENTILE_CONT интерполирует, PERCENTILE_DISC берёт реальное значение из данных).
• Проверка: Проверка: для небольшого региона с нечётным числом заказов вручную отсортировать суммы и взять среднее значение — оно должно совпасть с median_amount.

Подводные камни

• Частые ошибки: путают медиану со средним или считают, что они должны быть близки — при сильно скошенном распределении (например, доходы, суммы заказов) они могут заметно отличаться, и это нормально, а не признак ошибки в расчёте.