#147 Rozdzia� 7. Przetwarzanie wynik�w zapyta� Poznane dotychczas w�a�ciwo�ci instrukcji SELECT pozwalaj� na wyszukiwanie w bazie potrzebnych danych. Przy jej pomocy znajdowali�my przychody z wybranego filmu lub wszystkich re�yser�w zamieszka�ych w podanym stanie. Wyra�enie SELECT posiada tak�e bogate mo�liwo�ci tworzenia r�nego rodzaju wylicze� na danych, pozwalaj�ce spojrze� na dane w spos�b bardziej ca�o�ciowy. Mo�na wylicza� warto�ci �rednie, sumy dla wybranych kolumn lub dzieli� dane na kategorie i w�r�d nich dokonywa� oblicze�. Na przyk�ad wykorzystuj�c funkcje agreguj�ce i grupowanie danych mo�na wyliczy� �redni bud�et dla film�w wyprodukowanych przez konkretne studio filmowe lub przychody z film�w wyprodukowanych w wybranym roku. Ten rozdzia� omawia funkcje agreguj�ce, kt�re umo�liwiaj� wykonywanie oblicze� na wybranych kolumnach. Pokazane zostanie r�wnie� wykorzystanie klauzuli GROUP BY, kt�ra umo�liwia podzia� wynik�w na kategorie. Wyb�r warto�ci unikalnych: operator DISTINCT Tabela Movies zawiera kilka film�w z ka�dego studia. Gdyby�my chcieli otrzyma� po prostu list� wszystkich studi�w filmowych, to zapytanie z listingu 7.1 nie wykona zadania poprawnie. ------------------------ Listing 7.1. Wybranie wszystkich studi�w z bazy danych SELECT studio_id FROM Movies #148 STUDIO_ID ------------- 1 2 4 3 1 2 3 3 4 1 10 rows selected ------------------------ Jak wida�, wszystkie studia pojawiaj� si� na li�cie, ale niekt�re z nich pojawiaj� si� wielokrotnie. J�zyk SQL posiada operator DISTINCT, kt�ry umo�liwia wyeliminowanie z wyniku warto�ci powtarzaj�cych si�. Przyk�ad zastosowania tego operatora znajduje si� na listingu 7.2. ------------------------ Listing 7.2. Wyb�r -warto�ci niepowtarzalnych z tabeli Movies SELECT DISTINCT studio_id FROM Movies STUDIO_ID --------- 1 2 3 4 ------------------------ Jak wida�, zastosowanie operatora DISTINCT spowodowa�o, �e na li�cie wynikowej nie ma warto�ci powtarzaj�cych si�. Podstawowa sk�adnia wyra�e� wykorzystuj�cych operator DISTINCT wygl�da nast�puj�co: SELECT [DISTINCT] select_list FROM tablet , table, ...] WHERE expression] [ORDER BY expression] Wybieranie unikalnych kombinacji warto�ci W przypadku, gdy na li�cie pojawia si� kilka nazw kolumn, wybierana jest ka�da niepowtarzalna kombinacja warto�ci. Listing 7.3 zawiera list� studi�w filmowych, identyfikatory re�yser�w i tytu�y film�w. ------------------------ Listing 7.3. Tytu� (movie_title), studio (studio_id), identyfikator re�ysera (directorjd) z tabeli Filmy (Movies) SELECT movie_title, studio_id, directo'r_id FROM Movies MOVIE_TITLE STUDIO_ID DIRECTOR_ID ------------------------ Yegetable House 1 1 Prince Kong 2 10 The Code Warrior 4 2 Bulli Durham 3 9 Codependence Day 1 1 #149 The Linux Files 2 2 SQL Strikes Back 3 9 The Programmer 3 1 Hard Code 4 10 The Rear Windows 1 1 10 rows selected ------------------------ Kolejny listing 7.4 zawiera list� studi�w filmowych i re�yser�w, z kt�rej, u�ywaj�c operatora DISTINCT, wyeliminowano warto�ci powtarzaj�ce si�. ------------------------ Listing 7.4. Niepowtarzalne kombinacje kolumn studio Jd i directorj.d SELECT DISTINCT studio_id, dlrector_id FROM Movles STUDIO_ID DIRECTOR_ID ------------------------ 1 1 2 2 2 10 3 1 3 9 4 2 4 10 7 rows selected ------------------------ 7 wierszy zosta�o wybranych na listingu 7.4, poniewa� dwa filmy by�y wykonane przez tego samego re�ysera w tym samym studiu filmowym (wiemy o tym na podstawie danych z listingu 7.3). Gdyby jednak w listingu 7.4 zosta�y wybrane 3 kolumny, to jako wynik zapytania pojawi�yby si� unikalne kombinacje 3 warto�ci. Zwr��my uwag� na listing 7.5. ------------------------ Listing 7.5. Wyszukanie w tabeli wszystkich niepowtarzalnych wierszy SELECT DISTINCT * FROM Movies ------------------------ Zapytanie z listingu 7.5 zwraca wszystkie wiersze z tabeli Movies. W przypadku, gdyby zwr�ci�o ich mniej, to oznacza�oby, �e niekt�re wiersze s� identyczne. Jednak jak wiadomo, spe�nienie wymog�w relacyjnego modelu bazy danych nie dopuszcza na wprowadzenie do tabeli powtarzaj�cego si� wiersza. Klucz powinien by� unikalny dla ka�dego wiersza, co zapewnia, �e w tabeli nigdy nie ma dw�ch identycznych wierszy. DISTINCT i NULL Jak wiadomo z wcze�niejszej dyskusji o warto�ciach null, nie mog� one by� uwa�ane za warto�ci sobie r�wne, poniewa� ich warto�� jest nieokre�lona. Jednak operator DISTINCT �amie t� zasad� i w wyra�eniach z tym operatorem wszystkie warto�ci null s� traktowane jak warto�ci identyczne. #150 Wyb�r warto�ci z kolumny gross przy pomocy operatora DISTINCT pokazuje podstawowe w�a�ciwo�ci tego operatora. ------------------------ Listing 7.6. Niepowtarzalne warto�ci z kolumny przych�d (gross) SELECT DISTINCT gross FROM Movies GROSS -------- 10 17.5 17.8 30 45.3 6 rows selected ------------------------ Jak wiadomo, dwa wiersze z tabeli Movies maj� w kolumnie przych�d (gross) warto�� null. W zapytaniu pojawia si� tylko jedna warto�� null (co jest zgodne z zasad� dzia�ania operatora DISTINCT ). Funkcje agreguj�ce W j�zyku SQL dost�pnych jest bardzo du�o funkcji agreguj�cych. S� to funkcje, kt�re nie dzia�aj� na pojedynczej warto�ci pola, ale na grupie warto�ci zwracanych przez zapytanie. Na przyk�ad mo�emy w tabeli policzy� liczb� wierszy spe�niaj�cych okre�lone kryteria lub mo�emy wyliczy� warto�� �redni� dla wszystkich warto�ci z wybranej kolumny. Funkcje te dzia�aj� na wszystkich wierszach w tabeli, na pewnej grupie wierszy wybranej klauzul� WHERE lub na grupach danych wybranych klauzul� GROUP BY. Funkcji agreguj�cych u�ywamy pos�uguj�c si� sk�adni�: SELECT function(column) FROM table [, table ...] [WHERE condition] U�ycie tych funkcji zwykle zwi�zane jest z operacj� na jednej kolumnie (na kt�rej wykonywane s� obliczenia), a jako wynik zwracany jest tylko jeden wiersz. Listing 7.7 zawiera zapytanie wykorzystuj�ce funkcj� agreguj�c� COUNT (): ------------------------ Listing 7.7. U�ycie funkcji agreguj�cej SELECT COUNT(*) FROM Movies COUNTI*) --------- 10 ------------------------ #151 Jak wida�, wynikiem zapytania jest jeden wiersz. Zawiera on liczb� wszystkich wierszy z tabeli Movies. Gdyby do listy p�l w cz�ci select_list doda� wyra�enie nie b�d�ce funkcj� agreguj�c�, to pojawi si� komunikat informuj�cy o b��dzie. Pokazuje to listing 7.8. ------------------------ Listing 7.8. Wybranie kolumny i funkcji agreguj�cej w jednym zapytaniu SELECT title, COUNT(*) FROM Movies SELECT title, COUNTI*) FROM Movies ERROR at line 1 ORA 00937: not a single group group function ------------------------ Z drugiej strony mo�na bez problemu u�y� wyra�enia matematycznego i �a�cuchowego, jak pokazuje listing 7.9. ------------------------ Listing 7.9. Wyra�enia �a�cuchowe z funkcj� agreguj�c� SELECT 'There ar�', COUNT(*1, 'movies in the database' FROM Movies; 'THERE ARE COUNTI*) 'MOVIESINTHEDATABASE' ------------------------------------------------ There are 8 movies in the database ------------------------ Klauzula GROUP BY, kt�ra b�dzie szczeg�owo om�wiona dalej, umo�liwia podzia� zapytania na grupy wzgl�dem warto�ci z wybranej kolumny i wykonanie funkcji agreguj�cej dla ka�dej tak zdefiniowanej kategorii. ================ Rada Mo�na u�y� wielu funkcji agreguj�cych w cz�ci SELECT zapytania. Na przyk�ad aby wyliczy� sum� bud�etu i przychod�w w jednym zapytaniu nale�y napisa� nast�puj�c� instrukcj�: SELECT SUM(budget), SUM(gross) FROM Movies ================== Funkcje agreguj�ce i klauzula WHERE Klauzula WHERE mo�e by� wykorzystana w zapytaniach u�ywaj�cych funkcji agreguj�cych do okre�lenia wierszy, na kt�rych b�d� wykonywane obliczenia. Na przyk�ad zapytanie, kt�re zliczy filmy wyprodukowane latach osiemdziesi�tych wygl�da, tak jak na listingu 7.10. ------------------------ Listing 7.10. U�ycie funkcji agreguj�cej z klauzul� WHERE SELECT COUNTI*) FROM Movies WHERE release_date BETWEEN '01-JAN-19801 AND '31-DEC-1989' COUNT(*) -------- 2 ------------------------ #152 W przyk�adowej bazie danych znajduj� si� tylko dwa filmy z tego okresu. Ka�de wyra�enie WHERE, kt�re mo�e by� wykorzystane w standardowej instrukcji SE-LECT, mo�e by� zastosowane wraz z funkcjami agreguj�cymi. Zmiana nazwy wynik�w funkcji agreguj�cej S�owo kluczowe AS mo�e by� wykorzystane do zmiany nazwy rezultat�w funkcji agreguj�cych, tak jak mo�e by� wykorzystane do zmiany nazwy ka�dego innego wyra�enia. Listing 7.11 zawiera przyk�ad wykorzystania AS w zapytaniu agreguj�cym. ----------------------- Listing 7.11. S�owo kluczowe AS w zapytaniu agreguj�cym SELECT COUNTI*) AS total FROM Movies TOTAL ------- 8 ------------------------ ==================== Rada Je�li zamierzasz wstawia� kod zapytania do programu, musisz pami�ta� o zmianie nazw kolumn b�d�cych wynikiem funkcji agreguj�cych. Zmiany nazwy dokonujemy poprzez u�ycie s�owa kluczowego AS. W wi�kszo�ci j�zyk�w programowania nie jest mo�liwe korzystanie z nazw nadawanych automatycznie. =================== Funkcja COUNT() Pierwsz� funkcj� agreguj�c�, kt�r� chc� dok�adnie om�wi�, jest funkcja COUNT (). U�y�em jej ju� w kilku listingach w tym rozdziale. Funkcja zlicza ilo�� wierszy w zapytaniu. Je�li chcemy zna� ilo�� wierszy zwr�conych przez zapytanie, najpro�ciej u�y� funkcji COUNT(*), jak pokaza�em we wcze�niejszych cz�ciach tego rozdzia�u. S� dwa powody, dla kt�rych warto u�ywa� funkcji COUNT (*) do tego celu. Po pierwsze, pozwalamy optymalizatorowi bazy danych wybra� kolumn� do wykonywania oblicze�, co czasem nieznacznie podnosi wydajno�� zapytania, po drugie, nie musimy si� martwi� o warto�ci null zawarte w kolumnie oraz o to, czy kolumna o podanej nazwie w og�le istnieje. Zapytanie z listingu 7.12 zlicza osoby ze stanu Teksas. ------------------------ Listing 7.12. Zliczanie wierszy w tabeli Movies SELECT COUNT(*) FROM People WHERE person st�te = 'TX' COUNT(*) ---------- 4 ------------------------ #153 Funkcja COUNT() i warto�ci null Funkcja w postaci couNT(column_name) nie uwzgl�dnia p�l z warto�ciami null. U�ycie funkcji w postaci COUNT (*) zlicza wszystkie wiersze bez wzgl�du na zawarto��. Fakt, �e wiersze z warto�ci� null nie s� zliczane, mo�e by� przydatny, gdy warto�� null oznacza co� szczeg�lnego. Na przyk�ad w tabeli Movies warto�� null w kolumnie przych�d wskazuje, �e film jeszcze nie zosta� skierowany do dystrybucji i nie zarobi� pieni�dzy. Zliczaj�c ilo�� pozycji w polu przych�d (gross) mo�na okre�li� ile film�w jest w dystrybucji. Pokazuje to listing 7.13. ------------------------ Listing 7.13. Zliczanie wierszy w kolumnie gdzie wyst�puj� warto�ci null SELECT COUNT(gross) FROM Movies COUNT(GROSS) ------------- 8 ------------------------ Dwa wiersze z warto�ciami null w polu przych�d (gross) nie s� brane pod uwag� w wyniku zapytania. Funkcja COUNT() i operator DISTINCT Operator DISTINCT mo�e by� u�yty r�wnocze�nie z funkcj� COUNT (), aby zwr�ci� liczb� unikalnych rekord�w. U�ywamy go wed�ug przedstawionej poni�ej sk�adni: SELECT COUNT(DISTINCT column) FROM table Zapytanie na listingu 7.14 zlicza studia w tabeli Movies, uwzgl�dniaj�c ka�de z nich tylko raz. ------------------------ Listing 7.14. Zliczanie tylko warto�ci niepowtarzalnych SELECT COUNT(DISTINCT studio_id) FROM Movies COUNT(DISTINCTSTUDIO_ID) ------------------------ 4 ------------------------ Funkcje SUM() i AVG() Funkcja SUM () dodaje wszystkie warto�ci i zwraca pojedynczy wynik, a funkcja AVG (} wylicza warto�� �redni� dla grupy warto�ci. W przeciwie�stwie do funkcji COUNT (), kt�ra dzia�a dla wszystkich typ�w danych, funkcje SUM () i AVG {) dzia�aj� tylko dla argument�w liczbowych. #154 Listing 7.15 pokazuje zapytanie, kt�re wylicza bud�et wszystkich film�w zgromadzonych w bazie danych. Zapytanie z listingu 7.16 wylicza �redni bud�et dla tych film�w. ------------------------ Listing 7.15. U�ycie funkcji SUM() do zsumowania warto�ci w kolumnie SELECT SUM(budget) FROM Movies SUM(BUDGET) -------------- 262.85 ------------------------ ------------------------ Listing 7.16. Utycie funkcji A VG() do obliczenia warto�ci �redniej SELECT AVG(budget) FROM Movies AVG(BUDGET) ---------- 26.285 ------------------------ =============== Rada W przypadku funkcji SUM () i AVG (} warto�ci null s� ignorowane (nie s� uwzgl�dniane do oblicze�). ================== Obie funkcje mog� by� u�yte ��cznie z operatorem DISTINCT, podobnie jak funkcja COUNT (). Je�li u�yjemy tego operatora, to obliczenia s� przeprowadzane tylko dla warto�ci unikalnych. Por�wnajmy wyniki zapyta� przedstawionych na listingach 7.16, 7.17 z tymi na listingach 7.18 i 7.19. ------------------------ Listing 7.17. Funkcja SUM() dodaje warto�ci w wybranej kolumnie SELECT SUM(DISTINCT budget) FROM Movies SUM(DISTINCT BUDGET) ------------------------ 65.1 ------------------------ ------------------------ Listing 7.18. Wyliczenie warto�ci �redniej SELECT AVG(DISTINCT budget) FROM Movies AVG(DISTINCT BUDGET) ------------------------ 23.65 ------------------------ Funkcje MIN() i MAX() Funkcja MIN () s�u�y do znajdowania warto�ci najmniejszej w zbiorze warto�ci, a funkcja MAX () najwi�kszej. Obie funkcje mog� by� u�yte dla r�nych typ�w danych. #155 Funkcja MAX () znajduje najwi�kszy �a�cuch danych (zgodnie z regu�ami por�wnywania �a�cuch�w om�wionymi w rozdziale 5.), najnowsz� dat� (lub najodleglejsz� w przysz�o�ci) oraz najwi�ksz� liczb� w zestawieniu. Funkcja MIN {) znajduje odpowiednio warto�ci najmniejsze. ================= Rada Ani funkcja MIN () ani MAX () nie zwraca warto�ci null, je�eli w zestawieniu wyst�puj� jeszcze inne warto�ci. Warto�� null jest traktowana jako nieokre�lona i nie mo�na jej por�wnywa� z innymi. ================= Zar�wno funkcja MAX () jak i funkcja MIN () mog� by� stosowane ��cznie z operatorem DISTINCT, ale nie ma to wi�kszego znaczenia, poniewa� obie funkcje i tak zwracaj� tylko jedn� warto�� z zestawienia. Listing 7.19 przedstawia zapytanie wykorzystuj�ce funkcj� MIN{) do znalezienia najwcze�niej wydanego filmu. ------------------------ Listing 7.19. Wyszukanie najstarszej daty w tabeli Movies SELECT MIN(release_date) FROM Movies MIN(RELEA) --------------- 01-JAN-75 ------------------------ Wykonywanie oblicze� z podzia�em na kategorie Za��my, �e potrzebujemy dane do raportu zawieraj�cego zestawienie bud�etu ka�dego studia filmowego. Mo�na tego dokona� wykonuj�c wiele zapyta�, z kt�rych ka�de wylicza bud�et wybranego studia. Przyk�adowe zapytanie pokazuje listing 7.20. ------------------------ Listing 7.20. Zapytanie wyliczaj�ce bud�et studia Delighted Artists SELECT SUM(budget) FROM Movies WHERE studio_id = 3 SUM(BUDGET) -------------- 65.1 ------------------------ Taki spos�b rozwi�zania tego zadania rodzi dwa problemy. Po pierwsze, musimy utworzy� oddzielne zapytanie dla ka�dego studia filmowego. Po drugie, musimy zna� nazwy wszystkich studi�w filmowych wprowadzonych do bazy. W zwi�zku z tym metoda ta okazuje si� bardzo niepraktyczna przy tworzeniu podobnych raport�w. Zamiast tworzy� oddzielne zapytania, mo�emy u�y� klauzuli GROUP BY, kt�ra automatycznie dzieli wyniki zapytania na wybrane kategorie. #156 Klauzula GROUP BY Klauzula GROUP BY umo�liwia grupowanie wynik�w wzgl�dem zawarto�ci wybranej kolumny. W pewnym sensie jej dzia�anie jest podobne do operatora DISTINCT, poniewa� zwraca pojedynczy wynik dla ka�dej kategorii. Je�eli jednak u�yjemy w zapytaniu jednocze�nie funkcji agreguj�cej dla innej kolumny, to funkcja ta dokona zlicze� dla kategorii okre�lonych w klauzuli GROUP BY. Sk�adnia zapytania wykorzystuj�cego klauzule GRUP BY wygl�da nast�puj�co: SELECT select_list FROM table [ , table, ...] [WHERE condition) [GROUP BY group_by_list] [ORDER BY order by list] Liczba wierszy stanowi�cych wynik zapytania jest taka sama, jak liczba wierszy zwr�cona przez zapytanie z operatorem DISTINCT dla kolumny wybranej przez klauzul� GROUP BY. Na listingu 7.21 u�y�em funkcji SUM () do wyliczenia ��cznego bud�etu film�w z tabeli Filmy (Movies). Na listingu 7.22 dzi�ki wykorzystaniu w zapytaniu klauzuli GROUP BY wyliczenia przeprowadzone s� dla ka�dej kategorii osobno. ------------------------ Listing 7.21. ��czny bud�et z tabeli Filmy (Movies) SELECT SUM(budget) FROM Movies SUM(BUDGET) ------------- 277.85 ------------------------ ------------------------ Listing 7.22. Bud�et film�w z podzia�em na ka�de studio filmowe SELECT studio_id, SUM(budget) FROM Movies GROUP BY studio id STUDIO_ID SUM(BUDGET) 1 85 2 24.25 3 65.1 4 87.3 ------------------------ Jak wida�, bud�et zosta� wyliczony dla ka�dego studia filmowego osobno. ------------------------ Listing 7.23. U�ycie operatora DISTINCT do wybrania warto�ci niepowtarzalnych SELECT DISTINCT studio_id FROM Movies STUDIO_ID ------------ 1 2 3 4 #157 W tym wypadku u�ycie klauzuli GROUP BY bez funkcji agreguj�cych daje taki sam efekt, jak u�ycie operatora DISTINCT. Pokazuje to listing 7.24. ------------------------ Listing 7.24. Klauzula GROUP BY bez funkcji agreguj�cych SELECT studio_id FROM Movies GROUP BY studlo_id STUDIO_ID ---------- 1 2 3 4 ------------------------ Kolejne podobie�stwo GROUP BY do operatora DISTINCT polega na tym, �e warto�ci null traktowane s� w ten sam spos�b. Wszystkie pozycje z warto�ci� null w kolumnie grupuj�cej s� zaliczane do tej samej kategorii. Jest bardzo wa�ne, aby kolumna, wzgl�dem kt�rej dokonujemy podzia�u na kategorie, znajdowa�a si� w cz�ci deklaracyjnej wyra�enia SELECT. Zapytanie podane poni�ej nie dzia�a, poniewa� nie podano kolumny studio_id na li�cie deklaracji (select_list). SELECT SUM(budget) FROM Movies GROUP BY studio_id Klauzula GROUP BY dzia�a z wszystkimi funkcjami agreguj�cymi COUNT (), SUM (), AVG (), MIN (), MAK () zgodnie z podanymi tutaj zasadami. Grupy i podgrupy Klauzula ORDER BY umo�liwia porz�dkowanie wynik�w zapytania wewn�trz kategorii, a klauzula GROUP BY tworzy grupy i podgrupy w przypadku, gdy wybrana jest wi�cej ni� jedna kolumna. Listing 7.25 pokazuje przyk�ad, w kt�rym zliczono ilo�� film�w, kt�re re�yser wykona� dla wybranego studia. Klauzula ORDER BY musi wyst�powa� po GROUP BY, kolejno�� ta nie jest opcjonalna. ------------------------ Listing 7.25. Tworzenie podgrup SELECT studio_id, director_id, COUNT(*) FROM Movies GROUP BY studio id, director id. STUDIO_ID DIRECTOR_ID COUNT(*) ----------------------- 1 1 3 2 2 1 2 10 1 3 1 1 3 9 2 4 2 1 4 10 1 ----------------------- #158 Jak wida�, wyniki s� najpierw dzielone na kategorie wed�ug studia, a nast�pnie wed�ug re�ysera. Gdyby w klauzuli GROUP BY zmieni� kolejno�� kategorii, najpierw wed�ug re�ysera, a nast�pnie wed�ug studia. Wynik pokazywa�by ile film�w wybrany re�yser wykona� dla poszczeg�lnych studi�w filmowych. Ilustruje to listing 7.26. ----------------------- Listing 7.26. Kolejny przyk�ad tworzenia podgrup SELECT studio_id, director_id, COUNT(*) FROM Movies GROUP BY director id, studio id STUDIO_ID DIRECTOR_ID COUNT(* ----------------------- 1 1 3 3 1 1 2 2 1 4 2 1 3 9 2 2 10 1 4 10 1 7 rows selected ----------------------- Klauzule GROUP BY i ORDER BY Klauzula ORDER BY stosowana ��cznie z klauzul� GROUP BY sortuje wyniki zapytania. Wymaga to dodania za klauzul� GROUP BY klauzuli ORDER BY. Przyk�ad pokazuje listing 7.27. ----------------------- Listing 7.27. GROUP BY i ORDER BY w jednym zapytaniu SELECT Studi0_id, COUNT(*) FROM Movies GROUP BY studio_id ORDER BY studio_id STUDIO_ID COUNT(*) ----------------------- 1 3 2 2 3 3 3 2 4 2 ----------------------- Klauzula GROUP BY nie mo�e zawiera� nazwy kolumny nie uwzgl�dnionej na li�cie SELECT, z kolei ORDER BY nie mo�e wykorzystywa� nazwy nie zawartej w klauzuli GROUP BY. Pr�ba sortowania wzgl�dem nazwy kolumny nie zawartej w klauzuli GROUP BY spowoduje komunikat o b��dzie. Mo�na umieszcza� wiele kolumn w cz�ci ORDER BY je�li tylko s� one wymienione w klauzuli GROUP BY. Wyja�nia to przyk�ad na listingu 7.28. #159 Listing 7.28. U�ycie wielu nazw kolumn w klauzulach ORDER BY i GROUP BY SELECT studio_id, director_id, COUNT(*) FROM Movies GROUP BY studio id, director_id ORDER BY studio id, director id, STUDIO_ID DIRECTOR_ID COUNT(*) ----------------------- 1 1 3 2 21 2 10 1 3 1 1 3 9 2 4 2 1 4 10 1 7 rows selected ----------------------- Ustawienie kolumn w cz�ci ORDER BY nie musi by� takie samo, jak w cz�ci GROUP BY. Mo�na spowodowa� sortowanie najpierw wzgl�dem kolumny drugiej, a nast�pnie wed�ug pierwszej. Wyniki przedstawiono na listingu 7.29, a na listingu 7.30 te same wyniki w bardziej czytelnej postaci. ----------------------- Listing 7.29. Sortowanie na podstawie drugorz�dnego kryterium grupowania SELECT studio_id, director_id, COUNT (*) FROM Movies GROUP BY studio_id, director_id ORDER BY director id, studio id STUDIO_ID DIRECTOR_ID COUNT(*) ----------------------- 1 1 3 3 1 1 2 2 1 4 2 1 3 9 2 2 10 1 4 10 1 7 rows selected ----------------------- ----------------------- Listing 7.30. Inna wersja zapytania z listingu 7.29 SELECT studio_id, director_id, COUNT(*) FROM Movies GROUP BY studio^id, director_id ORDER BY director id, studio id STUDIO_ID DIRECTOR_ID COUNT(*) ----------------------- 1 1 3 1 3 1 2 2 1 2 4 1 9 3 2 10 2 1 10 2 1 7 rows selected ----------------------- #160 U�ycie wielu nazw kolumn w klauzuli GROUP BY nie powoduje konieczno�ci u�ycia tej samej liczby kolumn w cz�ci ORDER BY. Na listingu 7.31 pokazano zapytanie, kt�re sortuje wyniki tylko na podstawie g��wnego kryterium, a na listingu 7.32 tylko na podstawie drugiego kryterium grupowania. ----------------------- Listing 7.31. Zapytanie grupuj�ce z sortowaniem wed�ug g��wnego kryterium SELECT studio_id, director_id, COUNT(*) FROM Movies GROUP BY studio_id, director_id ORDER BY studio id STUDIO_ID DIRECTOR_ID COUNT(*) ----------------------- 1 1 3 3 1 1 2 2 1 4 2 1 3 9 2 2 10 1 4 10 1 7 rows selected ----------------------- ----------------------- Listing 7.32. Zapytanie grupuj�ce z sortowaniem wed�ug drugiego kryterium SELECT studio_id, director_id, COUNT(*) FROM Movies GROUP BY studio_id, director_id ORDER BY director id STUDIO_ID DIRECTOR_ID COUNT(*) ----------------------- 1 1 3 3 1 1 2 2 1 4 2 1 3 9 2 2 10 1 4 10 1 7 rows selected ----------------------- Klauzula GROUP BY stosowana ��cznie z WHERE Klauzul� WHERE mo�emy u�y� ��cznie z GROUP BY, aby ograniczy� ilo�� wierszy zanim b�d� dzielone na grupy i podgrupy. Na przyk�ad mo�emy wybra� tylko filmy przeznaczone do dystrybucji w okre�lonym czasie, albo filmy tylko z wybranego studia. U�ywaj�c instrukcji WHERE ��cznie z GROUP BY najpierw realizowane jest ograniczenie wynikaj�ce z kryteri�w w klauzuli WHERE. Nast�pnie wybrane rekordy s� grupowane i powstaje ostateczny wynik zapytania. Przyk�ad na listingu 7.33 dla ka�dego studia zlicza filmy, kt�re wesz�y na ekrany w latach 80-tych. #161 ----------------------- Listing 7.33. Przyk�ad zapytania z u�yciem klauzul GROUP BY i WHERE SELECT studio_id, COUNT(*} FROM Movies WHERE release_date BETWEEN '01-JAN-801 AND '31-DEC-891 GROUP BY studio id STUDIO_ID COUNT(*) ----------------------- 1 1 3 1 ----------------------- Filtrowanie wynik�w zapyta� z u�yciem HAVING J�zyk SQL dostarcza jeszcze jedn� metod� filtrowania wynik�w zapytania w po��czeniu z klauzul� GROUP BY. Klauzula WHERE filtruje wyniki zapytania zanim s� one grupowane, natomiast klauzula HAVING filtruje wyniki po wykonaniu grupowania. Wyra�enia zawarte w tej klauzuli wykonywane s� na ca�ych grupach, a nie na pojedynczych rekordach. Na przyk�ad, chc�c znale�� studia, kt�rych bud�et przekroczy� 60 min $, stosuj�c zapytanie z klauzul� WHERE nie otrzymamy poprawnej odpowiedzi. U�ywaj�c HAYING mo�emy policzy� bud�ety dla wszystkich studi�w i wybra� tylko te grupy, dla kt�rych suma jest wi�ksza ni� 60 min $. Pokazuje to listing 7.34. ----------------------- Listing 7.34. Filtrowanie grup z u�yciem HAVING SELECT studio_id, SUM(budget) FROM Movies GROUP BY studio_id HAYING SUM(budget) > 60 STUDIO_ID SUM(BUDGET) ----------------------- 1 85 3 65.1 3 87.3 ----------------------- Jak wida� funkcje agreguj�ce s� u�yte w dw�ch miejscach zapytania, w klauzuli SELECT oraz HAVING. Cz�sty b��d polega na u�yciu w klauzuli HAVING tylko nazwy kolumny, co automatycznie powoduje b��d. W tej cz�ci zapytania musi si� znajdowa� takie samo wyra�enie, jak na li�cie klauzuli SELECT. Nazwy kolumn, kt�re nie pojawiaj� si� na li�cie klauzuli SELECT, nie mog� by� w og�le u�yte w klauzuli GROUP BY. Klauzula HAVING pojawia si� przed ORDER BY, ale za cz�ci� GROUP BY. Poprawn� kolejno�� wszystkich klauzul pokazano na listingu 7.35. #162 ----------------------- Listing 7.35. Instrukcja z u�yciem wielu klauzul ��cznie SELECT studio_id, SUM(budget) FROM Movies GROUP BY studio_id HAVING SUM(budget) > 60 ORDER BY studio_id STUDIO_ID SUM(BUDGET) ----------------------- 1 85 3 65.1 4 87.3 ----------------------- Z�o�one wyra�enia w klauzuli HAVING Nie ma �adnych przeciwwskaza�, �eby u�ywa� z�o�onych wyra�e� w obr�bie klauzuli HAVING. Jedyne ograniczenie polega na tym, �e wszystkie wyra�enia w cz�ci HAVING musz� mie� sw�j odpowiednik na li�cie klauzuli SELECT. Z tego powodu zapytanie przedstawione na listingu 7.36 mo�e nie dzia�a� prawid�owo w niekt�rych bazach danych (chocia� dzia�a prawid�owo w bazie Oracle8). ----------------------- Listing 7.36. Zapytanie wykorzystuj�ce w cz�ci HA VING nazw� kolumny, kt�rej nie ma w cz�ci select_list zapytania SELECT studio_id, SUM(budget) FROM MoviesGROUP BY studio_id HAYING SUM(budget) > 60 AND MAX(release dat�) > '90-DEC-31' STUDIO_ID SUM(BUDGET) ----------------------- 1 85 3 65.1 4 87.3 ----------------------- HAVING i WHERE HAVING i WHERE mog� by� stosowane w jednym zapytaniu. W niekt�rych wypadkach ma sens filtrowanie wynik�w zapytania tymi dwoma metodami r�wnocze�nie. Za��my, �e chcemy znale�� filmy, kt�rych re�yserzy zarobili �rednio ponad 10 min $. Aby unikn�� wypaczenia wynik�w musimy pomin�� filmy, kt�re jeszcze nie wesz�y do dystrybucji i w zwi�zku z tym nie przynios�y jeszcze �adnego dochodu. Na listingu 7.37 pokazano rozwi�zanie tego zadania, wykorzystuj�c kombinacj� HTWING i WHERE. ----------------------- Listing 7.37. HA VING i WHERE w jednym zapytaniu SELECT director_id, AVG(gross) FROM Movies WHERE gross IS NOT NULL GROUP BY director_id HAYING AVG(gross) > 10 ORDER BY AYG(gross) DIRECTOR_ID AVG(GROSS) ----------------------- 2 17.65 10 30 1 30.7 ----------------------- #163 Jak wida� z listingu 7.37 kolejno�� element�w jest zgodna z tym, co pokazano wcze�niej: WHERE.... GROUP BY.....HAVING.....ORDER BY.....Wszystkie elementy zapytania w znacz�cy spos�b wp�ywaj� na jego ostateczny wynik. Gdyby nie odfiltrowano na pocz�tku (czyli w klauzuli WHERE) warto�ci null, to w cz�ci HAYING mog�oby to spowodowa� usuni�cie niekt�rych grup zamiast pojedynczych rekord�w. Z tego samego powodu nie dokonujemy filtrowania na podstawie funkcji agreguj�cej w klauzuli WHERE. W praktyce Transact SQL wspierany przez produkty Microsoft SQL Server oraz Sybase Adap-tive Server dostarcza dodatkowych �rodk�w, z kt�rymi mo�emy u�ywa� funkcji agreguj�cych. U�ywaj�c klauzuli COMPUTE BY mo�na wprowadzi� funkcje agreguj�ce do zwyk�ych zapyta� wybieraj�cych. Tworz�c raporty przy u�yciu j�zyka SQL (w przeciwie�stwie do metody, gdzie SQL s�u�y tylko do wybierania odpowiednich danych, a raport przygotowywany jest w innym j�zyku programowania), klauzula COMPUTE BY pomaga zaoszcz�dzi� wiele pracy programisty. Na przyk�ad listing 7.38 pokazuje zapytanie, kt�re zwraca tytu� filmu (title), przych�d (gross) i bud�et (budget) dla ka�dego filmu w bazie danych, a ponadto �redni przych�d i �redni bud�et wyliczany na podstawie wszystkich danych. ---------------------------- Listing 7.38. U�ycie klauzuli COMPUTE BY do stworzenia raportu SELECT raovie_title, budget, gross FROM Movies COMPUTE AVG(budget), AVG(gross) Movie_title budget gross Minera� House 20 30 Prince Kong 3 52 The Code Warrior 10 18 Bili Durhara 10 16 Codependence Day 15 30 The Linux Files 22 18 SQL Strikes Back 5 10 The Programmer 50 45 Hard Code 11 30 The Rear Windows; 50 18 avg avg ==== ==== -; 26.200000 26.700000 (11 row(s) affected) ---------------------------- Jak wida�, listing zawiera nie tylko warto�ci dla poszczeg�lnych wierszy, ale r�wnie� warto�ci zbiorcze wyliczone dla wszystkich film�w ��cznie. Gdyby nie zastosowanie klauzuli COMPUTE BY, podobny efekt mo�na by osi�gn�� tworz�c dwa zapytania. Jedno zapytanie wybiera�oby pojedyncze pozycje, a drugie wylicza�oby warto�ci �rednie. Mo�na r�wnie� po��czy� zastosowanie zapyta� wybieraj�cych z elementami COMPUTE BY i GROUP BY. U�ycie COMPUTE BY wymaga ponadto u�ycia klauzuli ORDER BY, aby pogrupowa� wiersze zwr�cone przez zapytanie. Dopiero wtedy mo�na zastosowa� funkcje agreguj�ce dla ka�dej grupy. Pokazuje to listing 7.39. #164 ---------------------------- Listing 7.39. U�ycie COMPUTE BY do zastosowania funkcji agreguj�cych dla grup SELECT movie_title, studio_id, budget, gross FROM Movies ORDER BY studio_id COMPUTE SUM(budget), SUM(gross) BY studio_id movie_title studio_id budget gross ------------------------------------------------------------- Minera� House 1 20 30 Codependence Day 1 15 30 The Rear Windows 1 50 18 ============ - - sum sum 85 78 Movie_title studio_id budget gross ------------------------------------------------------------- Prince Kong 2 52 52 The Code Warrior 2 18 18 The Linux Files 2 22 18 ============ sum sum - - 35 88 Bili Durhara 3 10 16 SQL Strikes Back 3 5 10 The Programmer 3 50 45 ======== - - sum sum 65 71 Movie_title studio_id budget gross ------------------------------------------------------------- Hard Code 4 77 30 sum sum ========== - - 77 30 (14 row(s) affected) ------------------------------ Klauzula ORDER BY jest wykorzystana do pogrupowania wierszy wzgl�dem warto�ci z kolumny studio_id, a funkcje agreguj�ce s� zastosowane do ka�dej grupy. Wyniki tego zapytania mo�na by uzyska� wykonuj�c dwa zapytania. Jednak a� 8 zapyta� by�oby potrzebnych, by uzyska� podobny uk�ad danych z pomini�ciem klauzuli COMPUTE BY.