:~$ newsletter --subscribe
cat ~/artykuly/bazy-danych/funkcje-grupujace-w-sql-cz-i.md

Grupowanie i agregacja danych w SQL: funkcje, GROUP BY i HAVING

Funkcje grupujące to bardzo ważny element w różnego rodzaju rozbudowanych kwerendach pobierających dane z bazy. Dzięki nim możemy wykonywać różne operacje na zbiorach (wierszach) otrzymując tym samym dokładnie spersonalizowane na nasze potrzeby dane.

Funkcje grupujące

Grupowanie danych w SQL polega na łączeniu ze sobą kilku wierszy w jeden. Jednym z najprostszych przykładów na grupowanie danych jest właśnie zgrupowanie całej tabeli czyli połączenie wszystkich jej wierszy w jeden.

W SQL mamy dostępną całą gamę funkcji grupujących są to między innymi:

  • avg(),
  • count(),
  • sum(),
  • min(),
  • max(),
  • stdev(),
  • stdevp().

Na osobny temat zasługuje klauzula GROUP BY, którą opisałem w dalszej części tego artykułu.

AVG()

Funkcja avg() zwraca średnią wartość liczb grupy bądź tabeli z danej kolumny podanej jako argument.

Przykład:

sql
SELECT AVG(wiek) FROM tab1;

Uwaga: Funkcja ta przyjmuje tylko liczby, bądź kolumny z „liczbowym” typem danych.

COUNT()

Funkcja count() jak wskazuje jej nazwa zwraca liczbę całkowitą przekazanych w postaci argumentu wartości. Funkcję tę najczęściej wykorzystuje się do sprawdzania liczby wierszy w danej grupie.

Przykładowe zapytanie zliczające liczbę wierszy w tabeli tab1:

sql
SELECT COUNT(*) FROM tab1;

lub:

sql
SELECT COUNT(kolID) FROM tab1;

Można również wyeliminować zliczanie powtarzających się danych za pośrednictwem słowa kluczowego DISTINCT:

sql
SELECT COUNT(DISTINCT id) FROM tab1;

Oczywiście należy pamiętać, że symbolu „*” nie można poprzedzić słowem kluczowym DISTINCT gdyż funkcja COUNT() wywołana dla argumentu „*” zwraca liczbę wszystkich wierszy grupy bądź tabeli również z tymi pustymi i powtórzonymi.

SUM()

Funkcja sum() zwraca sumę podanych wartości:

sql
SELECT SUM(klienci) FROM tab1;

MIN()

Funkcja min() jak sama nazwa wskazuje zwraca najmniejszą wartość. Jako argument może przyjmować dane typu „liczbowego”, daty i czasu, a nawet tekstowe. Ważne jest to, że ciąg znaków porównywany jest alfabetycznie.

Przykład:

sql
SELECT MIN(wiek) FROM tab1;

MAX()

Funkcja max() jak sama nazwa wskazuje zwraca największą wartość. Jako argument może przyjmować dane typu „liczbowego”, daty i czasu, a nawet tekstowe. Ważne jest to, że ciąg znaków porównywany jest alfabetycznie.

Przykład:

sql
SELECT MAX(wiek) FROM tab1;

STDEV()

stdev() zwraca odchylenie standardowe podanych jako argument wartości liczbowych.

Przykład:

sql
SELECT STDEV(ceny) FROM tab1;

STDEVP()

stdevp() zwraca odchylenie standardowe liczone dla całej populacji wartości podanych jako argument:

Przykład:

sql
SELECT STDEVP(ceny) FROM tab1;

Grupowanie wierszy: GROUP BY, GROUPING SETS i OVER

Funkcje grupujące omówione na początku tego artykułu można wywoływać dla całych tabel lub stworzonych grup. Przyjrzyjmy się więc jak ten proces wygląda.

Wszystkie ćwiczenia będziemy wykonywać na tabeli klienci, która w swojej pierwotnej formie wygląda tak:

Tabela klienci:

IDImieNazwiskoMiasto
1JanKowalskiWarszawa
2MichałŁagodaKraków
3MarcinKulczykKraków
4RadosławSikorskiKraków
5MonikaKopaczGdańsk
6PiotrGrabiecToruń
7EwaPogodnaBydgoszcz
8SebastianSzybkiWarszawa

