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

AVG(значение) эквивалентен сумме ненулевых значений, делённой на количество ненулевых значений, то есть на COUNT(значение). COUNT(*) считает все строки, включая строки с NULL, поэтому деление на него занижает среднее, если NULL означает отсутствие измерения, а не нулевое значение.

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

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

Такое поведение позволяет отличать «значение неизвестно» от настоящего нуля. Иначе незаполненная зарплата автоматически уменьшала бы среднюю зарплату, хотя фактическая зарплата сотрудника неизвестна.

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

Пусть в группе три строки: 100, NULL и 300. Сумма известных значений равна 400. Если делить её на количество строк, получится 133,33, хотя среднее по двум известным зарплатам равно 200.

Главный риск — выбрать знаменатель не по смыслу показателя. COUNT(*) отвечает на вопрос «сколько строк в группе», а COUNT(значение) — «для скольких строк значение известно».

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

В обычном агрегатном контексте AVG(значение) игнорирует NULL и концептуально вычисляется как SUM(значение) / COUNT(значение). Поэтому при наличии пропусков эти выражения дают одинаковый результат:

WITH salaries(amount) AS (
    VALUES (100.0), (NULL), (300.0)
)
SELECT
    AVG(amount) AS average_value,
    SUM(amount) / COUNT(amount) AS explicit_average,
    SUM(amount) / COUNT(*) AS wrong_if_null_means_missing
FROM salaries;

В этом примере первые два результата равны 200, а третье выражение использует в знаменателе все три строки и поэтому даёт 133,33. Десятичный литерал в примере нужен, чтобы избежать целочисленного деления в СУБД, где результат деления целых чисел усекается.

Если все значения в группе равны NULL, COUNT(значение) равен нулю, а AVG(значение) возвращает NULL, а не ноль. Это важный сигнал: данных для расчёта среднего нет. Подмена результата на ноль через COALESCE допустима только тогда, когда бизнес-смысл действительно требует показывать отсутствие наблюдений как нулевой показатель.

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

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

В отчёте по филиалам средняя сумма сделки в одном филиале была подозрительно низкой. В таблице было 100 сделок, но сумма заполнена только для 80. Запрос делил сумму известных сделок на COUNT(*), фактически трактуя 20 пропусков как сделки с нулевой суммой.

Рассматривались два варианта. Можно было заменить пропуски нулём и сохранить знаменатель COUNT(*), но это было бы корректно только при подтверждённом смысле «пропуск равен нулю». Второй вариант — использовать AVG(amount) или делить на COUNT(amount), сохраняя пропуски вне расчёта.

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

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

1. Что вернёт AVG, если в группе нет ни одного ненулевого значения?

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

Если интерфейс обязан показывать число, можно применить COALESCE(AVG(amount), 0), но такую замену лучше делать на уровне представления или отчёта. В аналитическом слое исходный NULL обычно полезнее, поскольку сохраняет информацию об отсутствии данных.

2. Почему предварительная замена NULL на ноль меняет смысл среднего?

Выражение с заменой NULL на ноль включает ранее неизвестные значения в совокупность и увеличивает знаменатель. Например, для 100, NULL и 300 среднее по известным значениям равно 200, а после замены NULL на 0 среднее по трём строкам равно 133,33.

Такой приём корректен для показателя, где пропуск действительно означает отсутствие величины: например, отсутствие начисления. Для незагруженной или неизвестной суммы он создаёт систематическое занижение.

3. Чем отличается AVG(DISTINCT значение) от обычного AVG(значение)?

AVG(DISTINCT значение) сначала исключает повторяющиеся значения, а затем считает среднее по оставшимся уникальным значениям. Это не среднее по строкам: если зарплата 100 встречается у ста сотрудников, она всё равно участвует только один раз.

Поэтому такой агрегат подходит для вопроса «каково среднее среди различных тарифов», но обычно не подходит для средней зарплаты сотрудников или средней суммы сделок. NULL при этом также не становится числом и не учитывается.