AVG и NULL: среднее только по трём известным суммам

Пять заказов, три известные суммы. AVG вернул 2 000 копеек, а при допущении, что два NULL означают ноль, получилось бы 1 200. Почему знаменатели разные?

SQL Studio / Разборы SQL / AVG и NULL: среднее только по трём известным суммам

В отчёте средний чек — 2000 копеек, хотя пять заказов в сумме имеют лишь 6000 известных копеек. Ошибка ли это? AVG(amount) игнорирует NULL, поэтому берёт три известные суммы, а не все пять заказов. Результат 2000 корректен как средняя известная сумма, но не обязательно как средний чек всех заказов. Если подставить неизвестным суммам нули, среднее станет 1200 — это уже другая бизнес-гипотеза.

Пять заказов: сначала считаем отсутствующие суммы

Для проверки не нужна схема магазина: пять заказов определены через WITH ... VALUES. Все суммы указаны в целых копейках, чтобы не смешивать проблему знаменателя с округлением денежных величин.

WITH orders(order_id, amount_kopecks) AS (
  VALUES (101, 1200), (102, 1800), (103, NULL),
         (104, 3000), (105, NULL)
)
SELECT order_id, amount_kopecks
FROM orders ORDER BY order_id;
Известные и неизвестные суммы пяти заказов
order_idamount_kopecks
1011200
1021800
103NULL
1043000
105NULL

Это разбор поведения AVG при пропусках, а не способ вычислить AOV. В отдельной статье о среднем чеке рассматриваются зерно заказов и ошибки JOIN. Здесь NULL значит «сумма пока неизвестна», а не «бесплатный заказ». Известных сумм три, всего строк пять. Возможно, два заказа ещё не завершены или платежи не загрузились: это необходимо выяснить до публикации AOV.

Внешне верный AVG может отвечать не на тот вопрос

Первый вариант вычисляет среднее среди заказов с известной суммой. Второй трактует все отсутствующие суммы как нули — это распространённое, но не всегда оправданное исправление «завышенного среднего чека».

WITH orders(order_id, amount_kopecks) AS (
  VALUES (101, 1200), (102, 1800), (103, NULL),
         (104, 3000), (105, NULL)
)
SELECT COUNT(*) AS all_orders, COUNT(amount_kopecks) AS priced_orders,
       SUM(amount_kopecks) AS known_sum,
       AVG(amount_kopecks) AS avg_known,
       AVG(COALESCE(amount_kopecks, 0)) AS avg_if_missing_zero
FROM orders;
Ровно те числа, которые возвращает AVG в SQLite
all_orderspriced_ordersknown_sumavg_knownavg_if_missing_zero
53600020001200

Проверка вручную: (1200 + 1800 + 3000) / 3 = 2000. Если бизнес явно подтвердил, что неоплаченные заказы должны входить в метрику как ноль, знаменатель становится 5 и результат равен 1200. Но при неопределённом платеже такой расчёт занижает неизвестную истинную среднюю, а не «исправляет» её.

Два знаменателя в одном запросе

Чтобы не скрывать выбор, выведем рядом два отношения. 1.0 * делает деление вещественным в SQLite, а NULLIF(...,0) явно обрабатывает отсутствие допустимого знаменателя.

WITH orders(order_id, amount_kopecks) AS (
  VALUES (101, 1200), (102, 1800), (103, NULL),
         (104, 3000), (105, NULL)
)
SELECT ROUND(1.0 * SUM(amount_kopecks) / NULLIF(COUNT(amount_kopecks), 0), 2) AS known_avg,
       ROUND(1.0 * SUM(amount_kopecks) / NULLIF(COUNT(*), 0), 2) AS all_orders_avg_if_zero
FROM orders;
Сравнение двух правил знаменателя
known_avgall_orders_avg_if_zero
20001200

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

Граница: все суммы NULL и совсем нет строк

WITH orders(order_id, amount_kopecks) AS (
  VALUES (101, NULL), (102, NULL)
)
SELECT COUNT(*) AS all_orders, COUNT(amount_kopecks) AS priced_orders,
       AVG(amount_kopecks) AS avg_known,
       AVG(COALESCE(amount_kopecks, 0)) AS avg_zero_assumed
FROM orders;
Два заказа, но ноль известных сумм
all_orderspriced_ordersavg_knownavg_zero_assumed
20NULL0

При двух неизвестных суммах AVG(amount) возвращает NULL. AVG(COALESCE(amount,0)) возвращает 0.0, будто у обоих заказов стоимость известна и равна нулю. Это не доказательство нулевых продаж.

WITH orders(order_id, amount_kopecks) AS (
  VALUES (101, 1200), (102, 1800), (103, NULL)
)
SELECT COUNT(*) AS all_orders, COUNT(amount_kopecks) AS priced_orders,
       AVG(amount_kopecks) AS avg_known,
       COALESCE(AVG(amount_kopecks), 0) AS display_only
FROM orders WHERE 1 = 0;
Агрегация по пустой выборке
all_orderspriced_ordersavg_knowndisplay_only
00NULL0

Когда нет вообще ни одной строки, агрегат без GROUP BY всё равно возвращает одну строку: COUNT(*)=0, а AVG — NULL. Подстановка нуля для показа в дашборде допустима только с подписью «нет данных», иначе читатель примет её за измеренный средний чек.

Этот материал — про знаменатель и полноту данных. Если подозреваете размножение заказов после соединения таблиц, откройте средний чек после JOIN; для определения правильной единицы измерения — COUNT по строкам и значениям; для сценариев возвратов — чистую выручку. Для самостоятельной проверки формул откройте бесплатную SQL-песочницу.

Спецификация: SQLite AVG, COUNT и SUM. В отдельном курсе MySQL 8 часть числовых преобразований может отличаться: примеры этой статьи воспроизведены именно на SQLite.

Практика с проверкой: P035 «Средний доставленный чек» — закрепите выбор знаменателя для заказов. Это смежная задача: ситуация с неизвестными суммами (NULL) разобрана в примерах выше.


Если нужна последовательная практика на MySQL 8, а не на SQLite в браузере, есть отдельный курс SQL на Stepik. Его синтаксис и поведение функций следует проверять по документации MySQL 8.