NULLIF w SQL: dzielenie przez zero i znaczenie brakującego wyniku

Masz liczbę rozwiązanych zadań i liczbę wszystkich zadań. Chcesz policzyć udział. Dla trzech z ośmiu sprawa wygląda prosto, dopóki w wyniku nie pojawi się zero. Potem trafiasz na wiersz bez mianownika. Wstawienie wartości domyślnej może sprawić, że raport zacznie wyglądać porządnie, ale nadal odpowiadać na niewłaściwe pytanie.

Ja rozdzielam tutaj trzy decyzje: kiedy wolno obliczyć wskaźnik, jakiego typu potrzebuje dzielenie i co pokazać przy braku wyniku. NULLIF pomaga w jednej z nich. Przy okazji można nią oczyścić umowny marker braku, lecz trzeba dokładnie wiedzieć, co porównujemy.

Wszystkie bloki SQL i tabele poniżej sprawdziłem w SQLite 3.53.1 przez node:sqlite w Node 24.18.0. Uruchamiaj je kolejno w jednym pustym połączeniu testowym. Różnice innych silników opisuję na podstawie dokumentacji; nie deklaruję wykonania tych przykładów w PostgreSQL, SQL Server ani MySQL.

Dwa argumenty i jeden wynik

NULLIF(a, b) zwraca NULL, jeśli porównanie a = b jest prawdziwe. W przeciwnym razie zwraca pierwszy argument. Wybór kolejności ma znaczenie: przy różnych liczbach wynik pochodzi z lewej strony. Regułę opisuje dokumentacja SQLite.

-- example: basics
SELECT NULLIF(7, 7) AS equal_values,
       NULLIF(7, 2) AS different_values,
       NULLIF(7, NULL) AS missing_second,
       NULLIF(NULL, 7) AS missing_first,
       NULLIF(NULL, NULL) AS both_missing;

Druga kolumna zawiera siedem, a nie pięć. Funkcja niczego nie odejmuje. Trzecia też zawiera siedem, ponieważ porównanie z brakującym drugim argumentem nie daje prawdy. Jeśli pierwszy argument już ma NULL, wynik pozostaje NULL.

Dlatego brak wyniku sam w sobie nie dowodzi równości. Mógł powstać przez równe argumenty albo przez brak pierwszej wartości. Tak samo dwa braki nie stają się potwierdzonymi, równymi pomiarami. Gdy potrzebuję rozróżnić powody, dodaję osobny status.

Mały zestaw zadań do obliczeń

Poniższe dane są syntetyczne. solved to liczba rozwiązanych zadań, total liczba wszystkich, a previous_total poprzednia liczba wszystkich zadań. Kolumna note posłuży później do ćwiczenia z tekstem. Każdy wiersz reprezentuje osobną próbkę raportu.

-- example: fixture
CREATE TEMP TABLE samples (
  id INTEGER PRIMARY KEY,
  solved INTEGER,
  total INTEGER,
  previous_total INTEGER,
  note TEXT
);
INSERT INTO samples VALUES
  (1, 3, 8, 8, ' ok '),
  (2, 0, 5, 4, ''),
  (3, 0, 0, 0, '   '),
  (4, 2, NULL, 6, NULL),
  (5, NULL, 4, 4, 'N/A'),
  (6, 2, 4, NULL, 'n/a');

Druga próbka ma poprawne zero rozwiązanych zadań. Trzecia nie ma żadnych zadań w mianowniku. Czwarta nie ma znanej liczby wszystkich zadań, a piąta nie ma licznika. W szóstej znamy bieżący pomiar, lecz brakuje poprzedniego.

Tworzę tabelę tymczasową, żeby można było bezpiecznie eksperymentować w osobnym połączeniu. Dalsze zapytania jej nie aktualizują. Wprowadzenie NULLIF do wyniku SELECT nie usuwa oryginalnego zera ani tekstu z tabeli.

Mianownik i dzielenie całkowite

