Вплив 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 ще ефективніше.