BAZY DANYCH · INF.03

Filtrowanie, agregacje i grupowanie

Biblioteka codziennie zadaje pytania: kto zalega, ile wynoszą kary, których książek brakuje. Warunki WHERE, funkcje COUNT, SUM i AVG oraz GROUP BY pozwolą zamienić te pytania w konkretne raporty.

Filtrowanie wierszy: WHERE, LIKE, IN, BETWEEN i NULL

W szkolnej bibliotece jest ponad dwadzieścia tytułów, a każdy z nich ma inną cenę, rok wydania i kategorię. Gdy bibliotekarz szuka książek na wystawę, nie chce przeglądać całej tabeli. Potrzebuje odpowiedzi na konkretne pytanie: które książki są dostępne, kosztują mniej niż 50 zł i zostały wydane po 1945 roku? Do wybierania wierszy służy klauzula WHERE.

WHERE działa jak sito. Baza bierze kolejno każdy wiersz tabeli i sprawdza warunek. Jeśli wynik jest prawdziwy, wiersz trafia do wyniku; jeśli fałszywy, zostaje odrzucony. W warunku można używać porównań =, <>, <, <=, > i >= oraz łączyć je operatorami AND, OR i NOT. Nawiasy pozwalają ustalić kolejność działań, podobnie jak w matematyce.

SELECT tytul, cena, stan
FROM ksiazki
WHERE cena < 50 AND dostepna = 1
ORDER BY cena;

To zapytanie zwraca tylko dostępne książki tańsze niż 50 zł. Kolumna dostepna przechowuje wartość 1 albo 0, więc warunek dostepna = 1 jest równoznaczny z „książka jest dostępna”. Zwróć uwagę, że WHERE działa przed sortowaniem: najpierw wybieramy wiersze, potem porządkujemy wynik przez ORDER BY.

LIKE służy do porównywania tekstów z wzorcem. Znak % zastępuje dowolny ciąg znaków, także pusty, a _ dokładnie jeden znak. Wzorzec 'Wiedźmin%' pasuje do tytułów rozpoczętych od słowa „Wiedźmin”, a '%historia%' do tytułów zawierających to słowo w dowolnym miejscu. W naszej bazie porównania tekstów są niewrażliwe na wielkość liter, więc 'wiedźmin%' znajdzie to samo co 'Wiedźmin%'.

SELECT tytul, rok_wydania
FROM ksiazki
WHERE tytul LIKE 'Wiedźmin%'
ORDER BY rok_wydania;

IN pozwala sprawdzić, czy wartość należy do krótkiej listy. Zamiast pisać kategoria_id = 2 OR kategoria_id = 6, wystarczy kategoria_id IN (2, 6). BETWEEN sprawdza przedział domknięty: rok_wydania BETWEEN 1945 AND 2000 obejmuje także rok 1945 i rok 2000. Oba operatory można poprzedzić słowem NOT, otrzymując NOT IN i NOT BETWEEN.

SELECT tytul, kategoria_id, rok_wydania, cena
FROM ksiazki
WHERE kategoria_id IN (2, 6)
  AND rok_wydania BETWEEN 1945 AND 2000
  AND dostepna = 1
ORDER BY rok_wydania, tytul;

NULL to brak wartości, a nie zero ani pusty napis. Dlatego email = NULL nie znajdzie uczniów bez adresu e-mail: porównanie z brakiem wartości daje nieznany wynik, a WHERE przepuszcza tylko wiersze, dla których warunek jest prawdziwy. Poprawna forma to email IS NULL albo email IS NOT NULL. Ta sama zasada dotyczy kolumn telefon, data_zwrotu i uwagi.

SELECT imie, nazwisko, klasa_id, telefon
FROM uczniowie
WHERE email IS NULL AND aktywny = 1
ORDER BY klasa_id, nazwisko;

Przykłady z życia

1. Szukanie książki w katalogu. Czytelnik pamięta tylko fragment tytułu i wpisuje „historia”. Bibliotekarz używa LIKE '%historia%', a nie zna dokładnego tytułu. Gdyby wpisał pełny tytuł, musiałby pamiętać dokładny zapis i znaki interpunkcyjne.

2. Lista osób do kontaktu. Wychowawca chce zadzwonić do uczniów, którzy nie podali e-maila. Warunek email IS NULL zwraca właśnie takie osoby, a telefon IS NOT NULL pozwala pominąć tych, do których nie ma żadnego kontaktu.

3. Wybór stypendystów. Szkoła szuka uczniów urodzonych między 2009 a 2010 rokiem, którzy są aktywni. Przedział data_urodzenia BETWEEN '2009-01-01' AND '2010-12-31' jest czytelniejszy niż dwa osobne warunki i trudniej w nim o pomyłkę przy granicach.