GROUP BY

Aby zrozumieć mniej więcej jak funkcjonuje klauzula GROUP BY wykonajmy poniższe zapytanie:

sql
SELECT miasto FROM klienci
GROUP BY miasto;

W efekcie uzyskamy taki rezultat:

Miasto
Bydgoszcz
Gdańsk
Kraków
Toruń
Warszawa

Jak widać nasze dane zostały pogrupowane według miasta pochodzenia danego klienta, a każda grupa (miasto) pojawia się w wyniku dokładnie raz. Pamiętajmy, że na liście SELECT mogą się wtedy znaleźć wyłącznie kolumny wymienione w GROUP BY lub funkcje grupujące, dlatego nie mogliśmy użyć tutaj zapisu SELECT *. Załóżmy, że chcemy dowiedzieć się ilu mamy klientów z danego miasta, do tego celu wykorzystamy funkcję COUNT() omówioną wyżej oraz grupowanie:

sql
SELECT miasto, COUNT(miasto) FROM klienci
GROUP BY miasto;

Efekt naszego zapytania:

MiastoCOUNT(Miasto)
Bydgoszcz1
Gdańsk1
Kraków3
Toruń1
Warszawa2

Funkcja grupująca COUNT() został tutaj wywołana dla danej grupy, utworzonej za pośrednictwem klauzuli GROUP BY.

Oczywiście grupowanie można użyć również w nieco bardziej rozbudowanych zapytaniach, na przykład:

sql
SELECT klienci.ID, klienci.Imie, zlecenia.ID, zlecenia.Miasto
FROM klienci JOIN zlecenia
GROUP BY zlecenia.Miasto, zlecenia.ID;

lub:

sql
SELECT klienci.ID, klienci.Imie, zlecenia.ID, zlecenia.Miasto
FROM klienci JOIN zlecenia
WHERE zlecenia.Miasto != 'Kraków'
GROUP BY zlecenia.Miasto, zlecenia.ID;

Należy jednak pamiętać, że funkcje grupujące nie mogą być wywoływane w klauzuli WHERE ponieważ dane wtedy nie są jeszcze pogrupowane.

Niedopuszczalny jest więc taki zapis:

sql
SELECT klienci.ID, klienci.Imie, zlecenia.ID, zlecenia.Miasto
FROM klienci JOIN zlecenia
WHERE COUNT(zlecenia.Miasto) > 1 -- BŁĄD!
GROUP BY zlecenia.Miasto, zlecenia.ID;

GROUPING SETS

UWAGA: GROUPING SETS działa na bazach Microsoftu.

Klauzula GROUPING SETS daje możliwość pogrupowania przez wiele metod.

Zapytanie:

sql
SELECT ID, imie
FROM klienci
GROUP BY imie
UNION ALL
Select nazwisko, miasto
FROM klienci
GROUP BY miasto;

Można zastąpić takim zapytaniem:

sql
SELECT ID, imie, nazwisko, miasto
FROM klienci
GROUP BY GROUPING SETS (
(imie),
(miasto)
);

OVER

UWAGA: OVER() działa na bazach Microsoftu.

Funkcje grupujące oraz funkcje rankingu można uruchamiać dla zgrupowanych danych za pomocą GROUP BY, czasami jednak grupując otrzymane wyniki, mamy sytuacje, kiedy w grupie znajduje się dokładnie jeden wiersz, a my potrzebujemy obliczyć na przykład średnią z jakiś danych. Oczywiste jest to, że nie policzymy średniej z jednej danej, jest to całkowicie bez sensu. Możemy w takim przypadku użyć klauzuli OVER, która pozwala określić dodatkowy porządek grupowania. Co więcej zdefiniowane za pomocą tej funkcji grupy nazywamy partycjami dla których, można bez przeszkód wywołać funkcje grupujące takie jak AVG().

sql
SELECT miasto, AVG(wiek) OVER() FROM klienci WHERE miasto = 'Kraków';

W powyższym zapytaniu obliczyliśmy średni wiek klientów, w danym mieście.

Gdybyśmy nie chcieli zastosować klauzuli OVER() musielibyśmy grupować dane po mieście, oraz wieku co dałoby błędny wynik.

