Kurs SQL #11: Klauzule HAVING i GROUP BY

W poprzednich lekcjach dowiedziałeś się jak korzystać z funkcji w języku SQL, a także poznałeś typy danych. Wiesz już także dokładnie jak robi się złączenia między tabelami. W tej części Darmowego Kursu SQL pokaże Ci grupowanie danych przy pomocy klauzul GROUP BY i HAVING. Oczywiście nie można ich omówić bez pokazania funkcji pracujących na zbiorach danych.

Jeżeli dopiero zaczynasz kurs zajrzyj tutaj: Kurs SQL #01: Wprowadzenie i środowisko

10 przydatnych zapytań SQL dla Administratora Baz Danych

Funkcje agregujące

Kolejnym typem funkcji występujących w języku SQL są funkcje agregujące. Działają one zawsze na zestawie danych, nigdy na pojedynczych wierszach. Dane takie możesz dla nich dostarczyć w formie zwykłego zapytania wyciągającego cała tabelę. Druga z metod to wykorzystanie specjalnych klauzul o których powiem więcej w dalszej części tego artykułu.

AVG()

Funkcja AVG() jak sama nazwa wskazuje posłuży Nam do obliczania średniej(average).

select round(avg(order_total),2) from oe.orders;
ROUND(AVG(ORDER_TOTAL),2)
34933.85

Wykorzystałem AVG() do wyliczenia średniej wartości zamówienia sklepie. Funkcja jako parametr przyjmuje nazwę kolumny, na której ma dokonać obliczeń. W tym przykładzie wykorzystałem wszystkie wiersze w tabeli OE.ORDERS, nie jest dla mnie istotne które zamówienie pochodzi od którego klienta. Można powiedzieć że podzieliłem zbiór danych na jedną grupę. Grupa ta zawiera wszystkie zamówienia w sklepie, co za tym idzie odnosi się do wszystkich wierszy w tabeli OE.ORDERS. Dodatkowo dla ułatwienia odczytu wyniku, przy pomocy ROUND(), dokonałem zaokrąglenia wyniku do dwóch miejsc po przecinku.

COUNT()

Kolejny przykład funkcji działającej na wielu wierszach na raz to COUNT(). Służy ona do zliczania ilości wierszy w podanej jej grupie. W przypadku wykonania COUNT() na całej tabeli, otrzymamy liczbę wierszy znajdujących się w tabeli.

select count(*) from oe.orders;
COUNT(*)
105

MIN() i MAX()

MIN() i MAX() będą zwracać nam konkretną wartość z zestawu danych które otrzymają. Jak się już zapewne domyślasz MIN() zwróci Ci wartość minimalną, podczas gdy MAX() maksymalną. Warto zauważyć, że każda z nich umie pracować zarówno na liczbach, tekście i datach. W przypadku tekstu otrzymywać będziemy wartości z początku lub końca listy sortowania alfabetycznego wartości. Dla dat z kolei najwcześniejszą, lub najpóźniejszą datę.

select min(list_price),max(list_price), min(product_name), max(product_name) from oe.product_information;
MIN(LIST_PRICE)MAX(LIST_PRICE)MIN(PRODUCT_NAME)MAX(PRODUCT_NAME)
1398032MB Cache /MZIP 100

SUM()

SUM() pomoże Nam w wykonaniu matematycznego dodawania. Wynik działania tej funkcji to nic innego jak suma z wartości liczbowych wchodzących w skład grupy.

select sum(order_total) from oe.orders;
SUM(ORDER_TOTAL)
3668054.7

Klauzula GROUP BY

Tak jak już w wspomniałem, do tej pory grupowaliśmy wiersze na jedną grupę. Czas stworzyć kilka grup w obrębie wierszy na których pracujemy. Do uzyskania tego efektu posłuży Nam klauzula GROUP BY. Daje ona możliwość wyspecyfikowania kolumn po których chcemy grupować. Zasada działania jest prosta. Silnik baz danych w obrębie kolumny wskazanej w klauzuli GROUP BY szuka wartości identycznych, a następnie zbiera wiersze z identycznymi wartościami tworząc z nich grupy. Dla lepszego zrozumienia, rzuć okiem na poniższą tabelę.