Matematyka: operator BETWEEN odpowiada przedziałowi domkniętemu, który zapisujesz jako $\langle a, b \rangle$. W matematyce zwracasz uwagę, czy końce należą do przedziału; w SQL BETWEEN zawsze je włącza. To dlatego BETWEEN 1945 AND 2000 obejmuje oba lata, a nie tylko lata pomiędzy nimi.

Agregacje: COUNT, SUM, AVG, MIN i MAX

Pojedynczy wiersz odpowiada na pytanie o jedną książkę. Często potrzebujemy jednak odpowiedzi o cały zbiór: ile książek ma biblioteka, ile egzemplarzy jest w magazynie, jaka jest średnia cena albo najwyższa kara. Do takich podsumowań służą funkcje agregujące, które zamieniają wiele wierszy w jedną wartość.

COUNT liczy wiersze. COUNT(*) liczy wszystkie wiersze w tabeli lub grupie, także te, w których niektóre kolumny są puste. COUNT(kolumna) liczy tylko wiersze, w których ta kolumna ma wartość inną niż NULL. W tabeli uczniowie jest 24 uczniów, ale tylko 14 ma adres e-mail — COUNT(*) zwróci 24, a COUNT(email) zwróci 14. To jedna z najczęstszych pułapek przy raportach.

SELECT COUNT(*) AS wszyscy,
       COUNT(email) AS z_emailem,
       COUNT(telefon) AS z_telefonem
FROM uczniowie;

SUM dodaje wartości liczbowe, AVG liczy średnią, a MIN i MAX znajdują najmniejszą i największą wartość. Funkcje agregujące pomijają NULL: AVG(kara) sumuje tylko istniejące kary i dzieli przez liczbę niepustych wartości. MIN i MAX działają także na datach i tekstach, więc można zapytać o pierwszą i ostatnią datę wypożyczenia.

SELECT COUNT(*) AS liczba_ksiazek,
       SUM(stan) AS egzemplarze,
       ROUND(AVG(cena), 2) AS srednia_cena,
       MIN(cena) AS najtaniej,
       MAX(cena) AS najdrozej
FROM ksiazki;

Wynikiem jest dokładnie jeden wiersz, nawet jeśli tabela ma tysiące rekordów. Każda funkcja może mieć alias, dzięki czemu nagłówki raportu są czytelne. Warto liczyć tylko to, czego naprawdę potrzebujemy: SUM(stan) odpowiada na pytanie o liczbę egzemplarzy, a COUNT(*) o liczbę tytułów w katalogu. To nie to samo, bo jedna książka może mieć kilka egzemplarzy.

Agregacje można łączyć z WHERE. Wtedy najpierw wybieramy podzbiór wierszy, a potem liczymy podsumowanie. Zapytanie o kary tylko dla zwróconych książek pominie wypożyczenia w trakcie i przeterminowane. W raporcie finansowym trzeba jasno powiedzieć, który zbiór jest podstawą obliczeń, inaczej średnia może dotyczyć innej grupy, niż sugeruje nagłówek.

SELECT COUNT(*) AS zwroty,
       SUM(kara) AS suma_kar,
       ROUND(AVG(kara), 2) AS srednia_kara
FROM wypozyczenia
WHERE status = 'zwrocone';

Przykłady z życia

1. Średnia ocen w dzienniku. Wychowawca liczy średnią z pięciu ocen, a nie z sześciu, jeśli jedna ocena jest brakująca. To dokładnie zachowanie AVG: NULL nie jest oceną zero, tylko brakiem oceny. Gdyby zamienić NULL na 0, średnia sztucznie by spadła.

2. Budżet domowy. SUM odpowiada na pytanie, ile łącznie wydano w miesiącu. COUNT mówi, ile było paragonów, a AVG — ile wynosił przeciętny paragon. Trzy różne liczby opisują ten sam zbiór wydatków i wszystkie mogą być potrzebne w raporcie.

3. Statystyka sportowa. Trener liczy sumę punktów drużyny, średnią punktów na zawodnika i najlepszy wynik. MAX wskaże najlepszego strzelca, ale nie powie, ilu zawodników w ogóle grało — do tego potrzebny jest COUNT.

Informatyka: w aplikacji mobilnej licznik kroków pokazuje sumę z dnia, a ekran tygodnia — średnią dzienną. Oba widoki korzystają z tych samych danych, ale inaczej je agregują. Projektant musi wiedzieć, czy brak pomiaru ma być pominięty, czy potraktowany jako zero, bo od tego zależą wyniki.

GROUP BY i HAVING — raporty biblioteki

Poprzednia sekcja dawała jedno podsumowanie dla całej tabeli. Dyrekcja chce jednak wiedzieć, ile książek jest w każdej kategorii, jaka jest średnia cena w fantastyce i które kategorie mają co najmniej trzy tytuły. Do dzielenia wierszy na grupy służy klauzula GROUP BY, a do filtrowania gotowych grup — HAVING.

