0Pricing
SQL Interview Prep · Lekcja

SUM i AVG z wartościami NULL

Dlaczego AVG pomija wartości NULL i jak wpływa to na oczekiwaną odpowiedź podczas rozmowy

SUM i AVG z wartościami NULL to bezpłatna lekcja SQL Interview Prep na CoddyKit. To lekcja 2 z 4. Możesz przeczytać całą lekcję poniżej za darmo — a potem ćwiczyć ją interaktywnie w przeglądarce z wbudowanym edytorem kodu i tutorem AI dostępnym 24/7. To część ścieżki edukacyjnej SQL Interview Prep, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs SQL Interview Prep zawiera 4 lekcji w sumie.

Pułapka ukryta w AVG

Oto klasyczne pytanie rekrutacyjne, na którym tracą punkty nieuważni kandydaci: „Mają Państwo kolumnę salary zawierającą wartości NULL. Co oblicza AVG(salary) i czy odpowiada to potrzebom biznesowym?”

Szczera odpowiedź pokazuje, czy rozumieją Państwo, że agregaty pomijają wartości NULL, co zmienia mianownik średniej. Jeśli zostanie to przeoczone w środowisku produkcyjnym, raportowana średnia może zostać po cichu zawyżona.

Przyjrzyjmy się temu zachowaniu w sposób całkowicie jednoznaczny.

Przykładowe dane

W całej lekcji korzystajmy z tabeli employees zawierającej kolumnę bonus, która może przyjmować wartość NULL:

  • Alice, bonus 100
  • Bob, bonus 200
  • Carol, bonus NULL
  • Dan, bonus 300

Cztery wiersze, trzy bonusy inne niż NULL i jeden NULL. Wykonamy na tych danych funkcje SUM i AVG i sprawdzimy, jak traktowana jest wartość NULL.

SUM pomija wartości NULL

SUM(bonus) dodaje tylko wartości inne niż NULL: 100 + 200 + 300 = 600. Wiersz z wartością NULL nie wnosi nic do wyniku — jest po prostu pomijany, a nie traktowany jako zero w sensie arytmetycznym zmieniającym liczność.

Praktyczny efekt jest taki sam, jak gdyby wartość NULL była nieobecna. SUM nigdy nie zgłasza błędu z powodu wartości NULL i nie zwraca NULL, chyba że każda wartość wejściowa jest NULL.

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

AVG również pomija wartości NULL

AVG(bonus) jest tutaj kluczowe. Oblicza sumę wartości innych niż NULL podzieloną przez liczbę wartości innych niż NULL: 600 / 3 = 200.

Mianownik wynosi 3, a nie 4. Wiersz z wartością NULL jest wykluczony zarówno z licznika, jak i z dzielnika. Właśnie dlatego AVG może zaskakiwać: średnia jest liczona dla obecnych wartości, a nie dla wszystkich wierszy.

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

Dlaczego mianownik ma znaczenie

Załóżmy, że biznesowe znaczenie wartości NULL w kolumnie bonus to „nie otrzymano bonusu” = 0. Wtedy prawidłowa średnia powinna wynosić 600 / 4 = 150, ale AVG(bonus) zwraca 200.

Prawidłowa odpowiedź na rozmowie kwalifikacyjnej brzmi: „AVG pomija wartości NULL, więc oblicza średnią dla pracowników, którzy mają bonus. Jeśli NULL oznacza zero, muszę najpierw zamienić wartości NULL na 0”. Wskazanie tej różnicy pozwala zdobyć punkt.

Zamiana wartości NULL na zero za pomocą COALESCE

Aby obliczyć średnią dla wszystkich wierszy, traktując NULL jako 0, należy opakować kolumnę w COALESCE(bonus, 0). Teraz każdy wiersz ma wartość liczbową, więc mianownik wynosi 4.

Otrzymujemy 600 / 4 = 150. Wniosek: AVG(col) i AVG(COALESCE(col, 0)) odpowiadają na różne pytania biznesowe. Należy wybrać odpowiednią formę świadomie.

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

AVG = SUM / COUNT — ostrożnie

Przydatna zależność: AVG(col) jest równe SUM(col) / COUNT(col) — proszę zwrócić uwagę na COUNT(col), a nie COUNT(*), ponieważ zarówno AVG, jak i ten wariant COUNT pomijają wartości NULL.

Jeśli omyłkowo napiszą Państwo SUM(col) / COUNT(*), otrzymają średnią dla wszystkich wierszy (w tym przykładzie 150), inną niż AVG (200). Rekruterzy czasami proszą o ręczne odtworzenie AVG, aby sprawdzić, czy wybiorą Państwo właściwy wariant 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;

Pułapka dzielenia całkowitoliczbowego

Podczas ręcznego obliczania średnich może pojawić się subtelny błąd: w wielu bazach danych dzielenie dwóch liczb całkowitych jest dzieleniem całkowitoliczbowym, które obcina część dziesiętną. 7 / 2 może dać wynik 3, a nie 3,5.

