1
1
Grupowanie i agregacja
Grupowanie i agregacja
danych cz.2
danych cz.2
(Group By, Having)
(Group By, Having)
Prezentacja do ćwiczenia 8
Prezentacja do ćwiczenia 8
Materiały wykorzystane w przykładach:
Materiały wykorzystane w przykładach:
Materiały pomocnicze do ćwiczeń laboratoryjnych\Ćwiczenie 8
Materiały pomocnicze do ćwiczeń laboratoryjnych\Ćwiczenie 8
2
2
Zakres ćwiczeń
Zakres ćwiczeń
Prezentacja przybliży następujące zagadnienia związane z klauzulą:
Prezentacja przybliży następujące zagadnienia związane z klauzulą:
•
Group By
Group By
•
Having
Having
W celu utrwalenia wiedzy prezentację zakończy ćwiczenie do
W celu utrwalenia wiedzy prezentację zakończy ćwiczenie do
samodzielnego
samodzielnego
wykonania.
wykonania.
3
3
Group by
Język SQL umożliwia grupowanie wierszy w zbiory, a następnie
przedstawienie danych na różnorakie sposoby. Wiersze tej samej
grupy mają identyczną wartość atrybutu grupowania, który
wskazano w klauzuli. Po podziale, dla każdej z grup można
zastosować funkcje agregujące, np. funkcję COUNT(). Ostatecznie
zwracany jest pojedynczy wiersz na bazie takiego zbioru.
Uzyskanie wspomnianego rezultatu wymaga zastosowania klauzuli
GROUP BY [HAVING] oraz funkcji agregujących.
Użyteczność funkcji agregujących jest zauważalna wówczas, gdy
stosuje się je do grupy wierszy a nie do wszystkich wierszy. W tym
celu należy stosować klauzulę GROUP BY.
Klauzula GROUP BY może być stosowana rekurencyjnie, co
oznacza, że można w niej wskazać wiele atrybutów grupowania. W
takim przypadku jest możliwe wydzielenie podgrup w ramach
wcześniej wydzielonych grup. Kolejność dzielenia relacji na grupy i
podgrupy odpowiada kolejności atrybutów grupowania.
4
4
SELECT
SELECT
kolumny
kolumny
FROM tabela(e)
FROM tabela(e)
WHERE
WHERE
kryteria
kryteria
GROUP BY
GROUP BY
pola_grupowania
pola_grupowania
[HAVING
[HAVING
...]
...]
Group by - składnia
Group by - składnia
5
5
Group by - przykłady
Group by - przykłady
Powyższe zapytanie SQL wyświetla średnią kwotę wydaną na wypożyczenie
Powyższe zapytanie SQL wyświetla średnią kwotę wydaną na wypożyczenie
pojazdu na godzinę wydaną przez określonego klienta. Grupowania dokonuje
pojazdu na godzinę wydaną przez określonego klienta. Grupowania dokonuje
się według imienia oraz nazwiska klienta.
się według imienia oraz nazwiska klienta.
Przykład 1:
Rysunek 1
SELECT k.imie, k.nazwisko, avg(w.cena) as srednia
FROM wypozyczenie as w, klient as k
WHERE k.id_klienta = w.id_klienta
GROUP BY k.imie, k.nazwisko
6
6
Warto zauważyć, że te same zapytanie SQL bez zastosowanej funkcji
Warto zauważyć, że te same zapytanie SQL bez zastosowanej funkcji
agregacji jest błędne, funkcje grupowania w powyższym przypadku muszą
agregacji jest błędne, funkcje grupowania w powyższym przypadku muszą
zostać użyte.
zostać użyte.
Group by - przykłady
Group by - przykłady
Przykład 2:
Rysunek 2
SELECT k.imie, k.nazwisko, avg(w.cena) as srednia
FROM wypozyczenie as w, klient as k
WHERE k.id_klienta = w.id_klienta
Wynik wykonania zapytania:
7
7
Zasada:
Zasada:
W przypadku zastosowania grupowania, każda nazwa kolumny
W przypadku zastosowania grupowania, każda nazwa kolumny
wymieniona ,
wymieniona ,
w liście po słowie select musi być zamknięta w nawiasach funkcji
w liście po słowie select musi być zamknięta w nawiasach funkcji
agregującej,
agregującej,
za wyjątkiem tylko tych kolumn, względem tylko tych kolumn, których
za wyjątkiem tylko tych kolumn, względem tylko tych kolumn, których
grupujemy.
grupujemy.
Funkcja agregująca przekształca zbiór wartości w jedną wartość.
Funkcja agregująca przekształca zbiór wartości w jedną wartość.
Group by
Group by
8
8
Zapytanie jako wynik zwraca imię oraz nazwisko klientów, którzy nie mieszkają
Zapytanie jako wynik zwraca imię oraz nazwisko klientów, którzy nie mieszkają
w Gdańsku. Dane grupowane są według identyfikatora klienta, imienia oraz
w Gdańsku. Dane grupowane są według identyfikatora klienta, imienia oraz
nazwiska.
nazwiska.
W powyższym zapytaniu tak samo jak poprzednio musiała zostać użyta
W powyższym zapytaniu tak samo jak poprzednio musiała zostać użyta
klauzula GROUP BY w innym wypadku zapytanie nie powiodło by się.
klauzula GROUP BY w innym wypadku zapytanie nie powiodło by się.
Group by - przykłady
Group by - przykłady
Przykład 3:
Rysunek 3
SELECT id_klienta, imie, nazwisko
FROM klient
WHERE miejscowosc != 'Gdańsk'
GROUP BY id_klienta, imie, nazwisko
9
9
Klauzula HAVING pozwala na wybór grup spełniających określone warunki,
Klauzula HAVING pozwala na wybór grup spełniających określone warunki,
działa dla grup analogicznie jak klauzula WHERE dla pojedynczych wierszy.
działa dla grup analogicznie jak klauzula WHERE dla pojedynczych wierszy.
HAVING operuje na wcześniej wydzielonych grupach, a wiec na wyniku
HAVING operuje na wcześniej wydzielonych grupach, a wiec na wyniku
działania klauzuli GROUP BY. Z punktu widzenia składni języka SQL klauzula
działania klauzuli GROUP BY. Z punktu widzenia składni języka SQL klauzula
HAVING może występować zarówno przed, jak i po klauzuli GROUP BY.
HAVING może występować zarówno przed, jak i po klauzuli GROUP BY.
Natomiast z logicznego punktu widzenia, zaleca się ją stosować po klauzuli
Natomiast z logicznego punktu widzenia, zaleca się ją stosować po klauzuli
GROUP BY. Tworzenie grup i obliczanie funkcji grupowych jest realizowane
GROUP BY. Tworzenie grup i obliczanie funkcji grupowych jest realizowane
przed selekcją grup.
przed selekcją grup.
Podsumowując:
Podsumowując:
HAVING pozwala określić, które z grupowanych
HAVING pozwala określić, które z grupowanych
wierszy
wierszy
mają być
mają być
wyświetlone. Sprawia, że po grupowaniu rekordów klauzulą GROUP
wyświetlone. Sprawia, że po grupowaniu rekordów klauzulą GROUP
pokazywane są te rekordy, które spełniają kryteria klauzuli HAVING.
pokazywane są te rekordy, które spełniają kryteria klauzuli HAVING.
Having
10
10
SELECT
SELECT
kolumny
kolumny
FROM tabela(e)
FROM tabela(e)
WHERE
WHERE
kryteria
kryteria
_wyboru_wierszy_do_grupowania
_wyboru_wierszy_do_grupowania
GROUP BY
GROUP BY
pola_grupowania
pola_grupowania
HAVING
HAVING
kryteria_grupowania;
kryteria_grupowania;
HAVING - składnia
HAVING - składnia
11
11
Powyższe zapytanie wyświetla miejscowości w których jest więcej niż
Powyższe zapytanie wyświetla miejscowości w których jest więcej niż
dwóch klientów, zliczane są wartości nie puste z kolumny miejscowość.
dwóch klientów, zliczane są wartości nie puste z kolumny miejscowość.
HAVING - przykłady
HAVING - przykłady
Przykład 4:
Rysunek 4
SELECT miejscowosc, COUNT(miejscowosc) as 'Ilosc klientow z
danego miasta '
FROM klient
GROUP BY miejscowosc HAVING COUNT(miejscowosc) > 2;
12
12
Zapytanie wyświetla podsumowanie dotyczące średniego przebiegu
Zapytanie wyświetla podsumowanie dotyczące średniego przebiegu
danych
danych
marek pojazdów z rocznika powyżej 1998 roku.
marek pojazdów z rocznika powyżej 1998 roku.
HAVING - przykłady
HAVING - przykłady
Przykład 5:
Rysunek 5
SELECT marka, AVG(przebieg) as 'średni przebieg'
FROM pojazdy
GROUP BY marka
HAVING MIN(rok_prod)>='1998';
13
13
Ćwiczenia
Ćwiczenia
1.
1.
Napisz zapytanie które wyświetli sumę przebiegu quadów
Napisz zapytanie które wyświetli sumę przebiegu quadów
(oznaczenie „q” w tabeli pojazdy) o roczniku powyżej 2007
(oznaczenie „q” w tabeli pojazdy) o roczniku powyżej 2007
2. Napisz zapytanie, które wyświetli średnią pojemność silnika
2. Napisz zapytanie, które wyświetli średnią pojemność silnika
dla motocykli (oznaczenie „m” w tabeli pojazdy) z rocznika
dla motocykli (oznaczenie „m” w tabeli pojazdy) z rocznika
2005
2005