Zadanie 4: Sklep komputerowy — funkcje agregujące i GROUP BY
ŚredniPo co się tego uczymy?
Właściciel sklepu rzadko pyta "pokaż mi wszystkie produkty" — częściej pyta "ile mamy produktów w każdej kategorii" albo "jaka jest średnia cena u każdego producenta". Do takich pytań służą funkcje agregujące.
Treść zadania
Scenariusz: Właściciel sklepu rzadko pyta "pokaż mi wszystkie produkty" — częściej pyta "ile mamy produktów w każdej kategorii" albo "jaka jest średnia cena u każdego producenta". Do takich pytań służą funkcje agregujące i GROUP BY.
Do wykonania — napisz zapytania odpowiadające na poniższe pytania:
1. Ile produktów jest w każdej kategorii?
2. Jaka jest średnia cena produktów każdego producenta?
3. Jaki jest najdroższy i jaki najtańszy produkt w całym sklepie?
4. Które kategorie mają więcej niż 3 produkty?
5. Jaka jest łączna wartość całego magazynu (cena × ilość_magazyn, zsumowane)?
6. Ilu mamy klientów zarejestrowanych z adresem e-mail w domenie gmail.com?
Jak zabrać się do zadania
1. Zacznij od zwykłego SELECT * FROM Produkty;, żeby przypomnieć sobie, jakie dane masz do dyspozycji.
2. Dodaj GROUP BY do kolumny, po której grupujesz, dopiero potem dopisz funkcję agregującą w SELECT.
3. Jeśli potrzebujesz warunku na WYNIK agregacji (punkt 4), pamiętaj że to HAVING, nie WHERE.
4. Dla jednej kategorii policz wynik "ręcznie" (na kartce) i porównaj z tym, co zwraca zapytanie — to najpewniejszy sposób sprawdzenia się.
Jak powinien wyglądać efekt
Zapytanie 1 powinno wyglądać mniej więcej tak:
+----------------+-------------------+ | nazwa | liczba_produktow | +----------------+-------------------+ | Procesory | 3 | | Karty graficzne| 3 | | Pamięci RAM | 3 | | Dyski SSD | 3 | | Monitory | 3 | +----------------+-------------------+ 5 rows in set (0.00 sec)
Dokładnie 5 wierszy (po jednym na kategorię) — jeśli widzisz więcej wierszy niż masz kategorii, prawdopodobnie brakuje GROUP BY albo grupujesz po niewłaściwej kolumnie.
Typowe problemy i jak sobie z nimi poradzić
Warunek na sumę w WHERE zamiast HAVING. Co się stało: WHERE COUNT(*) > 3 zgłasza błąd składni. Dlaczego: WHERE działa PRZED policzeniem agregacji, więc nie "widzi" jeszcze wyniku COUNT. Jak naprawić: przenieś warunek do HAVING COUNT(*) > 3, umieszczonego PO GROUP BY.
Kolumna w SELECT spoza GROUP BY. Co się stało: SELECT nazwa_produktu, kategoria, COUNT(*) ... GROUP BY kategoria zwraca nieprzewidywalne wartości albo błąd. Dlaczego: nazwa_produktu nie jest ani kolumną grupującą, ani objętą funkcją agregującą. Jak naprawić: usuń tę kolumnę z SELECT albo dodaj ją do GROUP BY, jeśli to ma sens.
Zła kolejność mnożenia i sumowania. Co się stało: zapytanie 5 zwraca nierealistycznie dużą albo małą wartość. Dlaczego: trzeba pomnożyć cenę przez ilość DLA KAŻDEGO wiersza osobno, dopiero potem zsumować — SUM(cena * ilosc_magazyn), a nie SUM(cena) * SUM(ilosc_magazyn).
Nawiązanie do egzaminu zawodowego
Realizuje efekt INF.03.4.4 w zakresie agregacji danych — zestawienia liczbowe tego typu są jednym z typowych wymagań w zadaniach praktycznych na egzaminie.
Kryteria sukcesu — sprawdź, czy wykonałeś zadanie poprawnie
Po wykonaniu zadania sprawdź, czy:
1. zapytanie 1 zwraca dokładnie tyle wierszy, ile masz kategorii (np. 5),
2. liczby z zapytania 1 sumują się do łącznej liczby produktów w bazie,
3. zapytanie 4 używa HAVING, a nie WHERE,
4. zapytanie 3 zwraca dokładnie jeden wiersz na MAX i jeden na MIN (albo oba naraz, jeśli połączyłeś zapytania),
5. wynik zapytania 5 to pojedyncza, sensowna liczba (nie 0, nie NULL).