SQL. Grupowanie wierszy Group by, Having

Tabela: pracownicy
pracownik_id
imie
nazwisko
stanowisko_id
wydzial_id
pensja
prowizja

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.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *