Come funziona il GROUP BY in SQL?

Il raggruppamento dei risultati è una potente funzione di SQL che consente di calcolare statistiche chiave per un gruppo di record.

GROUP BY è una delle clausole più potenti di SQL. Permette di vedere i dati in modo nuovo e di trovare metriche chiave (come la media, i valori massimi e minimi in un gruppo di record).

Senza GROUP BY, tutti i risultati ottenuti sono orientati ai record. Con GROUP BY, possiamo creare gruppi di record e calcolare le metriche su ciascun gruppo. In questo articolo scoprirete come GROUP BY rende le vostre query SQL molto più potenti e diversificate.

GROUP BY, parte 1: Raggruppare i dati

Supponiamo di avere un piccolo hotel in Patagonia. Abbiamo anche un database contenente i nomi degli ospiti, le città di provenienza, l'età, le date di check-in, le date di check-out e altro ancora. Questi dati sono contenuti in due tabelle chiamate room_guest e guest. Date un'occhiata:

room_guest

guest

Vogliamo calcolare alcune statistiche per poter prenotare più ospiti. La clausola SQL GROUP BY consente di raggruppare i record in base ai dati di una o più colonne. Possiamo raggruppare i record della tabella room_guest in base al valore della colonna origin_city. In questo modo tutti i record degli ospiti di "Genova" apparterranno a un gruppo; tutti i record degli ospiti di "Dubai" apparterranno a un altro gruppo e così via. La tabella seguente mostra ogni gruppo di record con un colore diverso.

Supponiamo che il proprietario dell'hotel voglia sapere quanti ospiti provengono da ogni città. Per scoprirlo, dobbiamo contare il numero di record di ciascun gruppo. In altre parole, abbiamo bisogno della funzione aggregata COUNT(*), che restituisce il numero di record in un gruppo. COUNT() è una funzione molto comune; ci torneremo più avanti in questo articolo.

Quindi, abbiamo bisogno di una query per creare gruppi di record con lo stesso valore in origin_city e poi contare il numero di record in ogni gruppo. La query dovrebbe essere simile a questa:

SELECT 	origin_city,
COUNT(*) AS quantity_of_guests
FROM   	room_guest 
GROUP BY	origin_city

È possibile confrontare il numero di ospiti di ciascuna città nella tabella dei risultati sottostante con la tabella colorata mostrata in precedenza:

Notate che il numero di righe nei risultati della query è uguale alla quantità di gruppi creati dalla clausola GROUP BY. Un gruppo per ogni città, una riga per ogni città.

Per concludere questa introduzione a GROUP BY, vi suggerisco di leggere l'articolo Come utilizzare la clausola GROUP BY. L'articolo contiene una descrizione completa della clausola GROUP BY e diversi esempi degli errori più comuni.

GROUP BY, parte 2: funzioni di aggregazione

Sebbene il raggruppamento in base a un valore sia utile, la vera potenza di GROUP BY è quando viene utilizzato con le funzioni di aggregazione. Direi che ogni query SQL che utilizza una clausola GROUP BY dovrebbe avere almeno una funzione aggregata. (Ma non è obbligatorio).

Nella sezione precedente, abbiamo detto che GROUP BY è usato per creare gruppi e calcolare metriche. Le metriche sono calcolate da funzioni di aggregazione come COUNT(), SUM(), AVG(), MIN() e MAX(). I valori calcolati da ciascuna di queste funzioni sono autoesplicativi. Tuttavia, tutte hanno qualcosa in comune: tutte le funzioni di aggregazione restituiscono un valore basato su tutti i record del gruppo.

Consideriamo un esempio. Il proprietario di un hotel vuole conoscere il valore massimo fatturato per ogni camera. Oltre a questo, vuole vedere il valore minimo e medio fatturato per ogni camera. Ecco la query e i risultati:

SELECT 	room_number,
MAX(amount_invoiced) AS max_amount_invoiced,
MIN(amount_invoiced) AS min_amount_invoiced,
AVG(amount_invoiced) AS average_amount_invoiced
FROM   	room_guest 
GROUP BY	room_number

Raggruppare i record in base a più colonne

In alcuni casi, potrebbe essere necessario raggruppare per due o più colonne. Possiamo farlo con GROUP BY? Certo che sì!

