Медиана чека в SQLite без percentile: полный расчёт

У небольшого магазина четыре заказа: 100, 200, 300 и 900 рублей. Средний чек равен 375, но типичный заказ в этой четвёрке заметно дешевле. Если менеджер хочет понять середину распределения, полезна медиана: половина заказов не выше неё, половина не ниже. Здесь она равна 250 — среднему двух центральных значений 200 и 300.

SQLite позволяет вычислить медиану без специальной агрегатной функции. Нужно отсортировать заказы внутри каждого магазина, дать им номера и узнать размер группы. Для нечётного числа заказов берётся одна центральная строка, для чётного — две. Затем обычный AVG объединяет одно или два значения.

Где находятся центральные строки

Для группы размера n их номера равны целочисленным выражениям (n + 1) / 2 и (n + 2) / 2. При n=4 получаем 2 и 3; при n=3 оба выражения дают 2. Оператор IN не создаёт дубль центральной строки, поэтому для нечётного набора среднее считается по одному значению.

CREATE TABLE orders (shop_id INTEGER, order_id INTEGER, amount INTEGER NOT NULL);
INSERT INTO orders VALUES
 (1,1,100),(1,2,200),(1,3,900),(1,4,300),
 (2,5,10),(2,6,30),(2,7,20);
WITH ranked AS (
  SELECT shop_id, amount,
         ROW_NUMBER() OVER (PARTITION BY shop_id ORDER BY amount, order_id) AS rn,
         COUNT(*) OVER (PARTITION BY shop_id) AS n
  FROM orders
)
SELECT shop_id, AVG(amount * 1.0) AS median_amount
FROM ranked
WHERE rn IN ((n + 1) / 2, (n + 2) / 2)
GROUP BY shop_id
ORDER BY shop_id;

Результат: магазин 1 — 250.0, магазин 2 — 20.0. Во втором магазине суммы 10, 20, 30: центральная строка одна. ORDER BY amount, order_id делает нумерацию стабильной при равных суммах. Равенство самих сумм медиане не мешает, но уникальный второй ключ помогает воспроизводить промежуточные номера при отладке.

Важен вход запроса. Здесь одна строка равна одному оплаченному заказу. Если вместо orders подставить позиции заказа, один крупный заказ с пятью товарами попадёт в распределение пять раз. Медиана станет медианой строк позиций, а не чеков. Сначала агрегируйте позиции до order_id, определите правила отмен и возвратов, затем ранжируйте итоговые суммы.

Сумма в примере объявлена NOT NULL, поэтому каждый заказ участвует в сортировке. Если рабочая таблица допускает неизвестную сумму, исключайте такие заказы до нумерации и отдельно показывайте их количество. Подставлять вместо неизвестной суммы ноль нельзя: это добавит искусственные маленькие чеки и сместит медиану.

Медиана не заменяет среднее

Медиана хорошо показывает центральный заказ при редких крупных покупках. Средний чек остаётся нужен для связи с выручкой: общая сумма заказов делится на их количество. Если умножить медиану на число заказов, реальную выручку не восстановить. В отчёте эти два показателя отвечают на разные вопросы, поэтому полезно показать их рядом с количеством заказов и несколькими крупными значениями.

Пустая группа не появится в этом запросе. Если отчёт обязан показывать магазины без заказов, начните с таблицы магазинов и присоедините вычисленные медианы через LEFT JOIN; значение останется NULL. Подставлять ноль можно лишь если бизнес действительно трактует отсутствие заказов как чек нулевой стоимости.

Число 250.0 в первом результате появляется потому, что две центральные суммы усредняются. В денежных системах с копейками заранее решите, как представлять половину копейки, если сумма центральных значений нечётна. Для описательного показателя можно оставить точное значение с дополнительным знаком, а для выплаты или цены потребуется согласованное правило округления. Эти задачи нельзя смешивать одним неявным приведением типа.

Если в отчёте есть фильтр по каналу продаж или месяцу, примените его до нумерации. Медиана всех заказов магазина и медиана заказов только из рекламного канала — разные распределения. При этом удалять из набора крупный заказ лишь потому, что он «портит среднее», не нужно: медиана как раз позволяет показать центр при таком хвосте, не скрывая саму крупную покупку.

Проверка на реальных данных проста: для магазина с небольшим числом заказов выгрузите суммы по возрастанию и вручную найдите середину. Для чётного количества проверьте обе центральные строки; для нечётного — одну. Затем сравните результат с SQL. Такой тест обнаруживает ошибку в формулах номеров быстрее, чем большой агрегированный отчёт.

На курсе «Продвинутый SQL» оконные функции и предварительная агрегация используются в расчётах для интернет-магазина. Вы пишете запросы на SQLite и проверяете их на разных данных, в том числе при равных значениях и пустых выборках. Посмотрите программу, чтобы перенести приём с маленькой таблицы на целую аналитическую витрину.

Если показатель нужен для оценки эффекта эксперимента, отдельно прочитайте почему средний чек в A/B-тесте считают на уровне пользователя.