SQL w praktyce — czy raport mówi prawdę?
Z gotowego projektu do odpowiedzi dla użytkownika
Ten wykład nie ma testu. Zaczynamy od działającej bazy ośrodka „Dąb” z poprzedniego spotkania. Nie tworzymy jeszcze raz tabel i nie zmieniamy przykładu na bibliotekę. Kierownik chce wiedzieć: kto przyjdzie, ile miejsc zostało i które szkolenia można jeszcze zaproponować uczestnikom.
SQL, czyli Structured Query Language (strukturalny język zapytań), pozwala wyrazić takie pytania. Jest deklaratywny: opisujemy oczekiwany wynik, a silnik dobiera sposób wykonania. Poprawna składnia nie gwarantuje jednak poprawnej odpowiedzi biznesowej. Zapytanie może wykonać się bez błędu i policzyć coś innego, niż potrzebuje użytkownik. To główny problem dzisiejszego pokazu.
Zapytania wykonujemy w PostgreSQL przez pgAdmin. Przed każdym wykonaniem przewidujemy wynik, a po nim porównujemy go z małym zbiorem kontrolnym. Samodzielne pisanie zapytań rozwiniemy na laboratoriach.
Stan, na którym sprawdzamy raport
| Edycja | Limit | Zapisy | Potwierdzone | Wolne miejsca |
|---|---|---|---|---|
| E1, kurs K1 | 2 | Ada i Bartek confirmed | 2 | 0 |
| E2, kurs K1 | 4 | Celina confirmed, Bartek cancelled | 1 | 3 |
| E3, kurs K2 | 4 | Brak zapisów | 0 | 4 |
To stan początkowy. Jeśli podczas poprzedniego pokazu zmieniono dane, trzeba najpierw je porównać z tą tabelą. Pokaz uruchamiamy w osobnej bazie prba4_pokaz, która ma ten sam model i wyjściowe dane co wykład 3. Dzięki temu przebieg poprzedniej demonstracji nie zmienia punktu startowego raportów. Inny wynik nie zawsze oznacza błędny SQL: może wynikać z innego stanu danych.
Pliki SQL do raportów — pobierz ZIP. Compose kursu przygotowuje dane z pliku 00_setup.sql automatycznie, gdy w bazie nie ma jeszcze tabel pokazu. W pgAdmin wybieramy prba4_pokaz, otwieramy Query Tool i zaczynamy od 01_roster.sql. Przy samodzielnym odtwarzaniu poza Compose plik 00 wykonuje się raz w nowej bazie.
1. Kto przyjdzie na E1?
W zapisie mamy identyfikator osoby, a nazwę w tabeli uczestników. Łączymy oba źródła po identyfikatorze. Nie dopasowujemy po imieniu, które nie musi być unikalne.
SELECT p.participant_id, p.name, z.edition_id
FROM enrollments AS z
JOIN participants AS p ON p.participant_id = z.participant_id
WHERE z.edition_id = 'E1' AND z.status = 'confirmed'
ORDER BY p.name, p.participant_id;Wynik zawiera Adę i Bartka. JOIN łączy pasujące wiersze, WHERE wybiera edycję i potwierdzone zapisy, SELECT określa kolumny odpowiedzi. ORDER BY zapewnia kolejność; bez niego nie zakładamy, że będzie zgodna z kolejnością dodawania. Drugi klucz sortowania rozstrzyga przypadek osób o tej samej nazwie.
p i z to aliasy, krótkie nazwy tabel używane w tym zapytaniu. Nie tworzą kopii danych ani nowych tabel. Najpierw zmieńcie warunek na E2 w myślach: czy Bartek powinien pojawić się na liście? Dlaczego?
2. Raport, który wygląda rozsądnie, ale liczy zły zbiór
Kierownik pyta o zajęte miejsca. Pierwsza propozycja:
SELECT edition_id, COUNT(*) AS registrations
FROM enrollments GROUP BY edition_id ORDER BY edition_id;Dostajemy E1 = 2 i E2 = 2. SQL jest poprawny, ale liczy wszystkie zapisy, łącznie z rezygnacjami. W efekcie raport zaniża dostępność E2. Ponadto E3 nie występuje, bo w tabeli zapisów nie ma żadnego jej wiersza.
Dodajemy warunek statusu:
SELECT edition_id, COUNT(*) AS confirmed
FROM enrollments WHERE status = 'confirmed'
GROUP BY edition_id ORDER BY edition_id;Teraz liczby są poprawne dla E1 i E2, lecz nadal brakuje E3. Samo filtrowanie istniejących zapisów nie stworzy wiersza dla edycji bez zapisów. Najpierw ustalamy, jakie obiekty mają wystąpić w raporcie, dopiero potem co liczyć.
3. Wszystkie edycje, również puste
Zaczynamy od editions. LEFT JOIN (lewe złączenie zewnętrzne) zachowuje wiersz tej tabeli także wtedy, gdy brak dopasowania w zapisach. Kolumny niedopasowanej strony mają wtedy NULL.
Łatwo jednak utracić tę właściwość przez niewłaściwe miejsce warunku:
SELECT e.edition_id, COUNT(z.participant_id) AS confirmed
FROM editions AS e
LEFT JOIN enrollments AS z ON z.edition_id = e.edition_id
WHERE z.status = 'confirmed'
GROUP BY e.edition_id ORDER BY e.edition_id;Dla pustej edycji status jest NULL. Warunek WHERE z.status = 'confirmed' nie daje TRUE, więc usuwa taki wiersz z wyniku. Dla raportu wszystkich edycji warunek statusu umieszczamy w ON: dopasowujemy tylko potwierdzone zapisy, ale zachowujemy każdą edycję.
Pozostaje druga pułapka:
SELECT e.edition_id, COUNT(*) AS confirmed
FROM editions AS e
LEFT JOIN enrollments AS z
ON z.edition_id = e.edition_id AND z.status = 'confirmed'
GROUP BY e.edition_id ORDER BY e.edition_id;E3 otrzymuje wynik 1. COUNT(*) liczy wiersze wyniku złączenia, a LEFT JOIN pozostawił jeden wiersz dla E3. COUNT(z.participant_id) liczy tylko niepuste identyfikatory dopasowanych zapisów, dzięki czemu zwróci zero.
NULL nie jest liczbą zero ani pustym tekstem. Oznacza brak wartości. Sprawdzamy go przez IS NULL, nie = NULL. Przy złączeniu może wynikać z braku pasującego wiersza, nawet jeśli źródłowa kolumna ma ograniczenie NOT NULL.
4. Pełny raport wolnych miejsc
SELECT e.edition_id, c.name AS course, e.seat_limit,
COUNT(z.participant_id) AS confirmed,
e.seat_limit - COUNT(z.participant_id) AS free_places
FROM editions AS e
JOIN courses AS c ON c.course_id = e.course_id
LEFT JOIN enrollments AS z
ON z.edition_id = e.edition_id AND z.status = 'confirmed'
GROUP BY e.edition_id, c.name, e.seat_limit
ORDER BY e.edition_id;Otrzymujemy wszystkie trzy edycje i wolne miejsca 0, 3, 4. GROUP BY wyznacza grupy, w których liczymy zapisy. Nie grupujemy tylko po nazwie kursu, bo jeden kurs może mieć wiele edycji z różnymi limitami i uczestnikami.
Wolne miejsca wyliczamy, zamiast dopisywać ręcznie kolejną kopię tej informacji. Po zmianie statusu ponowne wykonanie zapytania obliczy wynik ze stanu bazy. Nie zakrywamy wartości ujemnej przez zamianę na zero: gdy pojawi się przekroczenie limitu, raport powinien ujawnić problem.
5. Co możemy zaproponować nowemu uczestnikowi?
SELECT e.edition_id,
e.seat_limit - COUNT(z.participant_id) AS free_places
FROM editions AS e
LEFT JOIN enrollments AS z
ON z.edition_id = e.edition_id AND z.status = 'confirmed'
GROUP BY e.edition_id, e.seat_limit
HAVING e.seat_limit - COUNT(z.participant_id) > 0
ORDER BY free_places DESC, e.edition_id;Wynik to E3 z 4 wolnymi miejscami, potem E2 z 3. HAVING filtruje grupy na podstawie wyniku agregacji. WHERE filtruje wiersze przed grupowaniem.
Nie wystarczy WHERE seat_limit > 0: dodatni limit nie oznacza, że zostały wolne miejsca. E1 ma dodatni limit, ale wszystkie są zajęte.
Raport nie rezerwuje miejsca. Pokazuje stan podczas odczytu. Pomiędzy odczytem a późniejszym zapisem ktoś inny może zająć miejsce. Ochronę operacji modyfikującej dane trzeba zaprojektować oddzielnie.
6. Jedna rezygnacja i zmiana odpowiedzi
Przed aktualizacją sprawdzamy zakres tym samym warunkiem:
SELECT * FROM enrollments
WHERE participant_id = 'U2' AND edition_id = 'E1';UPDATE enrollments SET status = 'cancelled'
WHERE participant_id = 'U2' AND edition_id = 'E1';Zmienia się dokładnie U2–E1. Przy włączonym Auto commit w pgAdmin zmiana zostaje zatwierdzona po poprawnym wykonaniu polecenia. Następnie ponawiamy raport wolnych miejsc. Wynik to teraz 1, 3, 4, więc E1 również pojawi się wśród dostępnych edycji. Nazwisko uczestnika i inne zapisy nie zmieniły się. Nie usunęliśmy Bartka z ewidencji.
Bez warunku edycji aktualizacja dotyczyłaby wszystkich zapisów Bartka. Bez WHERE dotyczyłaby wszystkich zapisów w tabeli. Operacja bez WHERE nie jest błędem składni: może być zamierzona, ale jej zakres trzeba świadomie ustalić.
Prowadzący przywróci stan bazowy przed kolejną próbą, po sprawdzeniu, że nikt nie zajął zwolnionego miejsca. Sam odczyt i późniejszy UPDATE nie stanowią kompletnej ochrony przed równoczesnymi zgłoszeniami.
7. Liczymy osoby czy uczestnictwa?
Kierownik pyta: „Ile osób mamy na kursie K1?”. Jedna osoba może uczestniczyć w dwóch edycjach tego kursu. Trzeba więc doprecyzować, czy liczymy potwierdzone zapisy na edycje, czy różnych ludzi.
SELECT c.course_id, c.name,
COUNT(z.participant_id) AS confirmed_registrations,
COUNT(DISTINCT z.participant_id) AS distinct_participants
FROM courses AS c
LEFT JOIN editions AS e ON e.course_id = c.course_id
LEFT JOIN enrollments AS z
ON z.edition_id = e.edition_id AND z.status = 'confirmed'
GROUP BY c.course_id, c.name ORDER BY c.course_id;Na początku dla K1 obie liczby wynoszą 3. Prowadzący dodaje potwierdzony zapis Ady na E2, gdzie są wolne miejsca. Liczba zapisów rośnie do 4, ale liczba różnych uczestników pozostaje 3. COUNT(DISTINCT ...) usuwa powtórzenia identyfikatorów przed liczeniem. To nie jest poprawka na każdy problem z JOIN: wybór miary musi wynikać z pytania użytkownika.
Po próbie wracamy do danych bazowych. Podczas zajęć nie zmieniamy definicji „osoby” lub „zapisu” tylko po to, aby uzyskać oczekiwaną liczbę.
Dlaczego projekt nadal ma znaczenie?
Raporty sprawdzają projekt w praktyce. Rozdzielenie kursu i edycji pozwala pytać o oba poziomy. Status przy zapisie pozwala odróżnić rezygnację z jednego terminu od usunięcia uczestnika. Stabilne identyfikatory pozwalają liczyć różne osoby bez polegania na nazwach.
Na wykładzie 5 zbadamy, co dzieje się przy powielaniu tych samych informacji w wielu miejscach: anomalie zmian i normalizację. Na wykładzie 6 przejdziemy do indeksów i pomiaru planów zapytań. Na wykładzie 7 podzapytania i widoki pozwolą budować i ponownie wykorzystywać bardziej złożone odpowiedzi. Laboratoria służą samodzielnemu wykonaniu tych operacji, testowaniu i obronie rozwiązań.