Nella query precedente, abbiamo creato un report che analizza la quantità di denaro generata da ciascuna camera. Tuttavia, alcune camere possono essere configurate a livello premium o standard (vedere il numero di camera 2002) durante le diverse stagioni; quindi, per effettuare un'analisi corretta, dobbiamo raggruppare i record utilizzando due colonne: room_number e room_level.

Prima di passare alla query, utilizziamo i colori per vedere come vengono raggruppati i record in base alla clausola GROUP BY room_number, room_level. Ricordate che i record di ogni gruppo devono avere esattamente gli stessi valori in room_number e room_level. Ad esempio, il primo gruppo è per room_number = 1001 e room_level = ‘standard’.

La query è:

SELECT 	room_number,
		room_level,
MAX(amount_invoiced) AS max_amount_invoiced,
MIN(amount_invoiced) AS min_amount_invoiced,
AVG(amount_invoiced) AS average_amount_invoiced
FROM   	room_guest 
GROUP BY	room_number, room_level

La tabella seguente mostra i risultati di questa query. È possibile confrontare questa tabella con quella precedente per verificare i risultati.

Raggruppamento dei valori NULL

Come qualsiasi altro valore, anche i valori di NULL hanno un loro gruppo; se abbiamo un NULL in una qualsiasi colonna di GROUP BY, viene creato un gruppo di record in più per quei record. Per dimostrarlo, occorre inserire una coppia di record con valori NULL nella colonna origin_city:

INSERT INTO into room_guest VALUES ('Kevin C.', NULL, 2001, '2019-07-25', '2019-08-07', NULL, 'standard', 10500);
INSERT INTO into room_guest VALUES  ('Karl J.', NULL, 1002, '2019-11-12', '2019-11-22', NULL, 'premium', 13900);

Quindi, questa query ...

SELECT 	origin_city,
COUNT(*) AS quantity_of_guests
FROM   	room_guest 
GROUP BY	origin_city

... mostrerà il seguente risultato. Si noti il nuovo gruppo per i valori NULL di origin_city nella prima riga:

Utilizzo di WHERE con GROUP BY

La clausola WHERE è usata spesso nelle query SQL, quindi è importante capire come funziona quando è combinata con GROUP BY.

La clausola WHERE viene applicata prima di GROUP BY. Ciò significa che tutti i record vengono filtrati prima da WHERE; quindi i record che corrispondono alla condizione WHERE vengono raggruppati utilizzando i criteri GROUP BY.

Come esempio, utilizziamo la query precedente, ma questa volta filtreremo gli ospiti provenienti dalle città di San Francisco e Los Angeles. La query è:

SELECT 	room_number,
		room_level,
MAX(amount_invoiced) AS max_amount_invoiced,
MIN(amount_invoiced) AS min_amount_invoiced,
AVG(amount_invoiced) AS average_amount_invoiced
FROM   	room_guest
WHERE		origin_city IN (‘San Francisco’,’Los Angeles’ )
GROUP BY	room_number, room_level

Come ci si aspettava, questo insieme di risultati è più breve di quelli precedenti; la clausola WHERE ha filtrato molti ospiti e solo i record delle camere di San Francisco e Los Angeles sono stati elaborati dalla clausola GROUP BY.

Evitare i problemi con GROUP BY

Quando si inizia a usare la clausola GROUP BY, è comune imbattersi nei seguenti problemi. Ecco come evitarli.

Problemi di conteggio

Esaminiamo un caso simile in cui è necessario aggiungere più di una colonna supplementare alla clausola GROUP BY. Nella prima query, abbiamo raggruppato per origin_city. Tuttavia, alcune città hanno lo stesso nome (perché si trovano in stati o paesi diversi). Nel nostro set di dati, abbiamo due diverse città di nome San Pedro, una in Argentina e l'altra in Spagna. Non vogliamo contarle insieme, perché sono due luoghi diversi.

Per contare queste città separatamente, dobbiamo raggruppare i record utilizzando le colonne city_origin, state e country. Quindi ripeteremo la prima query, ma aggiungendo le colonne state e country alla clausola GROUP BY. Tuttavia, se aggiungiamo delle colonne alla clausola GROUP BY, dobbiamo aggiungerle anche alla clausola SELECT.

