Korzystanie z funkcji grupowych

Raportowanie danych zbiorczych za pomocą funkcji grupowych

SQL ma wiele predefiniowanych funkcji agregujących, których można używać do pisania zapytań w celu uzyskania dokładnie tego rodzaju informacji. Klauzula GROUP BY określa, jak grupować wiersze z tabeli danych podczas agregowania informacji, a klauzula HAVING odfiltrowuje wiersze, które nie należą do określone grupy.

Funkcje agregujące wykonują różne czynności, takie jak zliczanie wszystkich wierszy w tabeli, uśrednianie danych w kolumnie i sumowanie danych liczbowych. Agregaty mogą również przeszukiwać tabelę, aby znaleźć najwyższe „MAX” lub najniższe „MIN” wartości w kolumnie. Podobnie jak w przypadku innych typów zapytań, możesz ograniczyć lub odfiltrować wiersze, na które działają te funkcje, za pomocą klauzuli WHERE. Na przykład, jeśli kierownik musi wiedzieć, ilu pracowników pracuje w organizacji, do uzyskania tych informacji można użyć funkcji zbiorczej o nazwie COUNT (*). Funkcja COUNT (*) pokazana w poniższej instrukcji SELECT zlicza wszystkie wiersze w stół.

SELECT COUNT(*)
FROM employees;

  COUNT(*)
----------
        24

Tabela wynikowa funkcji COUNT (*) to pojedyncza kolumna z jednego wiersza, nazywana wynikiem lub wartością skalarną. Zwróć uwagę, że tabela wynikowa ma nagłówek kolumny, który odpowiada nazwie funkcji agregującej określonej w klauzuli SELECT.

Poniżej przedstawiono niektóre z powszechnie używanych funkcji agregujących -

SUM( [ALL | DISTINCT] expression )

AVG( [ALL | DISTINCT] expression )

COUNT( [ALL | DISTINCT] expression )

COUNT(*)

MAX(expression)

MIN(expression)

Słowa kluczowe ALL i DISTINCT są opcjonalne i działają tak samo jak z klauzulami SELECT, których nauczyłeś się pisać. Słowo kluczowe ALL jest ustawieniem domyślnym, gdzie opcja jest dozwolona. Wyrażenie podane w składni może być stałą, funkcją, lub dowolna kombinacja nazw kolumn, stałych i funkcji połączonych operatorami arytmetycznymi, jednak funkcje agregujące są najczęściej używane z nazwą kolumny. Z wyjątkiem funkcji COUNT, wszystkie funkcje agregujące nie uwzględniają wartości NULL.

Istnieją dwie zasady, które należy zrozumieć i przestrzegać podczas korzystania z agregatów:

Funkcje agregujące mogą być używane zarówno w klauzuli SELECT, jak i HAVING (klauzula HAVING jest opisana w dalszej części tego rozdziału).
Funkcji agregujących nie można używać w klauzuli WHERE. Jego naruszenie spowoduje powstanie funkcji grupowej Oracle ORA-00934, w tym przypadku komunikat o błędzie jest niedozwolony.

Ilustracje

Poniższe zapytanie SELECT zlicza liczbę pracowników w organizacji.

SELECT COUNT(*) Count
FROM employees;

COUNT
-----
   24

Poniższe zapytanie SELECT zwraca średnią pensji pracowników w organizacji.

SELECT AVG(Salary) average_sal
FROM employees;

AVERAGE_SAL
-----------
      15694

Poniższe zapytanie SELECT zwraca sumę wynagrodzeń pracowników w organizacji.

SELECT SUM(Salary) total_sal
FROM employees;

TOTAL_SAL
---------
    87472

Poniższe zapytanie SELECT zwraca najstarsze i najnowsze daty zatrudnienia pracowników w organizacji.

SELECT MIN (hire_date) oldest, MAX (hire_date) latest
FROM employees;

OLDEST		LATEST
---------	-----------
16-JAN-83	01-JUL-2012

GRUPUJ WEDŁUG

Funkcje agregujące są zwykle używane w połączeniu z klauzulą GROUP BY. Klauzula GROUP BY umożliwia korzystanie z funkcji agregujących do odpowiedzi na bardziej złożone pytania dotyczące zarządzania, takie jak:

Jakie jest średnie wynagrodzenie pracowników w każdym dziale?

Ilu pracowników pracuje w każdym dziale?

Ilu pracowników pracuje nad konkretnym projektem?

