31_SQL. Użycie klauzuli GROUP BY z HAVING

Dziś powtórzymy zasadę działania klauzuli GROUP BY. 

Skorzystamy z bezpłatnej bazy Oracle HR dostępnej tutaj:

http://www.oracle.com/technetwork/database/express-edition/downloads/index.html

Jeżeli wpiszemy proste wyrażenie pokazane poniżej, otrzymamy informację o błędzie. Funkcja AVG oblicza średnie wynagrodzenie z tabeli EMPLOYEES.

SELECT

DEPARTMENT_ID,

AVG(SALARY)

FROM EMPLOYEES;

Niezbędne jest dopisanie klauzuli GRUPED BY, która dedykowana jest do wszystkich funkcji, które podają jeden wspólny wynik dla wskazanej kolumny.

 Klauzula GRUPED BY 

Schemat działania klauzuli GRUPED BY jest następujący: najpierw po SELECT pojawia się jakaś funkcja wynikowa w połączeniu z nazwami grup. Taki zestaw danych nie może być wyświetlony ponieważ wynik jest jeden a grup jest kilka. Na końcu konieczna jest więc klauzula GRUPED BY.

Prawidłowa składnia powinna wyglądać tak:

Prawidłowe zapytanie ma formę:

SELECT

DEPARTMENT_ID,

AVG(SALARY)

FROM EMPLOYEES

GROUP BY DEPARTMENT_ID;

Otrzymujemy tabelę wynikową:

 Klauzula HAVING 

Na końcu tego wpisu zagnieździmy podzapytanie zawierające klauzulę HAVING. Tym razem będzie prosty warunek z HAVING.

 Klauzula HAVING w połączeniu z GROUP BY operuje na całych grupach nie na pojedynczych rekordach  

SELECT

DEPARTMENT_ID,

AVG(SALARY)

FROM EMPLOYEES

GROUP BY DEPARTMENT_ID

HAVING AVG(SALARY) < 6501

Klauzula HAVING uszczegóławia zapytanie. Najpierw funkcja AVG oblicza średnią. Dzięki klauzuli GROUP BY wyniki średnie zostają pogrupowane. Następnie klauzula HAVING wyświetla tylko to, co jest zgodne z zadanym warunkiem.

 Klauzula HAVING nie może istnieć bez klauzuli GROUP BY 

 Funkcje agregujące (nazwane tutaj funkcjami jednego wyniku) umieszczane są po SELECT i po HAVING 

W naszym przypadku jest to średnia pensja niższa od 6 500.

Teraz wyświetlimy wszystkie działy, w których jest zatrudnionych więcej niż 15 pracowników lub mniej niż 3.

SELECT

JOB_ID,

COUNT(LAST_NAME)

FROM EMPLOYEES

GROUP BY JOB_ID

HAVING COUNT(LAST_NAME) > 15 OR COUNT(LAST_NAME)<5;

 Po klauzuli HAVING musi znajdować się to samo wyrażenie, co po instrukcji SELECT 

Otrzymujemy tabelę wynikową:

Kolejny przykład: raport  na temat prowizji.

SELECT

JOB_ID,

AVG(COMMISSION_PCT)

FROM EMPLOYEES

GROUP BY JOB_ID

HAVING AVG(COMMISSION_PCT) < 0.3

 Klauzula HAVING ma znajdować się po GROUP BY i przed klauzulą OREDER BY 

Chcemy dowiedzieć się, w którym dziale średnia pensja jest półtora raza większa od średniej pensji wszystkich działów. Tym razem jest to zagnieżdżenie zapytań z klauzula HAVING.

SELECT

DEPARTMENT_ID,

AVG(SALARY)

FROM EMPLOYEES

GROUP BY DEPARTMENT_ID

HAVING AVG(SALARY)>

(SELECT

1.5*(AVG(SALARY))

FROM EMPLOYEES);

Otrzymujemy tabelę wynikową:

Schemat użycia klauzuli GROUP BY z HAVING

 Klauzula HAVING w połączeniu z GROUP BY wskazuje nam grupy spełniające zadany warunek 

Funkcje agregujące (nazwane tutaj funkcjami jednego wyniku) umieszczane są po SELECT i po HAVING.

 Użycie HAVING I WHERE w jednym zapytaniu 

Chcemy dowiedzieć się ile wynosiła suma wynagrodzeń dla poszczególnych typów stanowisk. Założyliśmy warunek, że nie bierzemy do sumy pojedynczych wynagrodzeń większych od 8 000 oraz, że nie interesują nas sumy wynagrodzeń dla poszczególnych stanowisk, które przekroczą 20 000.

SELECT

JOB_ID,

SUM(SALARY) AS Suma_wynagrodzeń

FROM EMPLOYEES

WHERE SALARY < 8000

GROUP BY JOB_ID

HAVING SUM(SALARY) < 20000;

Otrzymujemy tabelę wynikową:

Tym razem zrobimy raport wielkości wynagrodzeń i średnich wynagrodzeń dla poszczególnych działów.

SELECT

DEPARTMENTS.DEPARTMENT_NAME,

SUM(EMPLOYEES.SALARY) AS SUMA_WYNAGRODZEŃ,

ROUND(AVG(EMPLOYEES.SALARY)) AS ŚREDNIE_WYNAGRODZENIE

FROM EMPLOYEES, DEPARTMENTS

WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID

GROUP BY DEPARTMENTS.DEPARTMENT_NAME

ORDER BY SUMA_WYNAGRODZEŃ DESC;

Otrzymujemy tabelę wynikową:

Teraz zrobimy raport wynagrodzeń dla kategorii stanowisk. Raport będzie uwzględniał średnie wynagrodzenie, maksymalne i minimalne wynagrodzenie oraz ilość stanowisk pracy w danej kategorii stanowisk.

SELECT

EMPLOYEES.JOB_ID,

MIN(EMPLOYEES.SALARY),

MAX(EMPLOYEES.SALARY),

AVG(EMPLOYEES.SALARY),

COUNT(EMPLOYEES.SALARY) AS ILOŚĆ_PRACOWNIKÓW

FROM EMPLOYEES, JOBS

WHERE EMPLOYEES.JOB_ID = JOBS.JOB_ID

GROUP BY EMPLOYEES.JOB_ID;

Otrzymujemy tabelę wynikową: