0Pricing
Coding Interview Prep · Урок

SUM и AVG с NULL

Узнайте, почему AVG игнорирует NULL и как это меняет ожидаемый на собеседовании ответ.

«SUM и AVG с NULL» — бесплатный урок Coding Interview Prep на CoddyKit. Это урок 2 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения Coding Interview Prep, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс Coding Interview Prep содержит 4 уроков всего.

Ловушка, скрытая в AVG

Вот классический вопрос на собеседовании, на котором ошибаются невнимательные кандидаты: «У Вас есть столбец зарплаты с несколькими значениями NULL. Что вычисляет AVG(salary) и соответствует ли это потребностям бизнеса?»

Честный ответ показывает, понимаете ли Вы, что агрегатные функции игнорируют NULL, из-за чего меняется знаменатель среднего значения. Допустите такую ошибку в рабочей системе — и сообщаемое среднее значение незаметно окажется завышенным.

Давайте сделаем это поведение совершенно очевидным.

Пример данных

На протяжении всего урока используйте таблицу employees с допускающим NULL столбцом bonus:

  • Alice, премия 100
  • Bob, премия 200
  • Carol, премия NULL
  • Dan, премия 300

Четыре строки, три значения премии, отличные от NULL, и одно значение NULL. Мы применим к этим данным SUM и AVG и посмотрим, как обрабатывается NULL.

SUM игнорирует NULL

SUM(bonus) складывает только значения, отличные от NULL: 100 + 200 + 300 = 600. Строка со значением NULL ничего не добавляет; она просто пропускается, а не считается нулём, меняющим количество учитываемых значений.

Практически это равносильно тому, как если бы NULL отсутствовал. SUM никогда не выдаёт ошибку из-за NULL и возвращает NULL только в том случае, если все входные значения равны NULL.

SELECT SUM(bonus) AS total_bonus
FROM employees;
-- returns 600

AVG тоже игнорирует NULL

AVG(bonus) — ключевой случай. Он вычисляет сумму значений, отличных от NULL, делённую на количество значений, отличных от NULL: 600 / 3 = 200.

Знаменатель равен 3, а не 4. Строка со значением NULL исключается и из числителя, и из делителя. Именно поэтому AVG может удивлять: среднее вычисляется по имеющимся значениям, а не по всем строкам.

SELECT AVG(bonus) AS avg_bonus
FROM employees;
-- 600 / 3 = 200, NOT 600 / 4 = 150

Почему важен знаменатель

Предположим, что с точки зрения бизнеса NULL в премии означает «не получил премию» = 0. Тогда правильное среднее должно быть равно 600 / 4 = 150, но AVG(bonus) сообщает 200.

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

Принудительная замена NULL на ноль с помощью COALESCE

Чтобы вычислить среднее по всем строкам, считая NULL нулём, оберните столбец в COALESCE(bonus, 0). Теперь каждая строка содержит числовое значение, поэтому знаменатель равен 4.

В результате получается 600 / 4 = 150. Вывод: AVG(col) и AVG(COALESCE(col, 0)) отвечают на разные бизнес-вопросы. Выбирайте осознанно.

SELECT AVG(COALESCE(bonus, 0)) AS avg_over_all
FROM employees;
-- 600 / 4 = 150

AVG = SUM / COUNT: соблюдайте осторожность

Полезное тождество: AVG(col) равно SUM(col) / COUNT(col) — обратите внимание на COUNT(col), а не на COUNT(*), поскольку и AVG, и этот COUNT пропускают NULL.

Если по ошибке написать SUM(col) / COUNT(*), получится среднее по всем строкам (здесь 150), отличающееся от AVG (200). Интервьюеры иногда просят вручную восстановить AVG, чтобы проверить, выберете ли Вы правильный COUNT.

SELECT
  AVG(bonus)                       AS builtin_avg,   -- 200
  SUM(bonus) * 1.0 / COUNT(bonus)  AS manual_avg,    -- 200
  SUM(bonus) * 1.0 / COUNT(*)      AS over_all_rows  -- 150