Sama funkcja AVG zwykle zwraca liczbę dziesiętną, ale przy odtwarzaniu jej za pomocą SUM / COUNT dla kolumn całkowitoliczbowych można utracić precyzję. Najpierw należy pomnożyć wynik przez 1.0 albo rzutować go na typ dziesiętny.

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

Gdy wszystkie wartości są NULL

Przypadek brzegowy uwielbiany przez rekruterów: co się stanie, gdy każda wartość będzie NULL albo filtr nie dopasuje żadnych wierszy?

  • SUM zwraca NULL (a nie 0), gdy nie ma żadnych wartości innych niż NULL.
  • AVG również zwraca NULL, ponieważ dzielenie przez zero elementów jest niezdefiniowane.
  • COUNT natomiast zwraca 0.

Jeśli potrzebują Państwo domyślnej wartości liczbowej, należy opakować wynik w COALESCE(SUM(col), 0).

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

Średnie dla poszczególnych grup

Te same zasady dotyczące wartości NULL obowiązują wewnątrz GROUP BY. AVG dla każdej grupy dzieli przez liczbę wartości innych niż NULL w tej grupie. Grupa zawierająca wyłącznie bonusy NULL otrzyma dla AVG wartość NULL.

Dlatego gdy średnie dla poszczególnych działów wydają się zaskakujące, proszę najpierw podejrzewać wartości NULL zmniejszające mianowniki poszczególnych grup, a dopiero później błąd złączenia.

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

Jak sformułować odpowiedź

Dopracowana odpowiedź podczas rozmowy kwalifikacyjnej może brzmieć tak: „SUM i AVG pomijają wartości NULL. AVG dzieli przez liczbę wartości innych niż NULL, więc wartości NULL faktycznie zmniejszają mianownik. Jeśli NULL powinno być traktowane jako zero, zamieniam je na 0 za pomocą COALESCE przed agregacją; w przeciwnym razie średnia uwzględnia tylko wiersze zawierające wartość”.

To jedno zdanie pokazuje poprawność, świadomość biznesową i znajomość rozwiązania.

Szybki test

Zastosujmy tę zasadę do przykładowych danych.

Podsumowanie

Najważniejsze informacje o SUM i AVG z wartościami NULL:

  • Obie funkcje całkowicie pomijają wartości NULL.
  • AVG(col) = SUM(col) / COUNT(col) — mianownik nie uwzględnia wartości NULL.
  • Należy użyć COALESCE(col, 0), gdy NULL oznacza zero i powinno być uwzględnione.
  • Wejście zawierające wyłącznie NULL lub brak wierszy powoduje, że SUM i AVG zwracają NULL (COUNT zwraca 0).
  • Podczas ręcznego odtwarzania AVG należy uważać na dzielenie całkowitoliczbowe.

Następnie: MIN, MAX i agregowanie danych nieliczbowych.

Często zadawane pytania

Czy lekcja „SUM i AVG z wartościami NULL” jest bezpłatna?

Tak — pełny tekst „SUM i AVG z wartościami NULL” jest dostępny za darmo tutaj w sieci. Aby ćwiczyć ją interaktywnie (wbudowany edytor kodu i tutor AI dostępny 24/7) i odblokować resztę kursu SQL Interview Prep, przejdź na CoddyKit PRO. Kurs SQL Interview Prep zawiera 4 lekcji w sumie.

Co nauczysz się w „SUM i AVG z wartościami NULL”?

Dlaczego AVG pomija wartości NULL i jak wpływa to na oczekiwaną odpowiedź podczas rozmowy Ćwiczysz SQL Interview Prep z praktycznym kodem, który uruchamiasz bezpośrednio w przeglądarce, a tutor AI dostępny 24/7 odpowiada na Twoje pytania podczas pracy nad lekcją.

Czy potrzebuję doświadczenia, aby zacząć SQL Interview Prep?

Nie wymagamy żadnego doświadczenia. SQL Interview Prep w CoddyKit jest strukturyzowany dla początkujących i zaawansowanych użytkowników, więc możesz zacząć tutaj lub od początku i uczyć się w swoim tempie. To lekcja 2 z 4.

Ile czasu zajmuje lekcja „SUM i AVG z wartościami NULL”?

Większość lekcji CoddyKit trwa około 5–10 minut. Każda lekcja to mały, interaktywny krok, dzięki czemu robisz systematyczne postępy i zawsze wracasz dokładnie do tego samego miejsca — na webie i w aplikacji.

Czy mogę pisać i uruchamiać kod w tej lekcji SQL Interview Prep?

Tak. Każda lekcja SQL Interview Prep zawiera wbudowany edytor kodu, więc piszesz i uruchamiasz prawdziwy kod bezpośrednio w przeglądarce i od razu otrzymujesz sprzężenie zwrotne od AI — bez konfiguracji na komputerze.

Wszystkie lekcje w tym kursie

  1. COUNT(*) a COUNT(column) i COUNT(DISTINCT)
  2. SUM i AVG z wartościami NULL
  3. MIN, MAX i agregowanie wartości nieliczbowych
  4. Agregaty bez GROUP BY
← Powrót do SQL Interview Prep