Как написать SQL-запрос для расчёта среднего и медианы метрики по группам?

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

Среднее по группам считается через AVG(...) ... GROUP BY; для медианы в PostgreSQL используется percentile_cont(0.5) WITHIN GROUP (ORDER BY ...), а точный синтаксис медианы отличается между диалектами SQL.

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

AVG — стандартная агрегатная функция, доступная практически во всех СУБД одинаково. Медиана не входит в стандартный набор агрегатных функций большинства СУБД и требует функций для расчёта процентилей или оконных конструкций. Такие запросы часто становятся основой для автоматизированных регулярных отчётов и дашбордов.

Пример кода

Пример на SQLКопировать

-- PostgreSQL
SELECT
  category,
  AVG(price) AS avg_price,
  percentile_cont(0.5) WITHIN GROUP (ORDER BY price) AS median_price
FROM products
GROUP BY category;

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

• На практике: диалект имеет значение: в PostgreSQL используют percentile_cont/percentile_disc, в ClickHouse — median()/quantile(0.5)(), в BigQuery — PERCENTILE_CONT как оконную функцию, в MySQL до версии 8 нет встроенной функции медианы и её часто эмулируют через переменные или оконные функции ROW_NUMBER/PERCENT_RANK. Перед использованием медианы в проде стоит явно проверить синтаксис под конкретную СУБД. Для воспроизводимости полезно сохранять использованный SQL-запрос вместе с отчётом, чтобы коллеги могли проверить расчёт по тем же фильтрам и группировкам.
• Проверка: для категории с одним явным выбросом (очень дорогой товар) avg_price и median_price должны заметно разойтись — это подтверждает, что расчёт медианы работает иначе, чем среднего.

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

• Частые ошибки: писать запрос с функцией медианы одного диалекта и ожидать, что он заработает в другом без изменений; забывать GROUP BY все не агрегируемые колонки, что приводит к ошибке в строгих СУБД (PostgreSQL) или к непредсказуемому результату в менее строгих; не учитывать NULL-значения, которые агрегатные функции обычно игнорируют молча. Ещё одна ошибка — забывать фильтровать неактуальные или тестовые записи (например, заказы с внутренними тестовыми аккаунтами) перед агрегированием по группам.