Jeżeli w OVER() zostanie umieszczone słowo kluczowe PARTITION BY to funkcja grupująca, bądź funkcja rankingu zostanie wywołana dla każdej zdefiniowanej w ten sposób partycji.

sql
SELECT miasto,
COUNT(*) OVER (PARTITION BY miasto)
FROM klienci;

Funkcje rankingowe

Funkcje rankingowe w SQL mogą być wywoływane tylko i wyłącznie dla utworzonych za pomocą klauzuli OVER() partycji. W przeciwieństwie do funkcji grupujących które mogą być uruchamiane między innymi dla grup utworzonych przy pomocy operatora GROUP BY.

UWAGA: Omawiane funkcje działają na bazach danych firmy Microsoft.

Najpopularniejsze funkcje rankingowe:

  • ROW_NUMBER(),
  • DENSE_RANK(),
  • RANK(),
  • NTILE().

Tabela pracownicy, na której będziemy uruchamiać przykładowe zapytania:

IDImieNazwiskoData_urodzeniaStanowiskoData_zatrudnienia
1JanKowalski1984-12-23Programista1999-12-09
2AdamAdamiak1991-08-08Menadżer2011-01-31
3JakubKowalik1990-03-16Programista2010-01-04
4MichałJakubiak1981-05-02Projektant1998-09-22

ROW_NUMBER

Funkcja ta zwraca numer wiersza, innymi słowy po prostu numeruje wiersze:

sql
SELECT Imie, Nazwisko,
ROW_NUMBER() OVER (ORDER BY Stanowisko) AS ROW_NUMBER
FROM pracownicy;

W powyższym przykładzie należy zwrócić szczególną uwagę na klauzulę ORDER BY (służy ona do sortowania danych), bez niej otrzymamy błąd.

Efekt naszego zapytania:

ImieNazwiskoROW_NUMBER
AdamAdamiak1
JakubKowalik2
JanKowalski3
MichałJakubiak4

DENSE_RANK()

Numeruje wiersze, z takim wyjątkiem, że dla powtarzających się danych zwraca ten sam numer wiersza:

sql
SELECT Stanowisko,
DENSE_RANK() OVER (ORDER BY Stanowisko) AS DENSE_RANK
FROM pracownicy;

Efekt:

StanowiskoDENSE_RANK
Menadżer1
Programista2
Programista2
Projektant3

RANK()

Działa podobnie jak funkcja DENSE_RANK(): dla powtarzających się danych zwraca ten sam numer, ale w przeciwieństwie do niej zostawia przerwy w numeracji. Kolejna wartość po remisie uwzględnia liczbę wcześniejszych wierszy.

sql
SELECT Stanowisko,
RANK() OVER (ORDER BY Stanowisko) AS [RANK]
FROM pracownicy;

Efekt:

StanowiskoRANK
Menadżer1
Programista2
Programista2
Projektant4

NTILE()

NTILE() dzieli wiersze wygenerowane przez zapytanie, na określoną ilość bloków:

sql
SELECT Imie, Nazwisko,
NTILE(2) OVER (ORDER BY Stanowisko) AS NTILE
FROM pracownicy;

Efekt:

ImieNazwiskoNTILE
AdamAdamiak1
JakubKowalik1
JanKowalski2
MichałJakubiak2

Drugi przykład (dla nieparzystej liczby części w stosunku do liczby wierszy):

sql
SELECT Imie, Nazwisko,
NTILE(3) OVER (ORDER BY Stanowisko) AS NTILE
FROM pracownicy;

Efekt:

ImieNazwiskoNTILE
AdamAdamiak1
JakubKowalik1
JanKowalski2
MichałJakubiak3

Trzeci przykład:

sql
SELECT Imie, Nazwisko,
NTILE(4) OVER (ORDER BY Stanowisko) AS NTILE
FROM pracownicy;

Podział na 4 części, czyli każdy wiersz to osobna część.

Efekt:

ImieNazwiskoNTILE
AdamAdamiak1
JakubKowalik2
JanKowalski3
MichałJakubiak4

Filtrowanie grup: klauzula HAVING

