Dlaczego ANSI SQL definiuje SUMĘ (bez wierszy) jako NULL?

W ANSI SQL standard określa (Rozdział 6.5, zestaw opis funkcji) następujące zachowanie zagregowanych funkcjami zbiór pusty Wynik:

COUNT(...) = 0
AVG(...) = NULL
MIN(...) = NULL
MAX(...) = NULL
SUM(...) = NULL

Zwracanie wartości NULL dla AVG, MIN i MAX ma idealny sens, ponieważ średnia, minimalna i maksymalna pustego zestawu jest niezdefiniowana.

Ten ostatni jednak denerwuje mnie: Matematycznie, suma zbioru pustego jest dobrze zdefiniowana: 0. Używając 0, neutralny element dodawania, ponieważ podstawowy przypadek sprawia, że ​​wszystko jest spójne:

SUM({})        = 0    = 0
SUM({5})       = 5    = 0 + 5
SUM({5, 3})    = 8    = 0 + 5 + 3
SUM({5, NULL}) = NULL = 0 + 5 + NULL

Zdefiniowanie SUM({})jako nullzasadniczo sprawia, że ​​„brak wierszy” jest specjalnym przypadkiem, który nie pasuje do innych:

SUM({})     = NULL  = NULL
SUM({5})    = 5    != NULL + 5 (= NULL)
SUM({5, 3}) = 8    != NULL + 5 + 3 (= NULL)

Czy jest jakaś oczywista zaleta dokonanego wyboru (SUMA ma wartość NULL), którego przegapiłem?

Obawiam się, że powodem jest po prostu to, że reguły zostały ustalone w sposób doraźny (podobnie jak wiele innych „cech” standardu ISO SQL) w czasie, gdy agregacje SQL i ich związek z matematyką były mniej zrozumiałe niż są obecnie (*).

To tylko jedna z bardzo wielu niespójności w języku SQL. Sprawiają, że język jest trudniejszy do nauczenia, trudniejszy do nauczenia się, trudniejszy do zrozumienia, trudniejszy w użyciu, trudniejszy do wszystkiego, czego chcesz, ale tak po prostu jest. Z oczywistych powodów kompatybilności wstecznej nie można zmienić reguł „na zimno” i „tak po prostu” (jeśli komitet ISO opublikuje ostateczną wersję normy, a następnie dostawcy zaczną wdrażać tę normę, to ci dostawcy nie docenią bardzo, jeśli w kolejnej wersji reguły są zmieniane w taki sposób, że istniejące (zgodne) implementacje poprzedniej wersji standardu „automatycznie nie spełniają” nowej wersji ...)

(*) Teraz lepiej jest zrozumieć, że agregacje w pustym zbiorze zachowują się bardziej konsekwentnie, jeśli systematycznie zwracają wartość tożsamości (= to, co nazywacie „elementem neutralnym”) bazowego operatora binarnego. Podstawowym operatorem binarnym dla COUNT i SUM jest suma, a jego wartość identyfikacyjna wynosi zero. W przypadku MIN i MAX ta wartość tożsamości jest odpowiednio najwyższą i najniższą wartością danego typu, jeśli dane typy są skończone. Przypadki takie jak uśrednianie, środki harmoniczne, mediany itp. Są jednak niezwykle skomplikowane i egzotyczne pod tym względem.

W sensie pragmatycznym NULLprzydatny jest obecny wynik . Rozważ następującą tabelę i instrukcje:

C1 C2
-- --
 1  3 
 2 -1 
 3 -2 

SELECT SUM(C2) FROM T1 WHERE C1 > 9;

SELECT SUM(C2) FROM T1 WHERE C1 < 9;

Pierwsza instrukcja zwraca NULL, a druga zwraca zero. Gdyby pusty zestaw zwrócił zero SUM, potrzebowalibyśmy innego sposobu na odróżnienie prawdziwej sumy zerowej od pustego zestawu, być może używając licznika. Jeśli rzeczywiście chcemy zerowego dla pustego zestawu, prosty COALESCEspełni to wymaganie.

SELECT COALESCE(SUM(C2),0) FROM T1 WHERE C1 > 9;

Główną różnicą, którą widzę, jest w odniesieniu do typu danych. COUNT ma dobrze określony typ powrotu: liczba całkowita. Wszystkie pozostałe zależą od typu kolumny / wyrażenia, na które patrzą. Ich typ zwrotu musi być zgodny ze wszystkimi elementami zestawu (think float, currency, decimal, bcd, timespan, ...). Ponieważ nie ma zestawu, nie można sugerować typu zwrotu, dlatego NULL jest najlepszą opcją.

Uwaga: w większości przypadków możesz sugerować typ zwracany z typu kolumny, na który patrzysz, ale możesz wykonywać SUMY nie tylko na kolumnach, ale na wszelkiego rodzaju rzeczach. Implikacja typu zwrotu może być bardzo trudna, jeśli nie niemożliwa, w pewnych okolicznościach, szczególnie gdy myślisz o możliwych rozszerzeniach standardu (przychodzą na myśl typy dynamiczne).