Grupowanie wierszy

W SQL istnieje możliwość grupowania wierszy. Przykładowo jeżeli chcemy przedstawić zbiorcze statystyki zwierzaków rozdzielając je podług gatunku, musimy użyć konstrukcji GROUP BY.

Grupujemy wybierając kolumny do grupowania (zwykle jedną), w naszym przypadku Gatunek. Grupować można także po kilku kolumnach. Frazę GROUP BY umieszcza się po warunkach wybierających wiersze.

Po grupowaniu wierszy, we frazie SELECT nie możemy odnosić się do kolumn po których nie grupujemy, musimy zamiast tego używać funkcji agregujących. Jest to dość oczywiste, przykładowo kiedy pogrupujemy tabelę reprezentującą zwierzaki po gatunku, to odwoływanie się do imienia nie ma sensu - imiona dla danego gatunku mogą być przecież różne.

SELECT gatunek, COUNT(*) AS ile FROM Zwierzaki
GROUP BY gatunek ORDER BY gatunek;

SELECT gatunek, AVG(waga), MAX(wiek) FROM Zwierzaki
WHERE gatunek IS NOT NULL
GROUP BY gatunek ORDER BY gatunek;

COUNT

Zwraca liczbę wierszy; parametrem może być * lub nazwa dowolnej kolumny: w pierwszym przypadku liczy się wiersze, w drugim liczbę wierszy nie zawierających NULL w podanej kolumnie. Można także dowiedzieć się, ile jest różnych wartości w kolumnie, poprzedzając jej nazwę słowem DISTINCT.

MIN/MAX

Zwraca wartość minimalną/maksymalną z danej grupy; pomija się wartości NULL.

SUM/AVG

Zwraca sumę/średnią z wartości w kolumnie; tak jak powyżej, pomijane się wartości NULL.

Funkcji tych można używać także kiedy tabela nie jest grupowana, odnosić się będą wówczas do wszystkich wybranych wierszy.

Warunki wybierające wiersze, które grupujemy, możemy umieścić we frazie WHERE. Natomiast warunki dotyczące grup (na przykład ,,wybrać grupy które mają więcej niż 3 elementy'') umieszczamy we frazie HAVING.

Ponieważ wiersze z wartościami NULL utworzą osobną grupę, należy się ich wstępnie pozbyć we frazie WHERE

SELECT COUNT(*), gatunek, MIN(waga), MAX(waga) FROM Zwierzaki
WHERE gatunek IS NOT NULL
GROUP BY gatunek
HAVING COUNT(*) > 3 ORDER BY gatunek;

1. Fraza FROM ustala tabele, na których pracujemy
2. Sprawdza się dla ich wierszy warunek we frazie WHERE
3. Wybrane wiersze grupuje się według klauzuli GROUP BY
4. Odrzuca się grupy, które nie pasują do warunku HAVING
5. Dla każdej grupy wylicza wartość wyrażeń podanych w SELECT
6. Sortuje się wynik według frazy ORDER BY.

Zadanie 1.

Korzystając tylko z danych w tabeli Zwierzaki:

1. Dla każdego gatunku podaj liczbę Zwierzaków i średnią wagę.
2. Dla każdego gatunku podaj, ile Zwierzaków waży więcej niż 20.
3. Znajdź maksymalną wagę dla gatunków, z których mamy co najmniej 2 zwierzaki ważące więcej niż 20.
4. Znajdź wszystkie gatunki, w których rozpiętość wagi jest większa niż 20.
5. Ile mamy zwierzaków, których gatunku nie znamy?

Kilka sztuczek

1. Grupy z identycznymi wartościami

Czasem grupując wiersze po pewnej kolumnie wiemy, że w danej grupie wszystkie wartości w niektórych innych kolumnach też są sobie równe. Na przykład jeżeli grupujemy zadania studentów po indeksie, to imię i nazwisko też będą ustalone w ramach grupy. Jak umieścić takie kolumny w SELECT?

Po prostu dołączyć je do GROUP BY.

SELECT indeks, imie, nazwisko, AVG(ocena) FROM Klasowki 
GROUP BY indeks, imie, nazwisko;

Można również użyć funkcji agregujacej MIN lub MAX (działają na wszystkich typach danych), ale wyglada to nienaturalnie.

SELECT indeks, MIN(imie), MIN(nazwisko), AVG(ocena) FROM Klasowki 
GROUP BY indeks;

2. Wiersz z największą/najmniejszą wartością

Inną częstą sytuacją jest potrzeba wypisania danych z wiersza, który zawiera największą/najmniejszą wartość w jakiejś kolumnie. Na przykład szukamy najcięższego zwierzaka. Standardowe rozwiązanie to

SELECT * FROM Zwierzaki
WHERE waga = (SELECT MAX(waga) FROM Zwierzaki);

Rozwiązanie trikowe to użycie złączenia zewnętrznego tabeli z nią samą.

SELECT A.* FROM Zwierzaki A LEFT JOIN Zwierzaki B ON A.waga < B.waga
WHERE B.id IS NULL;

Jeszcze inny sposób to

SELECT * FROM Zwierzaki Z1
WHERE NOT EXISTS (SELECT * FROM Zwierzaki Z2 where Z2.waga > Z1.waga);

3. Ranking

Wiemy jak wypisać najcięższego zwierzaka, a jak wypisać siedem albo siedemnaście najcięższych?

Umiemy posortować wybierane wiersze w odpowiedniej kolejności. Pierwszy pomysł to użyć kontrukcji ORDER BY z niestandardowym dodatkiem LIMIT, podającym liczbę wybieranych wierszy:

SELECT imie FROM Zwierzaki
WHERE waga IS NOT NULL
ORDER BY waga DESC LIMIT 7;  -- trochę źle!!

Ale co się stanie, jeśli niektóre zwierzaki (na przykład siódmy i ósmy) będa ważyły tyle samo? SQL niedeterministycznie przypisze któremuś z nich niższą pozycję!

Trzeba znaleźć wagę siódmego zwierzaka i wybrać wszystkie te zwierzaki, które ważą co najmniej tyle.

SELECT imie FROM Zwierzaki
WHERE waga IN (SELECT waga FROM Zwierzaki
               WHERE waga IS NOT NULL
               ORDER BY waga DESC LIMIT 7);

Można też to zrobić tak

SELECT imie FROM Zwierzaki z1
WHERE 7 > (SELECT COUNT(*) FROM Zwierzaki z2
           WHERE z2.waga > z1.waga);

Odpowiedzi

1. select gatunek, count(*), avg(waga) from Zwierzaki
   group by gatunek;
   

2. select gatunek, count(*) from Zwierzaki
   where waga > 20 group by gatunek;
   

3. select gatunek, max(waga) from Zwierzaki
   where waga > 20
   group by gatunek having count(*) > 1;
   

4. select gatunek from Zwierzaki
   group by gatunek having max(waga) - min(waga) > 20;
   

5. select count(*) as "Nieznane" from Zwierzaki
   where gatunek is null;