Funkcja Group by tworzy grupy danych na podstawie kolumn i agreguje informacje tylko w ramach grupy. Kryterium grupowania jest definiowane przez kolumny określone w klauzuli GROUP BY. Zgodnie z tą hierarchią dane są najpierw organizowane w grupy, a następnie klauzula WHERE ogranicza wiersze w każdej grupie.

Zasady korzystania z klauzuli GROUP BY

(1) Wszystkie zależne kolumny lub kolumny używane w funkcji GROUP BY muszą stanowić podstawę grupowania, dlatego muszą być również zawarte w klauzuli GROUP BY.

SELECT	DEPARTMENT_ID, SUM(SALARY)
FROM employees;

DEPARTMENT_ID,
*
ERROR at line 2:
ORA-00937: not a single-group group function

(2) Klauzula GROUP BY nie obsługuje aliasów kolumn, ale rzeczywiste nazwy.

(3) Klauzula GROUP BY może być używana tylko z funkcjami agregującymi, takimi jak SUMA, AVG, COUNT, MAX i MIN.Jeśli jest używana z funkcjami jednorzędowymi, Oracle zgłasza wyjątek jako „ORA-00979: nie wyrażenie GROUP BY” .

(4) Funkcje agregujące nie mogą być używane w klauzuli GROUP BY. Oracle zwróci tutaj komunikat o błędzie „ORA-00934: funkcja grupy niedozwolona”.

Poniższe zapytanie wyświetla liczbę pracowników pracujących w każdym dziale.

SELECT  DEPARTMENT_ID,  COUNT (*)
FROM employees
GROUP BY DEPARTMENT_ID;

Podobnie, poniżej zapytanie, aby znaleźć sumę wynagrodzeń dla odpowiednich identyfikatorów stanowisk w każdym dziale. Uwaga: grupa jest tworzona na podstawie działu i identyfikatora stanowiska. Pojawiają się więc w klauzuli GROUP BY.

SELECT DEPARTMENT_ID, JOB_ID, SUM (SAL)
FROM employees
GROUP BY DEPARTMENT_ID, JOB_ID;

Poniższe zapytanie również daje ten sam wynik. Należy pamiętać, że grupowanie jest oparte na kolumnach identyfikatora działu i identyfikatora zadania, ale nie jest używane do celów wyświetlania.

SELECT SUM (SALARY)
FROM employees
GROUP BY DEPARTMENT_ID, JOB_ID;

Stosowanie słów kluczowych DISTINCT, ALL z funkcjami agregującymi

Określając słowo kluczowe DISTINCT z parametrem wejściowym, funkcja grupowania według funkcji uwzględnia tylko unikalną wartość kolumny do agregacji. Określając słowo kluczowe ALL z parametrem wejściowym, funkcja grupowania według funkcji uwzględnia wszystkie wartości kolumny do agregacji, w tym wartości null i duplikaty. ALL jest specyfikacją domyślną.

Klauzula HAVING

Klauzula HAVING jest używana do funkcji agregujących w taki sam sposób, w jaki klauzula WHERE jest używana do nazw kolumn i wyrażeń. Zasadniczo klauzule HAVING i WHERE robią to samo, czyli filtrują wiersze z uwzględnienia w tabeli wynikowej na podstawie warunku . Chociaż może się wydawać, że klauzula HAVING odfiltrowuje grupy, tak nie jest, a raczej klauzula HAVING filtruje wiersze.

Kiedy wszystkie wiersze z grupy zostaną wyeliminowane, to samo dotyczy grupy. Podsumowując, istotne różnice między klauzulami WHERE i HAVING są następujące:

Klauzula WHERE służy do filtrowania wierszy PRZED akcją GROUPING (tj. Przed obliczeniem funkcji agregujących).

Klauzula HAVING filtruje wiersze PO akcji GROUPING (tj. Po obliczeniu funkcji agregujących).

SELECT JOB_ID,	SUM (SALARY)
FROM employees
GROUP BY JOB_ID
HAVING SUM (SALARY) > 10000;

Klauzula HAVING jest opcją warunkową, która jest bezpośrednio powiązana z opcją klauzuli GROUP BY, ponieważ klauzula HAVING eliminuje wiersze z tabeli wynikowej na podstawie wyniku klauzuli GROUP BY.

SELECT department_id, AVG(Salary)
FROM employees
HAVING AVG(Salary) > 33000;
ERROR at line 1:  ORA-00937: not a single-group group function