GROUP BY zbiera wiersze o tej samej wartości wskazanej kolumny. Wszystkie książki z kategoria_id = 2 tworzą jedną grupę, książki z kategoria_id = 1 drugą i tak dalej. Dla każdej grupy baza liczy agregaty osobno. W wyniku pojawia się jedna linia na grupę, a nie jedna linia na książkę. Dlatego w SELECT wymieniamy kolumnę grupującą oraz funkcje agregujące, np. COUNT(*) i AVG(cena).

SELECT kategoria_id,
       COUNT(*) AS liczba_ksiazek,
       ROUND(AVG(cena), 2) AS srednia_cena
FROM ksiazki
GROUP BY kategoria_id
ORDER BY liczba_ksiazek DESC, kategoria_id;

To zapytanie zwraca osiem wierszy — po jednym na każdą kategorię, która ma przynajmniej jedną książkę. Kategoria bez książek w ogóle nie pojawi się w wyniku, bo nie ma z czego utworzyć grupy. Gdybyśmy chcieli ją zobaczyć z zerem, potrzebowalibyśmy LEFT JOIN z tabelą kategorii, co poznasz w module 05.

HAVING filtruje grupy już po obliczeniu agregatów. Warunek HAVING COUNT(*) >= 3 zostawia tylko kategorie z co najmniej trzema książkami. Nie można go zastąpić warunkiem WHERE COUNT(*) >= 3, ponieważ WHERE działa przed grupowaniem, gdy liczba książek w grupie jeszcze nie istnieje. To najważniejsza różnica między tymi klauzulami.

SELECT kategoria_id,
       COUNT(*) AS liczba_ksiazek,
       ROUND(AVG(cena), 2) AS srednia_cena
FROM ksiazki
GROUP BY kategoria_id
HAVING COUNT(*) >= 3
ORDER BY liczba_ksiazek DESC, srednia_cena DESC;

Kolejność działań jest stała: FROM, WHERE, GROUP BY, agregaty, HAVING, SELECT, ORDER BY i LIMIT. Dzięki temu w HAVING można używać aliasów z SELECT, choć bezpieczniej jest powtórzyć funkcję agregującą. W MySQL aliasy w HAVING działają, ale w innych bazach mogą być niedostępne — powtórzenie COUNT(*) jest bardziej przenośne.

Grupować można po kilku kolumnach. Raport wypożyczeń według statusu i klasy powstaje przez GROUP BY klasa, status. Każda para tworzy osobną grupę, więc wynik przypomina tabelę krzyżową. W module 06 wykorzystamy ten mechanizm do raportów przekrojowych, a teraz wystarczy zapamiętać, że liczba grup rośnie wraz z liczbą kombinacji wartości.

Przykłady z życia

1. Raport ocen według przedmiotu. Wychowawca chce średnią ocen z każdego przedmiotu, a nie średnią wszystkich ocen razem. Grupuje oceny po przedmiocie i liczy AVG(ocena). Przedmioty z małą liczbą ocen może odfiltrować przez HAVING COUNT(*) >= 3, żeby średnia nie opierała się na jednej ocenie.

2. Sprzedaż w sklepie. Właściciel grupuje paragony po dniu i liczy sumę sprzedaży. WHERE data BETWEEN ... ogranicza okres, a HAVING SUM(kwota) > 1000 zostawia tylko dni z wysokim obrotem. To dwa różne filtry na dwóch różnych poziomach danych.

3. Tabela ligowa. Trener grupuje mecze po drużynie, sumuje punkty i liczy średnią zdobytych bramek. HAVING COUNT(*) >= 5 usuwa drużyny, które rozegrały zbyt mało spotkań, by porównanie było uczciwe.

Geografia: opady można grupować po regionie i liczyć średnią roczną. Regiony z jedną stacją pomiarową mogą być odfiltrowane przez HAVING COUNT(*) >= 3, bo średnia z jednego pomiaru nie opisuje całego obszaru. Ta sama logika chroni raport biblioteki przed wnioskami z kategorii, w której jest tylko jedna książka.

Ćwiczenia SQL

Zadania do samodzielnego wykonania w edytorze SQL.

• 1Książki na wystawęWybierz dostępne książki z dwóch kategorii wydane w latach 1945–2000 i tańsze niż 60 zł.Zadanie i podpowiedzi →
• 2Kontakt do uczniów z 1A i 1BZnajdź aktywnych uczniów z klas 1A i 1B, którzy mają e-mail w domenie example.com i podany telefon.Zadanie i podpowiedzi →
• 3Podsumowanie roku w bibliotecePolicz liczbę wypożyczeń, sumę i średnią kar oraz zakres dat jednym zapytaniem.Zadanie i podpowiedzi →
• 4Kategorie pod lupąZrób raport kategorii z co najmniej trzema książkami: liczba tytułów, średnia cena, najdroższa książka i liczba egzemplarzy.Zadanie i podpowiedzi →