Klauzula HAVING w SQL według ogólnego wzoru zapytań SELECT podanego w artykule „Podstawowa wersja zapytania SELECT w SQL (SELECT … FROM … WHERE …)” wykonywana jest po klauzuli GROUP BY, czyli po pogrupowaniu danych. Działa ona w zasadzie tak samo jak słowo kluczowe WHERE, z takim wyjątkiem, że przy WHERE nie mogliśmy używać funkcji grupujących, funkcji rankingu oraz operować na pogrupowanych danych. W klauzuli HAVING jest to wszystko dopuszczalne. W skrócie funkcja ta może zostać wykorzystana na przykład do wyeliminowania grup danych, które nie spełniają podanego po niej warunku logicznego.

Ogólna składnia zapytania SELECT zawierającego klauzulę HAVING:

sql
SELECT kolumna1, kolumna2 FROM tabela
GROUP BY kolumna3
HAVING kolumna4 > 10; --warunek logiczny

Tabela klienci na której będziemy uruchamiać przykładowe zapytania:

IDImieNazwiskoMiasto
1JanKowalskiWarszawa
2MichałŁagodaKraków
3MarcinKulczykKraków
4RadosławSikorskiKraków
5MonikaKopaczGdańsk
6PiotrGrabiecToruń
7EwaPogodnaBydgoszcz
8SebastianSzybkiWarszawa

Pobierzmy z tabeli klienci miasta, z których mamy przynajmniej dwóch klientów:

sql
SELECT Miasto FROM klienci
GROUP BY Miasto
HAVING COUNT(*) > 1;

Efekt:

Miasto
Kraków
Warszawa

A teraz wykonajmy to samo zapytanie tylko zamiast HAVING użyjmy WHERE:

sql
SELECT miasto FROM klienci
WHERE COUNT(*) > 1 -- BŁĄD!!!
GROUP BY miasto;

Oczywiście otrzymaliśmy błąd, gdyż jak wspomniałem na początku w klauzuli WHERE nie można używać funkcji grupujących i rankingowych.

Pobierzmy informacje o miastach w których mamy klienta o imieniu „Radosław”:

sql
SELECT imie, nazwisko, miasto FROM klienci
GROUP BY imie, nazwisko, miasto
HAVING imie = 'Radosław';

Efekt:

ImieNazwiskoMiasto
RadosławSikorskiKraków

Jak można się zorientować przerabiając powyższe przykłady, dane w naszym zapytaniu są najpierw grupowane, a potem wywoływana jest klauzula HAVING. Gdybyśmy użyli słowa kluczowego WHERE, byłoby dokładnie na odwrót, czyli grupowalibyśmy tylko przefiltrowane wyniki, co w niektórych przypadkach mogłoby nam dać złe dane.

udostępnij: ·
Łukasz Dudziński
Łukasz Dudziński · założyciel i autor
Pasjonat informatyki, a zwłaszcza programowania. Zwariowany maniak na tle nowych technologii oraz komputerów. W wolnych chwilach bloger... więcej-o-mnie →
ls ~/artykuly/powiazane --limit 3
komentarze --count 2
rss dla komentarzy →
Michał2020-12-30 ·

A jeżeli chcę sumę, ale muszę dodać inne rekordy do komendy ? Np. Select (tutaj coś jest), (I tutaj też jest jakiś rekord) SUM(Tutaj jest to co sumuje)
From (jakiś zbiór)
Where (To coś)=(To inne coś)
To czy taka składnia zadziała ?

Łukasz Dudzińskiautor2020-12-31 ·

Hej Michał,

Kwerenda którą podałeś raczej nie zadziała. Jeśli sumujesz kolumnę i dodatkowo pobierasz inne kolumny, musisz zrobić grupowanie – bo jak ma zadziałać suma? Suma policzy się dla każdego rekordu, a jej składnikami będą wszystkie rekordy jakie zostały zgrupowane dla tego jednego.

Przykładowo: SELECT lastName, SUM(age) FROM users GROUP BY lastname – taka kwerenda zgrupuje ludzi o tym samym nazwisku, a następnie zsumuje ich wiek. Dla każdego nazwiska będzie suma wieku wszystkich użytkowników (o tym nazwisku), którzy reprezentowani są w tym jednym rekordzie.

dodaj-komentarz

Komentarze są moderowane. Podane dane przetwarzamy wyłącznie w celu publikacji komentarza (polityka prywatności).