SQL Podstawy: Funkcje agregujące. Grupowanie wierszy




Tabela: pracownicy
pracownik_id
imie
nazwisko
stanowisko_id
wydzial_id
pensja
prowizja


Zadanie 1
Wykonaj raport dotyczący minimalnej, maksymalnej i średniej pensji oraz sumie miesięcznych zarobków hadlowców z działu sprzedaży.


Funkcje agregujące


Wykonanie tego raportu wymaga zastosowania operatorów matematycznych na kolumnie pensja i wybranych rekordach tabeli pracownicy.

SQL udostępnia funkcje agregujące, które operują na zbiorze wierszy i zwracają jednen wynik dla grupy.

SELECT AVG(pensja), MAX(pensja), MIN(pensja), SUM(pensja)
FROM pracownicy
WHERE stanowisko_id Like '%HAND%';


AVG
COUNT -zlicza ilość wartości nie NULL
MAX - zwarca maksymalną wartość ignorując NULL
MIN -
SUM - zraca ignorując wartości NULL

Zadanie 2
Wyświetl pracownika, który pracuje w firmie najdłużej i najkrócej.

SELECT MIN(data_zatrudnienia), MAX(data_zatrudnienia)
FROM pracownicy;

Zadanie 3
Wyświetl liczbę pracowników, którzy otrzymują prowizję w wydziale nr 8

SELECT COUNT (prowizja_proc)
FROM pracownicy
WHERE wydzial_id=8;

Zadanie 4
Utwórz raport, który wyświetli liczbę wydziałów, które zatrudniają pracowników (czyli nie chcesz zliczyć oddziałów, które nie mają pracowników).

SELECT COUNT (DISTINCT wydzial_id)
FROM pracownicy;

Jak pamiętasz funkcje agregujące ignorują wartości NULL.

Zadanie 5
Oblicz średnią prowizję otrzymywaną przez pracowników

SELECT AVG(prowizja_proc)
FROM employess;

Powyższe zapytanie zwróci średnią dla pracowików, którzy otrzymują prowizję, a nie dla wszystkich pracowników. Pamiętaj, że funkcje agregujące ignorują wartości NULL. Jeśli chcesz otrzymać poprawną średnią użyj funkcji NVL.

Aby zmusić funkcje agregujące do włączenia wartości NULL należy używać funkcji NVL.

Powyższe zapytanie powinno wyglądać tak:
SELECT AVG (NVL(prowizja_proc,0))
FROM pracownicy;

W tym zapytania dla każdego pracownika, ktory nie otrzymuje prowizji, podstawiana jest wartość 0. Średnia jest wtedy obliczana jako całkowita prowizja wypłacana wszystkim pracownikom podzielona przez ilość wszystkich pracowników w firmie. Średnia jest obliczana na podstawie wszystkich wierszy w tabeli, niezależnie od tego czy wartości null są przechowywane w kolumnie prowizja_proc.

Zadanie 6
Oblicz średnią prowizję dla pracowników w poszczególnych wydziałach.


Grupowanie wierszy


Do tego momentu funkcje agregujące wykonywały swoje operacje traktując dane jako niezróżnicowane i jednolite typy informacji. Na przykład obliczanie średniej prowizji dla wszystkich pracowników dotyczyło czytania wszystkich wierszy w kolumnie prowizja. Jednak możemy przyjąć inny scenariusz,w którym potrzebne jest obliczanie średniej  prowizji dla pracowników każdego wydziału.

Klauzula Group by


Używając klauzuli GROUP BY możemy podzielić wiersze w tabeli ma małe grupy. I wtedy użyć funkcji agregujących do uzyskana wyników dla poszczególnych grup.


Zadanie 6: rozwiązanie
Oblicz średnią prowizję dla pracowników w poszczególnych wydziałach.

SELECT wydzial_id, AVG (pensja)
FROM pracownicy
GROUP BY wydzial_id;

Ponieważ nie ma klauzuli WHERE zwracane są wszystkie wiersze z tabeli pracownicy. Następnie wiersze są grupowane według wydzial_id, więc funkcja AVG jest stosowana do kolumny pensja obliczając średnią pensję dla każdego wydziału.

Uwaga. Kiedy używasz GROUP BY upewnij się iż wszystkie kolumny, które nie mają funkcji agregujących a są wymienione w SELECT, są zawarte w klauzuli GROUP BY.

Czasami możesz potrzebować wyświetlić wyniki dla grup bez grup.

Zadanie 7
Wyświetl całkowitą sumę wynagrodzeń, które są wypłacane poszczególnym stanowisko w poszczególnych wydziałach


SELECT wydzial_id, stanowisko_id, sum(pensja)
FROM pracownicy
GROUP BY wydzial_id, job_id;

Najpierw tabela pracowników podzielona jest ze względu na id_wydziału, a następnie każda z tych grup jest pogrupowana według stanowisk.
Zauważ,  że GROUP BY grupuje wiersze lecz nie gwarantuje uporządkowania wyników. Aby posortować grupowania użyj klauzuli ORDER BY.

SELECT wydzial_id, stanowisko_id, sum(pensja)
FROM pracownicy
GROUP BY wydzial_id, job_id
ORDER BY job_id;

Klauzula Group By:

  • wszystkie kolumny w klauzuli SELECT, które nie są użyte w funkcjach agregujących muszą być użyte w klauzuli GROUP BY
  • używając klauzuli WHERE można wyłączyć wiersze zanim przydzieli się je do grup. Nie można zatem użyć Where by ograniczyć grupy np:

    SELECT wydzial_id, AVG(pensja)
    FROM pracownicy
    WHERE AVG(pensja)>4000
    GROUP BY wydzial_id;
  • nie można używać aliasu kolumny w klauzuli GROUP BY np:
    SELECT wydział_id as wydz, AVG(pensja)
    FROM pracownicy
    GROUP BY wydz;
Poodbnie jak klauzuli WHERE, można użyć klauzuli having aby ograniczać grypy.

Klauzula HAVING

Zadanie 8
Wyświetl maksymalne pencsje w wydziłach, które są wyższe niż 8000.

Jak to zrobić? Należy znależć maksymalną prowizję dla każdego wydziału poprzez grupowanie według numeru wydziału. W tej systuacji nie możemy jednak zastosowac klauzuli Where (patrz punkty wyżej).

SELECT wydzial_id , MAX(pensja)
FROM pracownicy
GROUP BY wydział_id
HAVING MAX(pensja)>8000;

Jak wykonywane jest to zapytanie? Gdy używasz HAVING Serwer Oracle wykonuje następujące kroki:

  1. Najpierw wiersze są grupowane zgodnie z GROUP BY
  2. Następnie do grup zastowawna jest funkcja agregująca (MAX)
  3. Grupy, które spełniają krytera HAVING są wyświetlane

Zadanie 9
Wyświetl raport obliczający średnią pensję w każdym wydziale i wtedy wyświetlający maksymalną średnią pensję.

SELECT MAX(AVG(pensja))
FROM pracownicy
GROUP BY wydzial_id;

Zauważ, że klauzula GROUP BY jest obowiązkowa podczas zagnieżdżania funkcji grupujących.