Utilizzo delle funzioni di gruppo

Reporting Dati aggregati utilizzando le funzioni di Gruppo

SQL ha numerose funzioni di aggregazione predefinite che possono essere utilizzate per scrivere query per produrre esattamente questo tipo di informazioni.La clausola GROUP BY specifica come raggruppare le righe da una tabella di dati quando si aggregano le informazioni, mentre la clausola HAVING filtra le righe che non appartengono a gruppi specificati.

Le funzioni di aggregazione eseguono una serie di azioni come il conteggio di tutte le righe in una tabella, la media dei dati di una colonna e la somma dei dati numerici. Gli aggregati possono anche cercare una tabella per trovare i valori "MAX" più alti o "MIN" più bassi in una colonna. Come con altri tipi di query, è possibile limitare o filtrare le righe su cui agiscono queste funzioni con la clausola WHERE. Ad esempio, se un manager ha bisogno di sapere quanti dipendenti lavorano in un'organizzazione, la funzione aggregata denominata COUNT (*) può essere utilizzata per produrre queste informazioni. La funzione COUNT (*) mostrata nell'istruzione SELECT di seguito conta tutte le righe in un tavolo.

SELECT COUNT(*)
FROM employees;

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

La tabella dei risultati per la funzione COUNT (*) è una singola colonna di una singola riga nota come risultato o valore scalare. Si noti che la tabella dei risultati ha un'intestazione di colonna che corrisponde al nome della funzione di aggregazione specificata nella clausola SELECT.

Alcune delle funzioni aggregate comunemente usate sono le seguenti:

SUM( [ALL | DISTINCT] expression )

AVG( [ALL | DISTINCT] expression )

COUNT( [ALL | DISTINCT] expression )

COUNT(*)

MAX(expression)

MIN(expression)

Le parole chiave ALL e DISTINCT sono opzionali e si comportano come le clausole SELECT che hai imparato a scrivere. La parola chiave ALL è l'impostazione predefinita in cui l'opzione è consentita. L'espressione elencata nella sintassi può essere una costante, una funzione, o qualsiasi combinazione di nomi di colonna, costanti e funzioni collegate da operatori aritmetici. Tuttavia, le funzioni aggregate vengono spesso utilizzate con un nome di colonna. Ad eccezione della funzione COUNT, tutte le funzioni aggregate non considerano i valori NULL.

Ci sono due regole che devi capire e seguire quando usi gli aggregati:

• Le funzioni aggregate possono essere utilizzate sia nelle clausole SELECT che in HAVING (la clausola HAVING è trattata più avanti in questo capitolo).

• Le funzioni aggregate non possono essere utilizzate in una clausola WHERE. La sua violazione produrrà la funzione di gruppo Oracle ORA-00934 non consentita qui messaggio di errore.

Illustrazioni

La query SELECT seguente conta il numero di dipendenti nell'organizzazione.

SELECT COUNT(*) Count
FROM employees;

COUNT
-----
   24

La query SELECT di seguito restituisce la media degli stipendi dei dipendenti nell'organizzazione.

SELECT AVG(Salary) average_sal
FROM employees;

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

La query SELECT di seguito restituisce la somma degli stipendi dei dipendenti dell'organizzazione.

SELECT SUM(Salary) total_sal
FROM employees;

TOTAL_SAL
---------
    87472

La query SELECT di seguito restituisce le date di assunzione meno recenti e più recenti dei dipendenti nell'organizzazione.

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

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

RAGGRUPPA PER

Le funzioni aggregate vengono normalmente utilizzate insieme a una clausola GROUP BY. La clausola GROUP BY consente di utilizzare funzioni aggregate per rispondere a domande gestionali più complesse come:

Qual è lo stipendio medio dei dipendenti in ogni reparto?

Quanti dipendenti lavorano in ogni reparto?

Quanti dipendenti stanno lavorando a un particolare progetto?

Raggruppa per funzione stabilisce gruppi di dati in base a colonne e aggrega le informazioni solo all'interno di un gruppo. Il criterio di raggruppamento è definito dalle colonne specificate nella clausola GROUP BY. Seguendo questa gerarchia, i dati vengono prima organizzati nei gruppi e quindi la clausola WHERE limita le righe in ogni gruppo.

Linee guida per l'utilizzo della clausola GROUP BY

(1) Tutte le colonne dipendenti o le colonne utilizzate nella funzione GROUP BY devono costituire la base del raggruppamento, quindi devono essere incluse anche nella clausola GROUP BY.

SELECT	DEPARTMENT_ID, SUM(SALARY)
FROM employees;

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

(2) La clausola GROUP BY non supporta l'uso di alias di colonna, ma i nomi effettivi.

(3) La clausola GROUP BY può essere utilizzata solo con funzioni aggregate come SUM, AVG, COUNT, MAX e MIN. Se viene utilizzata con funzioni a riga singola, Oracle genera un'eccezione come "ORA-00979: non un'espressione GROUP BY" .

(4) Le funzioni aggregate non possono essere utilizzate in una clausola GROUP BY. Oracle restituirà il messaggio di errore "ORA-00934: funzione di gruppo non consentita".

La query sottostante elenca il conteggio dei dipendenti che lavorano in ciascun reparto.

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

Allo stesso modo, sotto la query per trovare la somma degli stipendi per i rispettivi ID lavoro in ciascun dipartimento. Nota che il gruppo viene stabilito in base a Reparto e ID lavoro. Quindi appaiono nella clausola GROUP BY.

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

Anche la query seguente produce lo stesso risultato. Si noti che il raggruppamento si basa sulle colonne ID reparto e ID lavoro ma non viene utilizzato a scopo di visualizzazione.

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

Uso di DISTINCT, TUTTE le parole chiave con funzioni di aggregazione

Specificando la parola chiave DISTINCT con il parametro di input, il gruppo per funzione considera solo il valore univoco della colonna per l'aggregazione. Specificando la parola chiave ALL con il parametro di input, raggruppa per funzione considera tutti i valori della colonna per l'aggregazione, inclusi valori nulli e duplicati. ALL è la specifica predefinita.

La clausola HAVING

La clausola HAVING viene utilizzata per le funzioni aggregate nello stesso modo in cui viene utilizzata una clausola WHERE per i nomi e le espressioni delle colonne. Essenzialmente, le clausole HAVING e WHERE fanno la stessa cosa, ovvero filtrano le righe dall'inclusione in una tabella dei risultati in base a una condizione . Anche se può sembrare che una clausola HAVING filtra i gruppi, non lo fa, ma una clausola HAVING filtra le righe.

Quando tutte le righe di un gruppo vengono eliminate, lo è anche il gruppo. Per riassumere, le differenze importanti tra le clausole WHERE e HAVING sono:

Una clausola WHERE viene utilizzata per filtrare le righe PRIMA dell'azione GROUPING (ovvero, prima del calcolo delle funzioni aggregate).

Una clausola HAVING filtra le righe DOPO l'azione GROUPING (ovvero, dopo il calcolo delle funzioni aggregate).

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

La clausola HAVING è un'opzione condizionale direttamente correlata all'opzione della clausola GROUP BY perché una clausola HAVING elimina le righe da una tabella dei risultati in base al risultato di una clausola 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