Grupowanie i agregacja

danych cz.2

(Group By, Having)

Prezentacja do ćwiczenia 8

Materiały wykorzystane w przykładach:

Materiały pomocnicze do ćwiczeń laboratoryjnych\Ćwiczenie 8

Zakres ćwiczeń

Prezentacja przybliży następujące zagadnienia związane z klauzulą:

•

Group By

•

Having

W celu utrwalenia wiedzy prezentację zakończy ćwiczenie do

samodzielnego

wykonania.

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.

SELECT

kolumny

FROM tabela(e)

WHERE

kryteria

GROUP BY

pola_grupowania

[HAVING

...]

Group by - składnia

Group by - przykłady

Powyższe zapytanie SQL wyświetla średnią kwotę wydaną na wypożyczenie

pojazdu na godzinę wydaną przez określonego klienta. Grupowania dokonuje

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

Warto zauważyć, że te same zapytanie SQL bez zastosowanej funkcji

agregacji jest błędne, funkcje grupowania w powyższym przypadku muszą

zostać użyte.

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:

Zasada:

W przypadku zastosowania grupowania, każda nazwa kolumny

wymieniona ,

w liście po słowie select musi być zamknięta w nawiasach funkcji

agregującej,

za wyjątkiem tylko tych kolumn, względem tylko tych kolumn, których

grupujemy.

Funkcja agregująca przekształca zbiór wartości w jedną wartość.

Group by

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

nazwiska.

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

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

Klauzula HAVING pozwala na wybór grup spełniających określone warunki,

działa dla grup analogicznie jak klauzula WHERE dla pojedynczych wierszy.

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

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

GROUP BY. Tworzenie grup i obliczanie funkcji grupowych jest realizowane

przed selekcją grup.

Podsumowując:

HAVING pozwala określić, które z grupowanych

wierszy

mają być

wyświetlone. Sprawia, że po grupowaniu rekordów klauzulą GROUP

pokazywane są te rekordy, które spełniają kryteria klauzuli HAVING.

Having

SELECT

kolumny

FROM tabela(e)

WHERE

kryteria

_wyboru_wierszy_do_grupowania

GROUP BY

pola_grupowania

HAVING

kryteria_grupowania;

HAVING - składnia

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ść.

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;

Zapytanie wyświetla podsumowanie dotyczące średniego przebiegu

danych

marek pojazdów z rocznika powyżej 1998 roku.

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';

Ćwiczenia

Napisz zapytanie które wyświetli sumę przebiegu quadów

(oznaczenie „q” w tabeli pojazdy) o roczniku powyżej 2007

2. Napisz zapytanie, które wyświetli średnią pojemność silnika

dla motocykli (oznaczenie „m” w tabeli pojazdy) z rocznika

2005

Document Outline

Slide 1
Slide 2
Slide 3
Slide 4
Slide 5
Slide 6
Slide 7
Slide 8
Slide 9
Slide 10
Slide 11
Slide 12
Slide 13

Prezentacja do cwiczenia 8 mssql

Document Outline