W tym wykładzie prezentujemy zapytania z użyciem funkcji agregujących, omawiamy ich działanie na casłej relacji, jak też na grupach rekordów relacji. Wprowadzane są klauzule GROUP BY i HAVING.
W klauzuli SELECT, obok wyrażeń omówionych w Wykładzie I mogą zostać użyte specjalne funkcje, zwracające nie same wartości zapisane w bazie, lecz informacje o strukturze tych wartości. Funkcje te, zwane sumarycznymi, (ale też podsumowującymi lub agregującymi) to:
Argumentem tych funkcji może być wyrażenie odpowiedniego typu, albo DISTINCT wyrażenie, rozumiane tak, jak to było przedstawione w Wykładzie 3. Wyżej wymienione funkcje przewidziane są przez standard języka SQL. Poszczególne jego implementacje uzupełniają je dodatkowymi funkcjami (w MS SQL Server m.in. STDEV – odchylenie standardowe i VAR – wariancja).
Wypisz płacę minimalną, maksymalną, sumę płac, średnią płacę (Sal) z tabeli EMP, a także policz pracowników odnotowanych w tej tabeli.
SELECT MIN(Sal) “Płaca minimalna”, MAX(Sal) ”Płaca maksymalna”, SUM(Sal) ”Suma płac”, AVG(Sal) ”Średnia płaca”, COUNT(Sal) ”Liczba pracowników” FROM EMP;
W powyższym przykładzie, argumentami funkcji agregujących we wszystkich wierszach, są wartości znaczące (nie są NULL). Wyniki zwracane przez funkcje agregujące, są w tym przypadku oczywiste. Natomiast problem może się pojawić, gdy jako argument takiej funkcji pojawi się wartość NULL. Różnice będą dotyczyć wyników funkcji COUNT i AVG. W obu przypadkach zostaną uwzględnione tylko wartości znaczące! Pokazuje to poniższa instrukcja:
SELECT AVG(Comm) S1, COUNT(Comm) L1, AVG(Isnull(Comm, 0)) S2, COUNT(Isnull(Comm, 0)) L2 FROM EMP;
I jej wynik:
S1 L2 S2 L2 ----- ----- ----- ----- 550 4 157 14
Jak widać, wartości NULL nie są uwzględniane przez funkcje agregujące. Szczególnie należy zwrócić uwagę na tę właściwość przy liczeniu rekordów wynikowych przy użyciu funkcji COUNT. Użycie, jako argumentu tej funkcji, wyrażenia mogącego zwrócić NULL, może oznaczać błędny wynik. Stąd bezpiecznym rozwiązaniem, jest użycie w roli jej argumentu wartości stałej (liczbowej lub tekstowej), albo ”*”, jako skrótowego odwołania do całego wiersza.
Funkcje agregujące mogą być stosowane na całej relacji, tak jak to zostało pokazane w powyższych przykładach, ale mogą też być stosowane w odniesieniu do pewnych grup rekordów w relacji. Definiowanie takich grup odbywa się w klauzuli GROUP BY. Dla jednakowych wartości wyrażeń podanych w tej klauzuli tworzone są grupy rekordów, w obrębie których wyliczane są wartości funkcji agregujących.
Oblicz średnią płacę dla każdego stanowiska (Job).
SELECT Job, Avg(Sal) ”Średnia płaca na stanowisku” FROM EMP GROUP BY Job;
W wyniku otrzymujemy tyle wierszy, ile jest różnych stanowisk odnotowanych w tabeli EMP. Każdy wiersz zawiera jedną nazwę stanowiska i obliczoną dla tego stanowiska średnią płacę.
Grupowanie rekordów może być wykonywane względem kilku wyrażeń.
Oblicz średnią płacę dla każdego stanowiska (Job) w każdym dziale.
SELECT Deptno ,Job ,Avg(Sal) ”Średnia płaca na stanowisku w dziale” FROM EMP GROUP BY Deptno, Job;
Tym razem w wyniku pojawi się tyle wierszy, ile jest unikalnych par wartości {Deptno, Job}.
Na liście SELECT w poleceniu zawierającym klauzulę GROUP BY mogą pojawić się tylko:
W klauzuli GROUP BY nie wolno odwoływać się do aliasów wyrażeń, nadanych w klauzuli SELECT.
Jeżeli wyrażenie przywołane w klauzuli GROUP BY oprócz innych wartości zawiera NULL, grupy rekordów tworzone są dla każdej unikalnej wartości, włączając w to także NULL. Inaczej mówiąc, wszystkie wiersze, mające tą samą wartość wyrażenia grupującego, trafiają do jednej grupy. Dotyczy to także NULL, dla którego tworzona jest oddzielna grupa.
Wynik zapytanie (polecenia) grupującego zawierającego klauzulę GROUP BY, może zostać ograniczony poprzez zastosowanie klauzuli HAVING. Klauzula HAVING umożliwia zdefiniowanie warunku, który musi spełniać grupa, aby pojawiła się w wyniku końcowym. Na liście HAVING mogą się pojawiać funkcje agregujące, niekoniecznie użyte wcześniej na liście SELECT. Mogą się także pojawiać wyrażenia dające się zinterpretować dla grup zdefiniowanych w klauzuli GROUP BY. Oczywiście mogą się także pojawiać wyrażenia użyte wcześniej na liście SELECT.
Oblicz średnią płacę dla każdego stanowiska (Job) w każdym dziale. Pomiń grupy zawierające tylko jeden rekord.
SELECT AVG(sal) ,Job ,Deptno FROM EMP GROUP BY Job, Deptno HAVING COUNT(1) > 1;
Zwróćmy uwagę, na istotną różnicę, pomiędzy klauzulami WHERE i HAVING. Warunek definiowany w klauzuli WHERE dotyczy rekordów zwracanych (odczytywanych z tabel w bazie danych) w wyniku działania instrukcji SELECT.
Warunek HAVING dotyczy wyników operacji na grupach. Nie wolno używać funkcji grupujących w klauzuli WHERE.
Najpierw tworzona jest relacja wynikająca z przywołanych w klauzuli FROM tabel i zdefiniowanych warunków złączenia. Następnie z tej relacji usuwane są rekordy, niespełniające warunku zdefiniowanego w klauzuli WHERE. Dopiero po wykonaniu tych operacji tworzone są grupy rekordów zgodnie z klauzulą GROUP BY, na tych grupach wyliczane są wyrażenia zawierające funkcje agregujące. I dopiero po tych wszystkich operacjach, z wyniku, usuwane są rekordy niespełniające warunku z klauzuli HAVING. Nie należy zatem definiować w klauzuli HAVING warunków, które mogą zostać zrealizowane w wyniku działania klauzuli WHERE. Zapobiega to wykonywaniu operacji grupowania i wyliczania wartości na rekordach, które i tak zostaną usunięte z wyniku.
Oblicz budżet (sumę) płac dla każdego działu, poza działem nr 10.
SELECT Sum(Sal) ,Deptno FROM EMP WHERE Deptno <> 10 GROUP BY Deptno;
W powyższym rozwiązaniu najpierw zostaną wyeliminowane rekordy dotyczące departamentu Nr 10, a potem utworzone grupy rekordów i obliczone dla nich wartości SUM(Sal). Eliminuje ono zbędne obliczenia, jakie mają mają miejsce w rozwiązaniu drugim:
SELECT Sum(Sal) ,Deptno FROM EMP GROUP BY Deptno HAVING Deptno <> 10;
Poniżej przedstawiamy “koncepcyjny” algorytm wykonania zapytania z użyciem funkcji agregujących:
Algorytm przedstawiony powyżej, jest tylko algorytmem koncepcyjnym – rzeczywista realizacja tego zagadnienia przez serwer bazy danych może wyglądać zupełnie inaczej.
Poniżej przedstawimy realizację zapytania grupującego „krok – po – kroku”, zgodnie z powyższym algorytmem.
Znaleźć średnie zarobki w działach, nazwy i lokalizacje działów. Pominąć dział o numerze 10 oraz te działy, dla których średnie zarobki są mniejsze od 1600.
Krok 1. Wybierane są wszystkie rekordy z tabel, w których znajdują się potrzebne dane.
SELECT * FROM EMP, DEPT;
W wyniku dostarczany jest iloczyn kartezjański wszystkich rekordów, zawierających wszystkie wartości kolumn ze wszystkich tabel wchodzących w skład zapytania (w naszym przypadku EMP i DEPT).
Krok 2. Do otrzymanego wyniku stosowany jest warunek zawarty w klauzuli WHERE lub INNER JOIN (predykat złączenia tabel) oraz inne warunki ograniczające.
SELECT * FROM EMP, DEPT WHERE EMP.Deptno = DEPT.Deptno AND DEPT.Deptno <> 10;
Z wyniku usuwane są wiersze, dające dla warunku złączenia wynik False lub Null.
Krok 3. Pozostałe kombinacje rekordów dzielone są na grupy zgodne z klauzulą GROUP BY (według jednakowych wartości wyrażeń w tej klauzuli),
SELECT DEPT.Deptno, Loc, Dname FROM EMP, DEPT WHERE EMP.Deptno = DEPT.Deptno AND DEPT.Deptno <> 10 GROUP BY DEPT.Deptno, Loc, Dname;
Tutaj w klauzuli SELECT mogą się pojawić tylko te nazwy kolumn (tylko te wyrażenia), które występują w klauzuli GROUP BY. W wyniku powstaje tyle grup rekordów, ile różnych kombinacji wynika z klauzuli GROUP BY.
Krok 4. Dla każdego pozostającego wiersza reprezentującego grupę obliczane są wartości wyrażeń na liście SELECT.
SELECT AVG(Sal), DEPT.Deptno, Loc, Dname FROM EMP, DEPT WHERE EMP.Deptno = DEPT.Deptno AND DEPT.Deptno <> 10 GROUP BY DEPT.Deptno, Loc, Dname;
W wyniku powstaje tyle rekordów, ile różnych kombinacji wynikało z klauzuli GROUP BY.
Krok 5. Do każdej grupy (a teraz już rekordu) zastosowany zostaje warunek w klauzuli HAVING. Pozostawiane są tylko rekordy, dla których wartość warunku jest True.
SELECT AVG (Sal), DEPT.Deptno, Loc, Dname FROM EMP, DEPT WHERE EMP.Deptno = DEPT.Deptno AND DEPT.Deptno <> 10 GROUP BY DEPT.Deptno, Loc, Dname HAVING AVG (Sal) > 1600 ORDER BY AVG(Sal) Desc;
Jeżeli po SELECT występuje DISTINCT, usunięte zostaną powtarzające się rekordy. Jeżeli występuje klauzula ORDER BY, rekordy zostaną uporządkowane według zadanego w niej porządku.
Na koniec chcemy zaprezentować przykład, pokazujący różnicę w użyciu kryteriów w klauzulach WHERE oraz HAVING.
Znaleźć średnie zarobki w działach. W obliczeniach pominąć pracowników na stanowisku MANAGER, a w wyniku uwzględnić tylko działy, dla których średnia płaca jest większa niż 1000.
Rozwiązanie I
SELECT AVG(Sal) "Średnia płaca", Deptno FROM EMP WHERE Job <> 'MANAGER' GROUP BY Deptno HAVING AVG(Sal) > 1000;
i jego wynik
Średnia płaca deptno ------------- ----------- 3150 10 1740 20 1310 30
Rozwiązanie II
I podobne (z pozoru!) Rozwiązanie II, gdzie dotychczasowy warunek dotyczący stanowiska MANAGER, użyty prawidłowo w rowiązaniu I w klauzuli WHERE, teraz został przeniesiony do klauzuli HAVING.
SELECT AVG(Sal) "Średnia płaca", Deptno FROM EMP GROUP BY Job, Deptno HAVING AVG(Sal) > 1000 AND Job <> 'MANAGER';
i jego wynik
Średnia płaca deptno ------------- ----------- 1300 10 5000 10 3000 20 1400 30
Jak widać, dla obu rozwiązań otrzymujemy różne wyniki! Przyczyną jest różnica w etapie realizacji zapytania, na którym usunięto rekordy z wartością job = ‘MANAGER’. W rozwiązaniu I usunięto je PRZED operacją grupowania, w rozwiązaniu II już PO tej operacji.