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

В большинстве SQL-систем агрегат AVG игнорирует NULL, поэтому среднее считается только по известным значениям. Если пропуски чаще относятся к небольшим значениям, их исключение завысит среднее; NULL не считается нулём.

Механизм можно представить как сумму известных значений, делённую на количество известных наблюдений. Поэтому важно отличать COUNT(*) от COUNT(столбец): первый считает все строки, второй — только ненулевые значения.

Исторический контекст

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

Агрегатные функции получили специальную семантику, чтобы единичные пропуски обычно не делали расчёт полностью непригодным. Обратная сторона подхода — изменение знаменателя и риск незаметного смещения показателя.

Постановка проблемы

Пусть в данных есть доходы 100, 200 и NULL. AVG вернёт 150, а не 100: пропуск будет исключён, а не преобразован в ноль. Если NULL возник у клиентов с низким доходом, среднее среди наблюдаемых клиентов перестанет описывать всю исходную популяцию.

Неверное решение особенно опасно при сравнении групп. Если в одной группе доля пропусков выше, различие средних может отражать неодинаковую полноту данных, а не различие самого показателя.

Подробное решение

Для обычного среднего SQL фактически использует только строки, где значение агрегируемого столбца не равно NULL. В условной форме это соответствует отношению суммы известных значений к числу известных значений:

SELECT
  SUM(value) AS total_known,
  COUNT(value) AS known_count,
  COUNT(*) AS all_rows,
  AVG(value) AS average_known
FROM measurements;

Здесь COUNT(value) не включает NULL, тогда как COUNT(*) включает все строки. Если все значения равны NULL, AVG обычно возвращает NULL, поскольку известного знаменателя нет.

Заменять NULL на ноль можно только при содержательной уверенности, что пропуск действительно означает нулевое значение. Если пропуск означает неизвестный факт, такая замена искусственно уменьшит среднее и может создать новый вид смещения.

Для отчёта нужно отдельно контролировать долю пропусков, сравнивать её между группами и явно определить целевую метрику. Среднее среди клиентов с известным значением и среднее по всем клиентам, где отсутствие события трактуется как ноль, — разные показатели.

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

Ситуация из практики

В отчёте сравнивали среднюю сумму покупки по рекламным каналам. В канале A пропуски составляли 2%, а в канале B — 35%; при этом пропуски возникали преимущественно для небольших заказов из-за сбоя передачи данных.

Рассматривались три варианта. Исключить строки с пропусками было быстро, но это завысило среднее канала B. Заменить пропуски нулём сохранило все строки, но неверно интерпретировало техническое отсутствие суммы как отсутствие покупки. Использовать только известные суммы с обязательным отображением полноты данных сохраняло корректную семантику, но не устраняло риск смещения из-за неслучайных пропусков.

Выбрали третий вариант для оперативного отчёта и отдельно исправили источник данных. В отчёте показывали среднее, число известных заказов и долю пропусков; после устранения сбоя сравнение каналов пересчитали на согласованном наборе данных.

Что кандидаты часто упускают

Чем отличаются AVG(value), SUM(value) / COUNT(*) и SUM(value) / COUNT(value)?

AVG(value) обычно игнорирует NULL и делит сумму известных значений на их количество. SUM(value) / COUNT(*) делит ту же сумму на число всех строк, поэтому фактически трактует пропуски как нули, хотя явно этого не сообщает. SUM(value) / COUNT(value) обычно совпадает с AVG(value), если нет дополнительных различий в типах и обработке пустого результата.
Что произойдёт с результатом, если в группе все значения равны NULL?

AVG и SUM обычно вернут NULL, а COUNT(value) — ноль. Это означает, что данных для расчёта нет; подставлять ноль автоматически нельзя, поскольку ноль является конкретным значением, а не признаком отсутствия наблюдений.
Почему контроль доли NULL не доказывает отсутствие смещения?

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