Zapis NULLIF(total, 0) zamienia zerowy mianownik na NULL. Dodatni pozostawia bez zmian, a brakujący nadal jest brakujący. Zobacz jednak różnicę między dwiema kolumnami wyniku:

-- example: ratios
SELECT id,
       solved / NULLIF(total, 0) AS integer_ratio,
       1.0 * solved / NULLIF(total, 0) AS ratio
FROM samples
ORDER BY id;

Pierwsze dzielenie w SQLite korzysta z dwóch liczb całkowitych i odrzuca część ułamkową. Trzy ósme stają się zerem, podobnie jak dwie czwarte. W drugiej kolumnie mnożę licznik przez 1.0 przed dzieleniem, więc operacja zachowuje część ułamkową. Zasady arytmetyki opisuje dokumentacja wyrażeń SQLite.

Zero w próbce drugiej ma inne znaczenie niż zera z błędnie dobranego typu. Tu naprawdę rozwiązano zero z pięciu zadań. Próbki trzecia, czwarta i piąta nie dostają liczbowego wskaźnika. Nie zamieniam ich automatycznie na zero, bo ukryłbym trzy różne sytuacje.

Typ zmieniony po wykonaniu dzielenia nie odzyska utraconej części ułamkowej. Poniższy mały test pokazuje to razem z zachowaniem zerowego mianownika w SQLite:

-- example: division
SELECT 100 / 0 AS direct_division,
       100 / NULLIF(0, 0) AS guarded_division,
       (3 / 8) * 1.0 AS cast_too_late,
       3.0 / 8 AS fractional_division;

SQLite zwraca NULL również przy bezpośrednim dzieleniu przez zero. Zdanie „SQL zawsze zgłosi błąd” byłoby błędne. MySQL również dokumentuje wynik NULL dla takiego dzielenia. PostgreSQL opisuje możliwość błędu dzielenia przez zero, a w SQL Server znaczenie mają ustawienia sesji, między innymi ANSI_WARNINGS.

Nie zmieniam ustawień błędów bazy po to, by ukryć problem w raporcie. Jawna reguła dla mianownika jest czytelniejsza. Pamiętaj też, że użyte tutaj 1.0 daje w SQLite arytmetykę zmiennoprzecinkową. Przy obliczeniach wymagających konkretnej precyzji dobierz typ i zaokrąglenie w swoim silniku.

NULLIF nie oblicza różnicy ani nie usuwa duplikatów

Porównam bieżącą liczbę zadań z poprzednią. Obok NULLIF pokazuję równoważny wybór przez CASE i osobne odejmowanie:

-- example: comparison
SELECT id,
       NULLIF(total, previous_total) AS current_if_not_equal,
       CASE WHEN total = previous_total THEN NULL ELSE total END
         AS case_result,
       total - previous_total AS difference
FROM samples
ORDER BY id;

W próbce drugiej NULLIF zwraca pięć, a różnica wynosi jeden. Alias sugerujący różnicę byłby mylący. W próbce szóstej funkcja zwraca cztery, mimo że poprzedniego pomiaru nie znamy. Taki wynik nie jest dowodem zmiany między dwoma kompletnymi pomiarami.

CASE ujawnia warunek wyboru: zwróć brak tylko wtedy, gdy równość została potwierdzona, inaczej zachowaj bieżącą wartość. Microsoft opisuje ten związek. Przy bardziej rozbudowanej klasyfikacji używam CASE WHEN.

Wynik nadal zawiera sześć wierszy. Funkcja nie szuka powtarzających się rekordów i nie wykonuje deduplikacji tabeli. Jeśli porównujesz kolumny, nazwij rezultat tak, aby było jasne, czy pokazujesz wybraną wartość, różnicę czy status porównania.

Zamiana umownych markerów na brak

Załóżmy, że w kolumnie notatki pusty tekst, same zwykłe spacje i dokładnie zapis N/A oznaczają brak. Pozostałe teksty mają zostać. Tę konkretną regułę zapiszę tak:

-- example: clean
SELECT id,
       NULLIF(NULLIF(TRIM(note), ''), 'N/A') AS cleaned_note
FROM samples
ORDER BY id;

