#147 Rozdział 7. Przetwarzanie wyników zapytań Poznane dotychczas właciwoci instrukcji SELECT pozwalajš na wyszukiwanie w bazie potrzebnych danych. Przy jej pomocy znajdowalimy przychody z wybranego filmu lub wszystkich reżyserów zamieszkałych w podanym stanie. Wyrażenie SELECT posiada także bogate możliwoci tworzenia różnego rodzaju wyliczeń na danych, pozwalajšce spojrzeć na dane w sposób bardziej całociowy. Można wyliczać wartoci rednie, sumy dla wybranych kolumn lub dzielić dane na kategorie i wró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 wartoci unikalnych: operator DISTINCT Tabela Movies zawiera kilka filmów z każdego studia. Gdybymy 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 licie, ale niektóre z nich pojawiajš się wielokrotnie. Język SQL posiada operator DISTINCT, który umożliwia wyeliminowanie z wyniku wartoci powtarzajšcych się. Przykład zastosowania tego operatora znajduje się na listingu 7.2. ------------------------ Listing 7.2. Wybór -wartoci 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 licie wynikowej nie ma wartoci 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 wartoci W przypadku, gdy na licie pojawia się kilka nazw kolumn, wybierana jest każda niepowtarzalna kombinacja wartoci. 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 wartoci 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 wartoci. 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 wczeniejszej dyskusji o wartociach null, nie mogš one być uważane za wartoci sobie równe, ponieważ ich wartoć jest nieokrelona. Jednak operator DISTINCT łamie tę zasadę i w wyrażeniach z tym operatorem wszystkie wartoci null sš traktowane jak wartoci identyczne. #150 Wybór wartoci z kolumny gross przy pomocy operatora DISTINCT pokazuje podstawowe właciwoci tego operatora. ------------------------ Listing 7.6. Niepowtarzalne wartoci 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 wartoci pola, ale na grupie wartoci zwracanych przez zapytanie. Na przykład możemy w tabeli policzyć liczbę wierszy spełniajšcych okrelone kryteria lub możemy wyliczyć wartoć redniš dla wszystkich wartoci 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 wartoci 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 okrelenia 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 Jeli 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ększoci 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. Jeli chcemy znać iloć wierszy zwróconych przez zapytanie, najprociej użyć funkcji COUNT(*), jak pokazałem we wczeniejszych 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...
sliwak