INF.03 · Bazy danych i SQL

GROUP BY: zamień tysiąc wierszy w jedno podsumowanie

Aplikacja szkolna nie wysyła dyrektorowi tysiąca ocen do ręcznego liczenia. Baza grupuje oceny według klasy lub przedmiotu i zwraca liczbę ocen, średnią, minimum oraz maksimum. Tak samo sklep liczy sprzedaż według kategorii. Do tego służą funkcje agregujące i GROUP BY.

około 35 minut
COUNT · SUM · AVG · MIN · MAX
bazy: sklep_muzyczny, biblioteka, apteka

Po tej lekcji: policzysz podsumowanie dla całej tabeli i dla grup, odróżnisz WHERE od HAVING i rozpoznasz błędy w zapytaniu z grupowaniem.

1 · Pięć funkcji

Jedna liczba z wielu wierszy

Funkcja agregująca bierze wiele wierszy i zwraca jedną wartość. Najważniejsza różnica: COUNT(*) liczy wszystkie wiersze, a COUNT(kolumna) pomija wartości NULL.

Funkcja	Wynik	Uwaga
`COUNT(*)`	liczba wszystkich wierszy	liczy także wiersze z NULL
`COUNT(email)`	liczba niepustych e-maili	pomija NULL
`SUM(ilosc)`	suma sztuk	dla danych liczbowych
`AVG(cena)`	średnia cena	NULL jest pomijany
`MIN`, `MAX`	najmniejsza i największa wartość	działa też dla dat i tekstu

W bazie sklep_muzyczny jest 8 klientów, ale tylko 6 z nich ma e-mail: COUNT(*) da 8, a COUNT(email) da 6.

Laboratorium · ćwiczenie w przeglądarce, do którego odsyłają rozgrzewki na końcu lekcji

2 · Teraz Ty

WHERE, GROUP BY i HAVING krok po kroku

To dziesięć produktów ze sklepu muzycznego w czterech kategoriach. Wybierz funkcję, włącz grupowanie, a potem dodaj filtr WHERE (działa na pojedyncze wiersze) i HAVING (działa na gotowe grupy). Po lewej widzisz wiersze źródłowe, po prawej powstające grupy.

Kolejność jest zawsze taka: najpierw WHERE odrzuca wiersze, potem GROUP BY tworzy grupy, a na końcu HAVING odrzuca całe grupy. Dlatego wyniku funkcji agregującej nie wolno filtrować w WHERE.

3 · Przykład z wynikiem

Ile produktów i jaka średnia cena w każdej kategorii?

W tabeli Produkty nie ma tekstowej kolumny z kategorią, tylko id_kategorii. Nazwę kategorii pobierasz więc przez JOIN z poprzedniej lekcji, a dopiero potem grupujesz.

raport.sqlsql

SELECT k.nazwa_kategorii,
       COUNT(*) AS liczba_produktow,
       ROUND(AVG(p.cena), 2) AS srednia_cena
FROM Produkty AS p
JOIN Kategorie AS k ON p.id_kategorii = k.id_kategorii
GROUP BY k.nazwa_kategorii
ORDER BY k.nazwa_kategorii;

COUNT(*) i AVG(p.cena) liczą się w każdej grupie osobno, czyli dla każdej kategorii.
AS nadaje wynikowi czytelny nagłówek. Bez aliasu nagłówek brzmiałby COUNT(*).
Kolumna w SELECT powinna być albo funkcją agregującą, albo kolumną z GROUP BY. Tak wynik jest jednoznaczny.

nazwa_kategorii	liczba_produktow	srednia_cena
Akcesoria	3	62.50
Instrumenty	3	1466.16
Ksiazki i nuty	2	45.95
Naglosnienie	2	749.50

Wynik: cztery grupy, czyli cztery wiersze.

4 · Zanim się pomylisz

Cztery pomyłki w grupowaniu

Komunikaty pochodzą z prawdziwej bazy z włączonym trybem ONLY_FULL_GROUP_BY.

Kolumna spoza GROUP BY

Tak nie

SELECT miasto, imie, COUNT(*)
FROM Klienci
GROUP BY miasto;

Błąd: Expression #2 of SELECT list is not in GROUP BY clause ... incompatible with sql_mode=only_full_group_by. W grupie „Olsztyn” są dwa różne imiona i baza nie wie, które pokazać.

Tak

SELECT miasto, COUNT(*) AS liczba
FROM Klienci
GROUP BY miasto;

Każda zwykła kolumna w SELECT musi opisywać całą grupę.

Funkcja agregująca w WHERE

Tak nie

SELECT miasto
FROM Klienci
WHERE COUNT(*) > 1
GROUP BY miasto;

Błąd: Invalid use of group function. W chwili działania WHERE grupy jeszcze nie istnieją.

Tak

SELECT miasto
FROM Klienci
GROUP BY miasto
HAVING COUNT(*) > 1;

Wynik funkcji agregującej filtrujesz w HAVING.

COUNT(kolumna) zamiast COUNT(*)

Tak nie

SELECT COUNT(email) FROM Klienci;

Wynik: 6, choć klientów jest 8. COUNT(email) pomija dwóch klientów bez adresu.

Tak

SELECT COUNT(*) FROM Klienci;

Chcesz policzyć wiersze, więc użyj COUNT(*). COUNT(kolumna) liczy tylko wartości niepuste.

Brak aliasu wyniku

Tak nie

SELECT miasto, COUNT(*)
FROM Klienci
GROUP BY miasto;

Wynik jest poprawny, ale nagłówek drugiej kolumny to COUNT(*). W raporcie dla odbiorcy to nieczytelne.

Tak

SELECT miasto, COUNT(*) AS liczba_klientow
FROM Klienci
GROUP BY miasto;

Alias AS nadaje kolumnie czytelną nazwę.

5 · Sprawdź sam siebie

Zanim oddasz raport z agregacją

Na egzaminie raport musi mieć poprawne grupy, funkcje, aliasy i filtr HAVING. Najpierw sprawdź rekordy źródłowe, potem dodaj agregację.

Przed grupowaniem obejrzałem rekordy źródłowe (czy po JOIN jest tyle wierszy, ile się spodziewam).
Każda zwykła kolumna w SELECT jest w GROUP BY.
Filtr na pojedyncze wiersze jest w WHERE, a filtr na wynik funkcji w HAVING.
Kolumny wynikowe mają czytelne aliasy.
Liczba grup zgadza się z oczekiwaną.

Podsumowanie

Co warto zapamiętać

Funkcja agregująca zwraca jedną wartość z wielu wierszy: COUNT, SUM, AVG, MIN, MAX.
COUNT(*) liczy wszystko, a COUNT(kolumna) pomija NULL.
GROUP BY tworzy jedną grupę (i jeden wiersz wyniku) dla każdej wartości.
WHERE odrzuca wiersze przed grupowaniem, a HAVING odrzuca grupy po policzeniu.
Kolumna w SELECT jest funkcją agregującą albo jest w GROUP BY.

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

Dokumentacja MariaDB: polecenia SQL