INF.03 · Bazy danych i SQL

Sklep komputerowy, część 4: funkcje agregujące i GROUP BY

Właściciel sklepu rzadko prosi: „pokaż mi wszystkie produkty”. Częściej pyta: „ile mamy produktów w każdej kategorii”, „jaka jest średnia cena u każdego producenta” albo „ile warta jest zawartość magazynu”. Na takie pytania odpowiadają funkcje agregujące i grupowanie.

  • około 40 minut
  • COUNT · SUM · AVG · MIN · MAX
  • GROUP BY · HAVING

Po tej lekcji: odpowiesz jednym zapytaniem na pytania o liczbę, sumę i średnią w grupach, a warunki na wynik grupowania zapiszesz w HAVING.

1 · Sytuacja

Pytania właściciela

Pracujesz na bazie z danymi z części 2. Zacznij od zwykłego SELECT * FROM Produkty;, żeby przypomnieć sobie dane. Potem dopisz GROUP BY do kolumny, po której grupujesz, i dopiero wtedy funkcję agregującą w SELECT. Warunek na wynik agregacji to HAVING, a nie WHERE.

Dla jednej grupy policz wynik ręcznie na kartce i porównaj z zapytaniem. To najpewniejszy sposób, żeby się sprawdzić.

2 · Do wykonania

Zadania do wykonania

  1. Ile produktów jest w każdej kategorii? Pokaż nazwę kategorii i liczbę (alias liczba_produktow).Liczby w wyniku mają się sumować do 15.
    Oczekiwany wynik5 wierszy
  2. Jaka jest średnia cena produktów każdego producenta? Pokaż nazwę producenta i średnią zaokrągloną do dwóch miejsc.
    Oczekiwany wynik5 wierszy
  3. Jaki jest najdroższy i najtańszy produkt w sklepie? Pokaż dwie wartości: największą i najmniejszą cenę.
    Oczekiwany wynik1 wierszpierwszy wiersz: 5399.00 · 219.00
  4. Które kategorie mają średnią cenę produktu powyżej 1000 zł? Użyj HAVING.
    Oczekiwany wynik3 wiersze
  5. Jaka jest łączna wartość magazynu (cena razy ilość, zsumowane)?
    Oczekiwany wynik1 wierszpierwszy wiersz: 170063.00
  6. Ilu klientów ma adres e-mail w domenie gmail.com?
    Oczekiwany wynik1 wierszpierwszy wiersz: 4
  7. Ile produktów ma każdy producent? Posortuj od największej liczby (przy remisie po nazwie producenta).
    Oczekiwany wynik5 wierszypierwszy wiersz: Samsung · 6

3 · Sprawdź się

Czy Twoje wyniki się zgadzają?

To szybka kontrola, a nie ocena. Liczba wierszy jest pierwszym sygnałem, że warunek albo złączenie działa tak, jak trzeba.

Ta interakcja wymaga włączonego JavaScriptu.

4 · Zanim się pomylisz

Cztery pomyłki w agregacjach

Rozwiń każdą z nich.

Zwykła kolumna poza GROUP BY
Tak nie
SELECT k.nazwa, p.nazwa, COUNT(*)
FROM Produkty p JOIN Kategorie k ON p.id_kategorii = k.id_kategorii
GROUP BY k.nazwa;

Błąd only_full_group_by: kolumna p.nazwa nie opisuje całej grupy, bo w kategorii są trzy różne produkty.

Tak
SELECT k.nazwa, COUNT(*) AS liczba_produktow
FROM ... GROUP BY k.nazwa;

Zwykła kolumna w SELECT musi być w GROUP BY.

Funkcja agregująca w WHERE
Tak nie
SELECT k.nazwa FROM ...
WHERE AVG(p.cena) > 1000
GROUP BY k.nazwa;

Błąd Invalid use of group function. W chwili działania WHERE grup jeszcze nie ma.

Tak
GROUP BY k.nazwa
HAVING AVG(p.cena) > 1000;

Warunek na wynik agregacji piszesz w HAVING.

HAVING COUNT(*) > 3 przy trzech produktach w każdej kategorii
Tak nie
GROUP BY id_kategorii
HAVING COUNT(*) > 3;

Pusty wynik, bo w każdej kategorii jest dokładnie 3 produkty. To poprawny wynik, tylko inny niż się spodziewałeś.

Tak
HAVING COUNT(*) >= 3;

Sprawdź granicę: „więcej niż 3” a „co najmniej 3”.

COUNT(kolumna) zamiast COUNT(*)
Tak nie
SELECT COUNT(telefon) FROM Klienci;

Wynik pomija klientów bez telefonu (NULL). Gdy chcesz policzyć klientów, dostajesz za mało.

Tak
SELECT COUNT(*) FROM Klienci;

COUNT(*) liczy wiersze, a COUNT(kolumna) tylko wartości niepuste.

5 · Sprawdź sam siebie

Zanim oddasz pracę

Ta lista odpowiada temu, co sprawdza komisja egzaminacyjna: poprawną bazę, poprawne wyniki i dokumentację.

Podsumowanie

Co warto zapamiętać

  1. Funkcje agregujące liczą w grupach, a GROUP BY tworzy te grupy.
  2. WHERE odrzuca wiersze przed grupowaniem, HAVING odrzuca grupy po policzeniu.
  3. Sumy z grup powinny dawać wynik całkowity.
  4. COUNT(*) liczy wiersze, a COUNT(kolumna) pomija NULL.

Dalej na swoim koncie

Rozgrzewki, quiz i zadania są w biurku ucznia.

Teoria jest otwarta dla wszystkich. Żeby rozwiązywać zadania, zapisywać szkice i widzieć swój postęp, zaloguj się.

Zaloguj się →
Źródła i dalsza nauka