Посчитайте медиану чека по каждому городу
Проверила команда Сопровод · Обновлено
Короткий ответ
Посчитайте медиану упорядоченным агрегатом: percentile_cont(0.5) WITHIN GROUP (ORDER BY amount) с GROUP BY city. При четном числе заказов он берет среднее двух центральных значений, percentile_disc(0.5) вернет меньшее из них, то есть реальный чек. Пустые суммы оба пропускают. avg медианой не считается: один крупный заказ сдвигает среднее, медиана почти не меняется.
Почему этот вопрос задают
Медиану спрашивают, чтобы проверить, отличаете ли вы среднее от типичного значения и знаете ли агрегаты PostgreSQL сверх sum и avg. От senior заодно ждут разговора о пустых значениях и цене сортировки.
Условие задачи
Сеть цветочных магазинов хранит заказы в таблице orders(id, city, amount). Маркетинг хочет знать типичный чек в каждом городе: среднее портят редкие корпоративные заказы. Посчитайте медиану суммы заказа по каждому городу и отсортируйте результат по названию города. В Казани четыре заказа, у одного заказа в Самаре сумма не записана.
Как подойти к решению
- Решите с заказчиком, что считать медианой при четном числе заказов: среднее двух центральных или реальный чек
- Возьмите
percentile_cont(0.5)сWITHIN GROUP (ORDER BY amount)и сгруппируйте по городу - Проверьте, как агрегат обходится с пустой суммой: строку с
NULLон пропускает - Выведите рядом
percentile_discиavg, чтобы показать разницу на данных
Решение
WITHIN GROUP (ORDER BY amount) задает порядок, в котором ищется середина, а GROUP BY делит заказы по городам. Строку без суммы агрегат пропускает, поэтому в Самаре медиана считается по трем заказам. percentile_cont возвращает double precision: для денег округлите результат или приведите его к numeric.
SELECT city,
percentile_cont(0.5) WITHIN GROUP (ORDER BY amount) AS median,
percentile_disc(0.5) WITHIN GROUP (ORDER BY amount) AS median_disc,
round(avg(amount), 2) AS avg_amount
FROM orders
GROUP BY city
ORDER BY city;city | median | median_disc | avg_amount --------+--------+-------------+------------ Казань | 1800 | 1500.00 | 2700.00 Самара | 1300 | 1300.00 | 2333.33 Тула | 1100 | 1100.00 | 1133.33 (3 rows)
Что спросят следом
Как посчитать медиану без
percentile_cont, например в базе, где его нет?Можно ли вывести медиану города рядом с каждым заказом через
OVER?Как посчитать сразу медиану и 90-й процентиль?
Доступно по подписке
Ответы на эти вопросы
Частые ошибки
«Медиана — это
avg(amount)»Среднее и медиана совпадают, например, на симметричных данных, а чеки обычно скошены вправо. В Казани один заказ на 6000 поднимает среднее до 2700, медиана остается 1800
«
percentile_discиpercentile_contдают одно и то же»Только при нечетном числе значений. При четном
percentile_contберет среднее двух центральных,percentile_discвозвращает одно из них. Уточните, нужен ли реальный чек из данных«Заменю пустую сумму нулем через
coalesce, чтобы не терять строки»Ноль сдвинет середину: в Самаре медиана станет 1100 вместо 1300. Пустая сумма значит, что данных нет, и агрегат правильно ее пропускает
«Отсортирую заказы и возьму строку с номером
count(*) / 2»При четном числе это одна из двух средних строк, при нечетном целочисленное деление дает строку левее середины. Встроенный
percentile_contизбавляет от этих ошибок
Сигналы для интервьюера
Сильный ответ
Интересуется, для чего нужна цифра, и предлагает показывать рядом с медианой число заказов, чтобы маленький город не вводил в заблуждение
Тревожный сигнал
Предлагает выгрузить все заказы и посчитать медиану в приложении, к запросу переходит только после подсказки
Красный флаг
Называет медианой среднее между самым маленьким и самым большим чеком и не видит разницы с серединой ряда
Как еще могут спросить
- Как найти медиану в PostgreSQL?
- Чем
percentile_contотличается отpercentile_disc? - Посчитайте медианную сумму заказа для каждой группы
- Почему для типичного чека медиана лучше среднего и как получить ее в SQL?











