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

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)
1	3980	32MB Cache /M	ZIP 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_ID	ORDER_DATE	ORDER_MODE	CUSTOMER_ID	ORDER_STATUS	ORDER_TOTAL	SALES_REP_ID	PROMOTION_ID
2458	16-AUG-07 03.34.12.234359 PM	direct	101	0	78279.6	153	–
2397	19-NOV-07 02.41.54.696211 PM	direct	102	1	42283.2	154	–
2454	02-OCT-07 05.49.34.678340 PM	direct	103	1	6653.4	154	–
2354	14-JUL-08 06.18.23.234567 PM	direct	104	0	46257	155	–
2358	08-JAN-08 05.03.12.654278 PM	direct	105	2	7826	155	–
2381	14-MAY-08 08.59.08.843679 PM	direct	106	3	23034.6	156	–

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_ID	SUM(ORDER_TOTAL)
107	155613.2
108	213399.7
158	25270.3
161	600
166	309

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.

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_ID	SALES_REP_ID	SUM(ORDER_TOTAL)
101	153	78279.6
101	159	29669.9
101	161	82445.6
102	153	10794.6
102	154	42283.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_ID	SUM(ORDER_TOTAL)
161	600
166	309
162	220
163	510
120	416
167	48
160	969.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_ID	SUM(ORDER_TOTAL)
120	416
117	126

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!