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:
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:
SELECT COUNT(*) FROM tab1;lub:
SELECT COUNT(kolID) FROM tab1;Można również wyeliminować zliczanie powtarzających się danych za pośrednictwem słowa kluczowego DISTINCT:
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:
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:
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:
SELECT MAX(wiek) FROM tab1;STDEV()
stdev() zwraca odchylenie standardowe podanych jako argument wartości liczbowych.
Przykład:
SELECT STDEV(ceny) FROM tab1;STDEVP()
stdevp() zwraca odchylenie standardowe liczone dla całej populacji wartości podanych jako argument:
Przykład:
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:
| ID | Imie | Nazwisko | Miasto |
| 1 | Jan | Kowalski | Warszawa |
| 2 | Michał | Łagoda | Kraków |
| 3 | Marcin | Kulczyk | Kraków |
| 4 | Radosław | Sikorski | Kraków |
| 5 | Monika | Kopacz | Gdańsk |
| 6 | Piotr | Grabiec | Toruń |
| 7 | Ewa | Pogodna | Bydgoszcz |
| 8 | Sebastian | Szybki | Warszawa |
GROUP BY
Aby zrozumieć mniej więcej jak funkcjonuje klauzula GROUP BY wykonajmy poniższe zapytanie:
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:
SELECT miasto, COUNT(miasto) FROM klienci
GROUP BY miasto;Efekt naszego zapytania:
| Miasto | COUNT(Miasto) |
| Bydgoszcz | 1 |
| Gdańsk | 1 |
| Kraków | 3 |
| Toruń | 1 |
| Warszawa | 2 |
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:
SELECT klienci.ID, klienci.Imie, zlecenia.ID, zlecenia.Miasto
FROM klienci JOIN zlecenia
GROUP BY zlecenia.Miasto, zlecenia.ID;lub:
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:
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:
SELECT ID, imie
FROM klienci
GROUP BY imie
UNION ALL
Select nazwisko, miasto
FROM klienci
GROUP BY miasto;Można zastąpić takim zapytaniem:
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().
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.
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:
| ID | Imie | Nazwisko | Data_urodzenia | Stanowisko | Data_zatrudnienia |
| 1 | Jan | Kowalski | 1984-12-23 | Programista | 1999-12-09 |
| 2 | Adam | Adamiak | 1991-08-08 | Menadżer | 2011-01-31 |
| 3 | Jakub | Kowalik | 1990-03-16 | Programista | 2010-01-04 |
| 4 | Michał | Jakubiak | 1981-05-02 | Projektant | 1998-09-22 |
ROW_NUMBER
Funkcja ta zwraca numer wiersza, innymi słowy po prostu numeruje wiersze:
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:
| Imie | Nazwisko | ROW_NUMBER |
| Adam | Adamiak | 1 |
| Jakub | Kowalik | 2 |
| Jan | Kowalski | 3 |
| Michał | Jakubiak | 4 |
DENSE_RANK()
Numeruje wiersze, z takim wyjątkiem, że dla powtarzających się danych zwraca ten sam numer wiersza:
SELECT Stanowisko,
DENSE_RANK() OVER (ORDER BY Stanowisko) AS DENSE_RANK
FROM pracownicy;Efekt:
| Stanowisko | DENSE_RANK |
| Menadżer | 1 |
| Programista | 2 |
| Programista | 2 |
| Projektant | 3 |
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.
SELECT Stanowisko,
RANK() OVER (ORDER BY Stanowisko) AS [RANK]
FROM pracownicy;Efekt:
| Stanowisko | RANK |
| Menadżer | 1 |
| Programista | 2 |
| Programista | 2 |
| Projektant | 4 |
NTILE()
NTILE() dzieli wiersze wygenerowane przez zapytanie, na określoną ilość bloków:
SELECT Imie, Nazwisko,
NTILE(2) OVER (ORDER BY Stanowisko) AS NTILE
FROM pracownicy;Efekt:
| Imie | Nazwisko | NTILE |
| Adam | Adamiak | 1 |
| Jakub | Kowalik | 1 |
| Jan | Kowalski | 2 |
| Michał | Jakubiak | 2 |
Drugi przykład (dla nieparzystej liczby części w stosunku do liczby wierszy):
SELECT Imie, Nazwisko,
NTILE(3) OVER (ORDER BY Stanowisko) AS NTILE
FROM pracownicy;Efekt:
| Imie | Nazwisko | NTILE |
| Adam | Adamiak | 1 |
| Jakub | Kowalik | 1 |
| Jan | Kowalski | 2 |
| Michał | Jakubiak | 3 |
Trzeci przykład:
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:
| Imie | Nazwisko | NTILE |
| Adam | Adamiak | 1 |
| Jakub | Kowalik | 2 |
| Jan | Kowalski | 3 |
| Michał | Jakubiak | 4 |
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:
SELECT kolumna1, kolumna2 FROM tabela
GROUP BY kolumna3
HAVING kolumna4 > 10; --warunek logicznyTabela klienci na której będziemy uruchamiać przykładowe zapytania:
| ID | Imie | Nazwisko | Miasto |
| 1 | Jan | Kowalski | Warszawa |
| 2 | Michał | Łagoda | Kraków |
| 3 | Marcin | Kulczyk | Kraków |
| 4 | Radosław | Sikorski | Kraków |
| 5 | Monika | Kopacz | Gdańsk |
| 6 | Piotr | Grabiec | Toruń |
| 7 | Ewa | Pogodna | Bydgoszcz |
| 8 | Sebastian | Szybki | Warszawa |
Pobierzmy z tabeli klienci miasta, z których mamy przynajmniej dwóch klientów:
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:
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”:
SELECT imie, nazwisko, miasto FROM klienci
GROUP BY imie, nazwisko, miasto
HAVING imie = 'Radosław';Efekt:
| Imie | Nazwisko | Miasto |
| Radosław | Sikorski | Krakó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.



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.
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 ?