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_id | amount_kopecks |
|---|---|
| 101 | 1200 |
| 102 | 1800 |
| 103 | NULL |
| 104 | 3000 |
| 105 | NULL |
Это разбор поведения 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;
| all_orders | priced_orders | known_sum | avg_known | avg_if_missing_zero |
|---|---|---|---|---|
| 5 | 3 | 6000 | 2000 | 1200 |
Проверка вручную: (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_avg | all_orders_avg_if_zero |
|---|---|
| 2000 | 1200 |
Это не две формы одной формулы: первая оценивает среднее известной суммы, вторая — среднее при допущении, что неизвестная сумма равна нулю. Для среднего чека завершённых оплат правильнее сначала выбрать завершённые и достоверно оценённые заказы, а уже потом считать 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_orders | priced_orders | avg_known | avg_zero_assumed |
|---|---|---|---|
| 2 | 0 | NULL | 0 |
При двух неизвестных суммах 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_orders | priced_orders | avg_known | display_only |
|---|---|---|---|
| 0 | 0 | NULL | 0 |
Когда нет вообще ни одной строки, агрегат без 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.