Poiché le colonne Stato e Paese sono presenti nella tabella guest è necessario JOIN le tabelle room_guest e guest. Ecco la query che abbiamo:

SELECT 	origin_city, state, country
COUNT(*) AS number_of_guests
FROM   	room_guest 
JOIN		guest ON guest.guest_name = room_guest.guest_name
GROUP BY	origin_city, state, country

I risultati mostrano due diverse città "San Pedro", perché abbiamo usato state e country come colonne aggiuntive nella clausola GROUP BY.

C'è ancora un problema da risolvere in questa query: se la stessa persona ha visitato l'hotel due volte, la stiamo contando due volte. Questo non è necessariamente sbagliato, ma se volessimo conoscere il numero di visitatori unici dell'hotel? Dovremmo usare COUNT(distinct guest_name). La funzione di raggruppamento COUNT(distinct column) restituisce la quantità di valori unici per una determinata colonna in un gruppo di record.

Nella query seguente, aggiungiamo la funzione COUNT(distinct). Manteniamo anche la funzione originale COUNT(*), in modo che il lettore possa confrontare i due risultati:

SELECT 	origin_city, state, country
COUNT(distinct guest_name) AS number_of_unique_guests,
COUNT(*) AS number_of_guests
FROM   	room_guest 
JOIN		guest ON guest.guest_name = room_guest.guest_name
GROUP BY	origin_city, state, country

Ora possiamo vedere che l'hotel ha ricevuto un totale di tre visite da un residente di Dubai, ma che queste tre visite sono state effettuate da due persone distinte (Peter S. e Olek V) .

Prima di chiudere questa sezione, vi suggerisco di guardare questo video di 5 minuti su GROUP BY per i principianti. È un modo super dinamico per imparare SQL.

Omissione di colonne non aggregate da GROUP BY

Un altro errore molto comune di GROUP BY è quello di aggiungere una colonna non aggregata (cioè una colonna non utilizzata in una funzione aggregata) in SELECT che non è presente in GROUP BY. Per evitare questo errore, seguite una regola molto semplice: Tutte le colonne in SELECT devono comparire nella clausola GROUP BY o essere utilizzate in una funzione aggregata.

Proviamo a eseguire una query non valida per vedere l'errore:

SELECT    room_number,
	    room_level,
	    origin_city, --This column is invalid, is not in the GROUP BY
    COUNT(*) AS quantity_of_visitors,
FROM      room_guest 
GROUP BY  room_number, room_level

Se eseguiamo questa query, otterremo il seguente errore:

ERROR:  The column «room_guest.origin_city» must be in the GROUP BY clause
LINE 3:   guest_age,

Possiamo risolvere l'errore aggiungendo la colonna origin_city alla clausola GROUP BY:

	SELECT 	room_number,
			room_level,
			Origin_city,
			COUNT(*) AS quantity_of_visitors
	FROM		room_gest
	GROUP BY	room_number, room_level, origin_city -- origin_city added

Se state cercando di capire la differenza tra GROUP BY e ORDER BY, leggete Differenza tra GROUP BY e ORDER BY in parole semplici. Vi aiuterà a capire come funziona.

C'è di più da fare con GROUP BY

Abbiamo imparato a usare GROUP BY per raggruppare i record in base a valori comuni. Sappiamo che le funzioni aggregate MIN(), MAX(), AVG() e SUM() calcolano varie statistiche. La funzione COUNT() fa molte cose:

• COUNT(*) conta tutte le righe.
• COUNT(guest_name) conta tutti i valori non NULL nella colonna nome_ospite.
• COUNT(distinct guest_name) conta tutti i valori diversi non NULL nella colonna guest_name.

Quando si raggruppa, NULL riceve il proprio gruppo. Tutte le colonne non aggregate in SELECT devono essere presenti in GROUP BY.

A causa della lunghezza dell'articolo, non ho trattato la clausola HAVING, che è una sorta di clausola WHERE utilizzata per filtrare gruppi anziché record. Per i lettori che vogliono fare un passo avanti, lascio un link al nostro corso SQL Basics che tratta molti argomenti interessanti. È un ottimo modo per sviluppare le proprie competenze in SQL!