select * from oe.orders;
ORDER_IDORDER_DATEORDER_MODECUSTOMER_IDORDER_STATUSORDER_TOTALSALES_REP_IDPROMOTION_ID
245816-AUG-07 03.34.12.234359 PMdirect101078279.6153
239719-NOV-07 02.41.54.696211 PMdirect102142283.2154
245402-OCT-07 05.49.34.678340 PMdirect10316653.4154
235414-JUL-08 06.18.23.234567 PMdirect104046257155
235808-JAN-08 05.03.12.654278 PMdirect10527826155
238114-MAY-08 08.59.08.843679 PMdirect106323034.6156

OE.ORDERS zawiera spis zamówień do sklepu. Na pierwszy rzut oka widzimy dwie kolumny po których grupowanie miałoby sens – CUSTOMER_ID i SALES_REP_ID. Wykorzystajmy pierwszą z nich.

select 
    customer_id, sum(order_total)
from oe.orders
group by customer_id;
CUSTOMER_IDSUM(ORDER_TOTAL)
107155613.2
108213399.7
15825270.3
161600
166309

Powyższe zapytanie tworzy grupy wierszy na podstawie CUSTOMER_ID, następnie na każdej z grup wykonuje SUM(). Efektem tego zapytania jest lista klientów, wraz z wydanymi przez nich w sklepie kwotami pieniędzy.

kurs_praktyczna_administracja_oracle_database_vertical_ad

Zaawansowane grupowanie danych z ORDER BY

Nic nie stoi na przeszkodzie, aby po GROUP BY podawać więcej niż jedną kolumnę. Możemy sprawdzić, ile każdy z klientów wydał na swoje zamówienia, ale z podziałem na sprzedawców przez których kupował. Zapytanie takie wygląda jak poniżej.

select 
    customer_id, sales_rep_id, sum(order_total)
from oe.orders
group by customer_id, sales_rep_id
order by 1, 2;
CUSTOMER_IDSALES_REP_IDSUM(ORDER_TOTAL)
10115378279.6
10115929669.9
10116182445.6
10215310794.6
10215442283.2

Zauważ że w GROUP BY muszą być wymienione wszystkie pozycje z SELECT, jedynie z pominięciem tej z funkcją liczącą. Do zapytania dodałem ORDER BY dla zwiększenia przejrzystości wyniku. Jak widzisz otrzymaliśmy dokładny spis sumarycznych wydatków w sklepie, każdego klienta, z podziałem na sprzedawców.

Filtrowanie wierszy za pomocą klauzuli HAVING

HAVING służy do filtrowania wynikowych wierszy, z zapytań gdzie użyte zostało grupowanie. W naszym przykładzie otrzymaliśmy listę użytkowników, wraz z ich wydatkami. Załóżmy, że potrzebujemy takiej listy, ale tylko z osobami które sumarycznie w sklepie wydały stosunkowo niewielkie kwoty. Taka lista pozwoli Nam wytypować klientów do których warto by wysłać dodatkowe promocje. Przy pomocy HAVING możemy wybrać tych klientów, którzy wydali mniej niż 1000 w sklepie.

select 
    customer_id, sum(order_total)
from oe.orders
group by customer_id
having sum(order_total) <1000;
CUSTOMER_IDSUM(ORDER_TOTAL)
161600
166309
162220
163510
120416
16748
160969.2

Klauzula WHERE

WHERE możemy wykorzystać również przy okazji grupowania wierszy. Przyjrzyj się poniższemu przykładowi.

select 
    customer_id, sum(order_total)
from oe.orders
where order_date between to_date('01-JAN-2008','DD-MM-YYYY') and to_date('31-DEC-2008','DD-MM-YYYY')
group by customer_id
having sum(order_total) < 1000;
CUSTOMER_IDSUM(ORDER_TOTAL)
120416
117126

W powyższym przykładzie ponownie szukamy klientów którzy wydali mniej niż 1000 w sklepie. Tym razem jednak jeszcze przed wykonaniem W powyższym przykładzie ponownie szukamy klientów którzy wydali mniej niż 1000 w sklepie. Analizujemy jednak tylko zamówienia z 2008 roku. Użycie WHERE powoduje filtrowanie rekordów jeszcze przed połączeniem ich w grupy. Dalsze operacje GORUP BY i HAVING są wykonywane już tylko na rekordach z roku 2008.

Jeżeli podobały Ci się dotychczasowe materiały udostępnij je dalej przy pomocy guzików mediów społecznościowych! 

W kolejnej części Darmowego Kursu SQL dowiesz się jak pisać zaawansowane podzapytania!