Najpierw TRIM usuwa zwykłe spacje z końców. Wewnętrzne NULLIF zamienia pusty wynik na brak, a zewnętrzne robi to samo dla N/A. Małe n/a pozostaje, bo w tej tabeli SQLite domyślne porównanie tekstu używa reguły BINARY. Wpływ reguł porównywania na NULLIF opisuje dokumentacja funkcji.

To założenie dla tego pola, a nie uniwersalny przepis czyszczenia danych. Jeżeli małe litery też oznaczają brak, dopisz taką regułę świadomie. Jeśli zero jest poprawnym pomiarem, nie usuwaj go przez NULLIF(score, 0) tylko dlatego, że na wykresie wygląda mało efektownie.

Co dzieje się z brakami w średniej

Gdy wskaźnik staje się NULL, agregaty reagują na to zgodnie ze swoimi zasadami. Dla naszych danych trzy próbki mają policzony wskaźnik: 0.375, zero i 0.5. Ich średnia to około 0.292. Zastąpienie pozostałych trzech wyników zerami obniża ją do około 0.146.

-- example: summary
WITH rates AS (
  SELECT 1.0 * solved / NULLIF(total, 0) AS ratio
  FROM samples
)
SELECT COUNT(*) AS all_samples,
       COUNT(ratio) AS computed_samples,
       ROUND(AVG(ratio), 3) AS mean_ratio,
       ROUND(AVG(COALESCE(ratio, 0)), 3) AS zero_filled_mean
FROM rates;

Obie średnie można obliczyć, ale opisują inne założenia. Dlatego pokazuję obok liczbę wszystkich próbek i liczbę policzonych wskaźników. Reguły AVG i COUNT opisuje dokumentacja agregatów SQLite. Samo zaokrąglenie służy tu prezentacji wyniku.

To średnia wskaźników poszczególnych próbek. Nie jest automatycznie udziałem rozwiązanych zadań w całej puli. Przy wskaźniku zbiorczym trzeba osobno ustalić, które liczniki i mianowniki należą do wspólnej populacji. Nie sumowałbym bez zastanowienia licznika z wiersza, którego mianownik jest nieznany.

Więcej o wartości zastępczej przed i po agregacji pokazuję w haśle COALESCE w SQL.

Ćwiczenie: wskaźnik z jawnym statusem

Przygotuj raport, który zachowa wszystkie próbki. Brak dowolnej liczby ma dać status missing_data. Niedodatnia liczba wszystkich zadań ma dać invalid_total. Ujemna liczba rozwiązanych albo liczba większa od wszystkich zadań ma dać invalid_solved. Dopiero pozostałe próbki mają status ok i liczbowy wskaźnik.

Spróbuj zapisać warunki, a potem porównaj rozwiązanie:

-- example: exercise
WITH classified AS (
  SELECT id, solved, total,
         CASE
           WHEN solved IS NULL OR total IS NULL THEN 'missing_data'
           WHEN total <= 0 THEN 'invalid_total'
           WHEN solved < 0 OR solved > total THEN 'invalid_solved'
           ELSE 'ok'
         END AS status
  FROM samples
)
SELECT id, status,
       CASE WHEN status = 'ok'
            THEN 1.0 * solved / NULLIF(total, 0)
       END AS ratio
FROM classified
ORDER BY id;

Kolejność warunków rozstrzyga, który powód pokażemy, jeśli wiersz ma kilka problemów. Zero rozwiązanych zadań przy dodatnim mianowniku przechodzi poprawnie. Ujemnego mianownika nie odrzuciłoby samo NULLIF(total, 0), bo jest różny od zera; dlatego walidacja ma własny warunek.

Na koniec zmień jedną próbkę na ujemny mianownik, a inną na liczbę rozwiązanych większą od wszystkich. Sprawdź statusy i brak wskaźników. Potem usuń wiersze w połączeniu testowym i obejrzyj liczniki agregacji. W ten sposób upewnisz się, że raport odróżnia poprawny wynik zero, wadliwy pomiar i brak materiału do obliczeń.