Влияние NULL на агрегатные функции: SUM(), COUNT(), AVG(), MIN(), MAX()

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

• SUM() — суммирование данных.
• AVG() — нахождение среднего значения.
• MIN() — поиск минимального значения.
• MAX() — поиск максимального значения.
• COUNT() — подсчёт строк.

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

Поведение NULL в агрегатах: краткий обзор

Вот здесь начинается самое интересное:

• SUM() и AVG() игнорируют NULL. Если хотя бы одна запись имеет значение NULL, она просто не учтется в вычислениях. Вроде бы справедливое поведение, ведь как вырастет сумма, если кто-то "не пришёл на вечеринку"? Или как считать среднее, если одного значения не хватает?
• MIN() и MAX() также пропускают NULL. Они находят минимальное или максимальное значение только из тех данных, которые не являются NULL. Так что, когда вы ищете самого младшего сотрудника, пропустившего заполнение даты рождения, NULL не станет победителем.
• COUNT(*) учитывает все строки, даже те, где есть NULL. А вот COUNT(column) будет считать только строки, где в указанном столбце есть значение, то есть NULL игнорируется.

Давайте разберем это на примерах.

Примеры использования агрегатных функций с NULL

Вот таблица students_scores, содержащая оценки студентов за тест:

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

1. Сумма всех оценок: SUM()

SELECT SUM(score) AS total_score
FROM students_scores;

Результат:

Как видите, пропущенные NULL значения просто не участвовали в суммировании. Для Алисы (85), Чарли (92) и Елены (74) сумма составила 251. Боб и Дана остались за бортом.

2. Средняя оценка: AVG()

SELECT AVG(score) AS average_score
FROM students_scores;

Результат:

Опять же, NULL были проигнорированы, и среднее значение рассчитывалось только для тех, у кого есть оценки: (85 + 92 + 74) / 3 = 83.67.

3. Минимальная и максимальная оценка: MIN() и MAX()

SELECT
    MIN(score) AS min_score, 
    MAX(score) AS max_score 
FROM students_scores;

Результат:

Здесь тоже всё просто: NULL значения снова проигнорированы, и минимальной стала оценка 74, а максимальной — 92.

4. Подсчёт строк: COUNT(*) vs COUNT(column)

SELECT
    COUNT(*) AS total_rows, 
    COUNT(score) AS non_null_scores 
FROM students_scores;

Результат:

• COUNT(*) учёл все строки, включая те, где score равен NULL.
• COUNT(score) посчитал только те строки, у которых в столбце score есть значение.

Практические кейсы

Приведём несколько практических примеров.

Пример 1: Подсчёт сотрудников с указанной и неуказанной зарплатой

Допустим, у нас есть таблица employees с зарплатами.

Мы хотим узнать, сколько сотрудников указали свою зарплату, а сколько — нет.

SELECT
    COUNT(*) AS total_employees,
    COUNT(salary) AS employees_with_salary,
    COUNT(*) - COUNT(salary) AS employees_without_salary
FROM employees;

Здесь:

• COUNT(*) вернёт общее количество сотрудников.
• COUNT(salary) посчитает, сколько сотрудников указали зарплату.
• Для вычисления числа сотрудников без зарплаты, мы просто вычитаем одно значение из другого.

Результат

Пример 2: Расчёт средней цены товаров с учётом отсутствующих данных

Вы владелец волшебного магазина, и в таблице products есть колонка price, но у некоторых товаров цена пока не указана.

Вам нужно узнать среднюю цену только для тех товаров, где она задана.

SELECT AVG(price) AS average_price
FROM products;

Результат:

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

Пример 3: Нахождение минимального и максимального возраста студентов

В таблице students хранится возраст учащихся, но для некоторых из них возраст неизвестен (NULL).

Мы хотим узнать, самого младшего и старшего студента.

SELECT
    MIN(age) AS youngest_student,
    MAX(age) AS eldest_student
FROM students;

Результат:

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

Особенности и подводные камни

Когда работаете с NULL в агрегатах, важно помнить про следующие моменты:

• В сумме SUM() и среднем AVG() NULL не учитываются. Это можно использовать, чтобы не добавлять "пустые" значения в вычисления.
• Если вам нужно учитывать строки с NULL в столбце, можно использовать COUNT(*).
• При использовании MIN() или MAX(), NULL не влияет на результат. Однако, если весь столбец заполнен только NULL, результатом будет тоже NULL.

Советы для работы с NULL

1. Учитывайте специфику задачи. Важно понимать, нужно ли учитывать NULL в вашем запросе. Иногда, как в случае с AVG(), их игнорирование — это то, что нужно. А иногда, как при подсчёте общего количества, важно учесть и строки с NULL.
2. Используйте COALESCE() при необходимости. Если нужно заменить NULL значением по умолчанию в расчётах, функция COALESCE() станет вашим другом (но это уже тема следующей лекции).
3. Не путайте COUNT(*) и COUNT(column). Это классическая ошибка новичков. Первый считает строки, второй — только строки с ненулевыми значениями.

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

2

Задача

SQL SELF, 9 уровень, 2 лекция

Недоступна

Сумма и среднее значение оценок

Сумма и среднее значение оценок

Открыть

2

Задача

SQL SELF, 9 уровень, 2 лекция

Недоступна

Подсчёт строк с заполненными и незаполненными данными

Подсчёт строк с заполненными и незаполненными данными

Открыть