FROM employees;

Ловушка целочисленного деления

Тонкая ошибка при вычислении средних вручную: во многих базах данных деление двух целых чисел выполняется как целочисленное деление, при этом десятичная часть отбрасывается. 7 / 2 может дать 3, а не 3.5.

AVG обычно возвращает десятичное число, но если восстановить его с помощью SUM / COUNT для целочисленных столбцов, можно потерять точность. Сначала умножьте на 1.0 или преобразуйте значение к десятичному типу.

SELECT
  SUM(bonus) / COUNT(bonus)        AS maybe_truncated,
  SUM(bonus) * 1.0 / COUNT(bonus)  AS precise
FROM employees;

Когда все значения равны NULL

Пограничный случай, который любят задавать интервьюеры: что если каждое значение равно NULL или фильтр не находит строк?

  • SUM возвращает NULL (а не 0), если нет входных значений, отличных от NULL.
  • AVG также возвращает NULL, поскольку деление на нулевое количество значений не определено.
  • COUNT, напротив, возвращает 0.

Если Вам нужно числовое значение по умолчанию, оберните результат в COALESCE(SUM(col), 0).

SELECT COALESCE(SUM(bonus), 0) AS safe_total
FROM employees
WHERE 1 = 0;  -- no rows: returns 0, not NULL

Средние значения по группам

Те же правила для NULL применяются внутри GROUP BY. В каждой группе AVG делит на количество значений, отличных от NULL, в этой группе. Для группы, состоящей только из значений NULL, результатом будет AVG = NULL.

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

SELECT department, AVG(bonus) AS avg_bonus
FROM employees
GROUP BY department;

Как сформулировать ответ

Отточенный ответ на собеседовании может звучать так: «SUM и AVG обе игнорируют NULL. AVG делит на количество значений, отличных от NULL, поэтому NULL фактически уменьшает знаменатель. Если NULL должен считаться нулём, перед агрегацией я преобразую его с помощью COALESCE; в противном случае среднее отражает только строки, содержащие значение».

Эта одна фраза демонстрирует правильность, понимание потребностей бизнеса и способ исправления.

Быстрая проверка

Примените правило к примеру данных.

Итоги

Главные выводы о SUM и AVG при наличии NULL:

  • Обе функции полностью игнорируют NULL.
  • AVG(col) = SUM(col) / COUNT(col) — знаменатель не включает NULL.
  • Используйте COALESCE(col, 0), когда NULL означает ноль и должен учитываться.
  • Входные данные, состоящие только из NULL, или отсутствие строк приводят к тому, что SUM и AVG возвращают NULL (COUNT возвращает 0).
  • Остерегайтесь целочисленного деления при ручном восстановлении AVG.

Далее: MIN, MAX и агрегирование нечисловых данных.

Часто задаваемые вопросы

Урок «SUM и AVG с NULL» бесплатный?

Да — полный текст урока «SUM и AVG с NULL» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс Coding Interview Prep, подпишись на CoddyKit PRO. Курс Coding Interview Prep содержит 4 уроков всего.

Чему я научусь в уроке «SUM и AVG с NULL»?

Узнайте, почему AVG игнорирует NULL и как это меняет ожидаемый на собеседовании ответ. Ты практикуешь Coding Interview Prep с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать Coding Interview Prep?

Предыдущий опыт не требуется. Coding Interview Prep на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 2 из 4.

Сколько времени занимает урок «SUM и AVG с NULL»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке Coding Interview Prep?

Да. Каждый урок Coding Interview Prep включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

  1. COUNT(*) и COUNT(столбец) и COUNT(DISTINCT)
  2. SUM и AVG с NULL
  3. MIN, MAX и агрегирование нечисловых данных
  4. Агрегаты без GROUP BY
← Назад к Coding Interview Prep