Wpływ NULL na funkcje agregujące: SUM(), COUNT(), AVG(), MIN(), MAX()

Pamiętamy, że funkcje agregujące to takie, które działają od razu na wielu wierszach danych i zwracają jeden wynik. W PostgreSQL bardzo często będziesz używać następujących funkcji agregujących:

• SUM() — sumowanie danych.
• AVG() — obliczanie średniej wartości.
• MIN() — szukanie minimalnej wartości.
• MAX() — szukanie maksymalnej wartości.
• COUNT() — liczenie wierszy.

Na pierwszy rzut oka wszystko jest proste: przekazujesz do funkcji kolumnę lub wyrażenie i dostajesz wynik. Ale co się dzieje, jeśli w kolumnie pojawi się NULL?

Zachowanie NULL w agregatach: szybki przegląd

I tu zaczyna się robić ciekawie:

• SUM() i AVG() ignorują NULL. Jeśli choć jeden rekord ma wartość NULL, po prostu nie będzie brany pod uwagę w obliczeniach. W sumie to logiczne — jak suma ma wzrosnąć, jeśli ktoś „nie przyszedł na imprezę”? Albo jak liczyć średnią, jeśli brakuje jednej wartości?
• MIN() i MAX() też pomijają NULL. Szukają minimum lub maksimum tylko wśród tych danych, które nie są NULL. Więc jeśli szukasz najmłodszego pracownika, który nie podał daty urodzenia, NULL nie wygra.
• COUNT(*) liczy wszystkie wiersze, nawet te, gdzie jest NULL. Ale COUNT(column) policzy tylko te wiersze, gdzie w danej kolumnie jest jakaś wartość, czyli NULL jest ignorowany.

Rozłóżmy to na przykładach.

Przykłady użycia funkcji agregujących z NULL

Oto tabela students_scores, która zawiera oceny studentów za test:

Teraz zadamy kilka zapytań i przeanalizujemy wyniki:

1. Suma wszystkich ocen: SUM()

SELECT SUM(score) AS total_score
FROM students_scores;

Wynik:

Jak widzisz, brakujące wartości NULL po prostu nie zostały uwzględnione w sumowaniu. Dla Alicji (85), Czarka (92) i Eleny (74) suma to 251. Bob i Dana zostali pominięci.

2. Średnia ocena: AVG()

SELECT AVG(score) AS average_score
FROM students_scores;

Wynik:

Znowu, NULL zostały zignorowane i średnia liczona była tylko dla tych, którzy mają ocenę: (85 + 92 + 74) / 3 = 83.67.

3. Minimalna i maksymalna ocena: MIN() i MAX()

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

Wynik:

Tu też wszystko jasne: NULL znowu zostały pominięte, a minimalna ocena to 74, maksymalna — 92.

4. Liczba wierszy: COUNT(*) vs COUNT(column)

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

Wynik:

• COUNT(*) policzył wszystkie wiersze, także te, gdzie score to NULL.
• COUNT(score) policzył tylko te wiersze, gdzie w kolumnie score jest jakaś wartość.

Praktyczne przypadki

Podajmy kilka praktycznych przykładów.

Przykład 1: Liczenie pracowników z podaną i niepodaną pensją

Załóżmy, że mamy tabelę employees z pensjami.

Chcemy wiedzieć, ilu pracowników podało swoją pensję, a ilu nie.

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

Tutaj:

• COUNT(*) zwróci całkowitą liczbę pracowników.
• COUNT(salary) policzy, ilu pracowników podało pensję.
• Aby policzyć pracowników bez pensji, po prostu odejmujemy jedno od drugiego.

Wynik

Przykład 2: Liczenie średniej ceny produktów z uwzględnieniem brakujących danych

Jesteś właścicielem magicznego sklepu i w tabeli products jest kolumna price, ale dla niektórych produktów cena nie jest jeszcze podana.

Chcesz poznać średnią cenę tylko dla tych produktów, gdzie jest ona podana.

SELECT AVG(price) AS average_price
FROM products;

Wynik:

Jeśli chcesz ustawić domyślną cenę dla produktów bez ceny (np. 0), możesz użyć funkcji COALESCE() z następnego wykładu.

Przykład 3: Znalezienie minimalnego i maksymalnego wieku studentów

W tabeli students przechowywany jest wiek uczniów, ale dla niektórych z nich wiek jest nieznany (NULL).

Chcemy poznać najmłodszego i najstarszego studenta.

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

Wynik:

To zapytanie zwróci minimalny i maksymalny wiek tylko dla tych studentów, których wiek jest podany. NULL znowu zostanie pominięty.

Specyfika i pułapki

Gdy pracujesz z NULL w agregatach, warto pamiętać o kilku rzeczach:

• W sumie SUM() i średniej AVG() NULL nie są brane pod uwagę. Możesz to wykorzystać, żeby nie dodawać „pustych” wartości do obliczeń.
• Jeśli chcesz uwzględnić wiersze z NULL w kolumnie, użyj COUNT(*).
• Przy użyciu MIN() lub MAX(), NULL nie wpływa na wynik. Ale jeśli cała kolumna to tylko NULL, wynikiem też będzie NULL.

Wskazówki do pracy z NULL

1. Zwracaj uwagę na specyfikę zadania. Ważne, czy musisz uwzględniać NULL w swoim zapytaniu. Czasem, jak przy AVG(), ich ignorowanie jest OK. A czasem, jak przy liczeniu wszystkich rekordów, ważne jest, by uwzględnić też wiersze z NULL.
2. Używaj COALESCE() w razie potrzeby. Jeśli musisz zastąpić NULL wartością domyślną w obliczeniach, funkcja COALESCE() będzie twoim kumplem (ale to już temat na kolejny wykład).
3. Nie myl COUNT(*) i COUNT(column). To klasyczny błąd początkujących. Pierwszy liczy wiersze, drugi — tylko te z niepustymi wartościami.

Teraz już wiesz, jak sprytnie milczący NULL może wpłynąć na agregaty. Ta wiedza pozwoli ci uniknąć niemiłych niespodzianek i wykorzystać NULL na swoją korzyść. W następnym wykładzie poznamy potężne narzędzie COALESCE(), żeby radzić sobie z NULL jeszcze skuteczniej.