GROUP BY e funzioni di aggregazione

Con la parola chiave DISTINCT, nella lezione 10, abbiamo visto la forma più semplice di aggregazione: quella che distingue le righe uguali riportandole quindi una sola volta. L’esempio ivi riportato è, infatti, riscrivibile utilizzando la clausola GROUP BY; in altre parole, le seguenti due istruzioni sono equivalenti:

SELECT DISTINCT surname FROM Person

SELECT surname FROM Person GROUP BY surname

Se le eseguiamo nel nostro database di esempio, otteniamo dunque gli stessi risultati. La clausola GROUP BY serve a specificare quali sono i campi su cui effettuare i raggruppamenti: il motore di query, per ogni riga esaminerà tali campi e la classificherà nel gruppo corrispondente. Si possono specificare calcoli da effettuare per ogni gruppo. Ad esempio la query seguente restituisce, per ogni gruppo (surname), il numero di occorrenze trovate, cioè quante persone hanno quel cognome:

SELECT surname, COUNT(*)
FROM Person
GROUP BY surname

In questo caso il motore raggruppa le righe in base al cognome, e per ogni gruppo effettua il conteggio di quanti elementi sono presenti nel gruppo.

Da quanto detto, possiamo desumere una naturale limitazione di SQL: se viene specificata una clausola GROUP BY, allora nella clausola SELECT deve esserci:

• o un campo specificato nella clausola GROUP BY;
• oppure una funzione di aggregazione.

Questo perché quando il motore aggrega le righe deve sapere come comportarsi per ogni campo da restituire.

Funzioni di aggregazione

Le funzioni di aggregazione sono particolari funzioni che operano su più righe. In generale, non tutti i database supportano le stesse funzioni, per cui è bene riferirsi alle guide ufficiali. Ad esempio MySql fornisce la funzione GROUP_CONCAT, che non è invece supportata su Oracle.

Le funzioni più comuni sono:

• COUNT per effettuare conteggi nel gruppo;
• SUM per le somme;
• MAX e MIN;
• AVG per calcolare la media.

La funzione COUNT

Questa funzione può essere invocata in tre modi. Il più semplice COUNT(*) l’abbiamo già visto: effettua il conteggio di tutte le righe presenti nel gruppo, indipendentemente dai valori assunti. Può essere usato anche senza GROUP BY, per calcolare le righe totali presenti in una tabella:

SELECT COUNT(*) FROM Person

Se tra le parentesi specifichiamo un’espressione, verranno contate solo le righe che hanno quell’espressione non nulla. Ad esempio la seguente query raggruppa i libri per anno di pubblicazione, contando quanti sono e quanti sono stati suddivisi per volume.

SELECT pub_year, COUNT(*), COUNT(volume)
FROM Book
GROUP BY pub_year

Se prima dell’espressione indichiamo la parola chiave DISTINCT verranno conteggiate solo le espressioni non nulle e distinte. In questo caso, in cui abbiamo due tabelle in join, avremo come risultato il numero di redattori per ogni libro. Avremo vedremo uno 0 nel caso in cui il libro non ha specificato nessun redattore nel database.

SELECT Book.title, COUNT(DISTINCT personID)
FROM Book
LEFT JOIN Editor ON book.ID = Editor.publicationID
GROUP BY Book.id, Book.title

La funzione SUM

Questa funzione somma i valori dei campi trovati nel gruppo. I valori nulli vengono ignorati, contrariamente a quanto farebbe una somma semplice.

SELECT Person.surname, SUM(Book.pages)
FROM Book
JOIN Editor ON book.ID = Editor.publicationID
JOIN Person ON Editor.personID = Person.ID
GROUP BY Person.ID, Person.surname

Questa interrogazione restituisce il numero di pagine totale editate da ogni redattore.

Le funzioni MIN, MAX e AVG

Con queste funzioni si possono ottenere i valori massimo e minimo di una colonna, in base al criterio di ordinamento predefinito (ad esempio per il testo verrà usato un ordinamento alfabetico). I valori nulli vengono ignorati.

Naturalmente più funzioni possono essere usate nella stessa query. La seguente restituisce, per ogni anno di pubblicazione, il primo titolo in ordine alfabetico, il numero di libri pubblicati, la media del numero di pagine e il numero di pagine del libro più grande:

SELECT Book.pub_year, MIN(title), COUNT(*), AVG(pages), MAX(pages)
FROM Book
GROUP BY pub_year

Filtraggio sul raggruppamento

A differenza di WHERE, che agisce a livello di singola riga, la parola chiave HAVING permette di effettuare un filtraggio sul ragguppamento. Questa clausola si inserisce subito dopo la GROUP BY.

Il criterio di filtraggio può contenere qualsiasi funzione di raggruppamento. Supponiamo di volere vedere in quali abbiamo pubblicato almeno 100 libri:

SELECT Book.pub_year, COUNT(*), AVG(pages)
FROM Book
GROUP BY pub_year HAVING COUNT(*) > 100

Analisi dei dati con ROLLUP e CUBE

Le direttive ROLLUP e CUBE, restituiscono nel risultato ulteriori righe per rappresentare aggregazioni trasversali, ad esempio per calcolare subtotali. Vediamo un esempio concreto:

SELECT Book.pub_year, publisher, COUNT(*), SUM(pages)
FROM Book
GROUP BY pub_year, publisher WITH ROLLUP

Se eseguissimo questa query senza WITH ROLLUP otterremmo, per ogni anno e per ogni casa editrice, il numero di libri pubblicati e il totale delle pagine. Ma se volessimo vedere anche l’aggregazione per anno indipendentemente dall’editore dovremmo effettuare un’altra query. Utilizzando invece il rollup, possiamo vedere anche tali raggruppamenti trasversali, come vediamo dalle ultime tre righe restituite dalla query:

pub_year	publisher	count	pages
1993	1	1	12
1993	NULL	1	12
NULL	NULL	6	2509

Come si vede, sono stati fatti anche i raggruppamenti per anno, indipendentemente dall’editore e i totali generali (ultima riga). Non sono stati fatti invece i raggruppamenti per editore indipendentemente dall’anno. Per avere tutte le possibili combinazioni di raggruppamenti si utilizza la direttiva WITH CUBE, per ottenere anche righe fatte così:

pub_year	publisher	count	pages
NULL	5	2	999
NULL	NULL	6	2509