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.
Jeżeli preferujesz naukę prze oglądanie materiałów video zachęcam Cię do sprawdzenia mojego Kursu online SQL w Praktyce!
Kurs dostępny pod linkiem: Kurs SQL
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!