Articolo originale: SQL Group By Tutorial: Count, Sum, Average, and Having Clauses Explained

La clausola GROUP BY è molto utile ma a volte può essere un po' fuorviante.

Anche dopo otto anni, ogni volta che uso GROUP BY devo fermarmi e pensare a cosa sta facendo realmente.

In questo articolo, vedremo come costruire una clausola GROUP BY, cosa fa alla tua query e come puoi usarla per eseguire raggruppamenti e raccogliere informazioni sui tuoi dati.

Parleremo di:

Creare il database

Prima di poter iniziare a scrivere le nostre query dobbiamo creare un database.

Per questi esempi, useremo PostgreSQL, ma le query e i concetti mostrati qui possono essere trasferiti facilmente in tutti i sistemi di database moderni (come MySQL, SQL Server e così via).

Per lavorare con il nostro database PostgreSQL, possiamo usare psql — il programma interattivo a riga di comando di PostgreSQL. Se hai un altro client di database con cui preferisci lavorare andrà bene lo stesso.

Per iniziare, creiamo il database. Con PostgreSQL già installato, possiamo eseguire sul terminale il comando createdb <nome-database> per creare un nuovo database. Ho chiamato il mio fcc:

$ createdb fcc

Poi, avviamo la console interattiva usando il comando psql e connettiamola al database appena creato con \c <nome-database>:

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#
Note: in questi esempi, ho ripulito l'output psql per migliorare la leggibilità, quindi non preoccuparti se l'output mostrato qui non è esattamente lo stesso che vedi nel tuo terminale.

Ti suggerisco di seguire questi esempi e di eseguire anche tu le query. Imparerai e ricorderai molto di più lavorando con questi esempi che leggendoli solamente.

Aggiungere i dati

Per i nostri esempi, useremo una tabella che contiene dei dati di vendite di vari prodotti in vari negozi.

Chiameremo questa tabella vendite e sarà una semplice rappresentazione del registro delle vendite di un negozio, contenente: nome del luogo, nome del prodotto, prezzo e momento della vendita (venduto_il).

Se stessimo costruendo questa tabella in una applicazione reale, imposteremo delle chiavi esterne ad altre tabelle, ma per illustrare il concetto alla base di GROUP BY useremo semplicemente delle colonne di testo.

Creiamo la tabella e inseriamo alcuni dati di vendita:

CREATE TABLE vendite(
  luogo TEXT,
  prodotto TEXT,
  prezzo DECIMAL,
  venduto_il TIMESTAMP
);

INSERT INTO vendite(luogo, prodotto, prezzo, venduto_il) VALUES
('HQ', 'Coffee', 2, NOW()),
('HQ', 'Coffee', 2, NOW() - INTERVAL '1 hour'),
('Downtown', 'Bagel', 3, NOW() - INTERVAL '2 hour'),
('Downtown', 'Coffee', 2, NOW() - INTERVAL '1 day'),
('HQ', 'Bagel', 2, NOW() - INTERVAL '2 day'),
('1st Street', 'Bagel', 3, NOW() - INTERVAL '2 day' - INTERVAL '1 hour'),
('1st Street', 'Coffee', 2, NOW() - INTERVAL '3 day'),
('HQ', 'Bagel', 3, NOW() - INTERVAL '3 day' - INTERVAL '1 hour');

Abbiamo tre luoghi: HQ, Downtown e 1st Street.

Abbiamo due prodotti, Coffee e Bagel, e inseriamo le vendite con diversi valori venduto_il per rappresentare le diverse volte in cui un prodotto è stato venduto.

Alcune vendite sono di oggi, alcuni di ieri e altre dall'altro ieri.

Come funziona GROUP BY?

Per illustrare come funziona la clausola GROUP BY, partiamo da un esempio.

Immagina una stanza piena di persone nate in diverse nazioni.

Se vogliamo trovare l'altezza media per nazione delle persone nella stanza, dovremmo prima chiedere alle persone di dividersi in gruppi in base al loro Paese di nascita.

Una volta separate nei vari gruppi, possiamo calcolare l'altezza media in ogni gruppo.

La clausola GROUP BY funziona in questo modo: prima definiamo come vogliamo raggruppare insieme le righe — poi possiamo svolgere dei calcoli o raggruppamenti sui gruppi.

Gruppi multipli

Possiamo raggruppare i dati in tanti gruppi o sotto-gruppi quanti ne desideriamo.

Ad esempio, dopo aver chiesto alle persone di dividersi in gruppi in base alla loro nazione di nascita, potremmo chiedere ai membri di ogni gruppo di suddividersi ulteriormente in altri gruppi in base al colore degli occhi.

Facendo questo, ci ritroveremo con persone divise in base alla combinazione di nazione di nascita e colore degli occhi.

Adesso possiamo trovare l'altezza media in ognuno dei gruppi più piccoli e avremo dei risultati più specifici: altezza media per nazione per colore degli occhi.

Le clausole GROUP BY sono spesso usate in situazioni in cui puoi usare la frase per qualcosa o per ogni qualcosa:

  • altezza media per nazione di nascita
  • numero totale di persone per ogni combinazione di colore di occhi e capelli
  • vendite totali per prodotto

Scrivere clausole GROUP BY

Una clausola GROUP BY è molto semplice da scrivere — usiamo semplicemente le parole chiave GROUP BY e poi specifichiamo il campo (o i campi) che vogliamo raggruppare:

SELECT ...
FROM vendite
GROUP BY luogo;

Questa semplice query raggruppa i dati di vendite secondo la colonna luogo.

Abbiamo fatto il raggruppamento — ma cosa mettiamo in SELECT?

La cosa ovvia da selezionare è la colonna luogo — la stiamo usando per raggruppare, quindi vogliamo vedere almeno i nomi dei gruppi che abbiamo realizzato:

SELECT luogo
FROM vendite
GROUP BY luogo;

Il risultato è:

  luogo
------------
 1st Street
 HQ
 Downtown
(3 rows)

Se diamo un'occhiata alla tabella con i dati grezzi (SELECT * FROM vendite;), vedremo che abbiamo quattro righe per  HQ, due righe per Downtown e due righe per 1st Street:

 prodotto |    luogo   | prezzo |          venduto_il
---------+------------+-------+----------------------------
 Coffee   | HQ         |    2   | 2020-09-01 09:42:33.085995
 Coffee   | HQ         |    2   | 2020-09-01 08:42:33.085995
 Bagel    | Downtown   |    3   | 2020-09-01 07:42:33.085995
 Coffee   | Downtown   |    2   | 2020-08-31 09:42:33.085995
 Bagel    | HQ         |    2   | 2020-08-30 09:42:33.085995
 Bagel    | 1st Street |    3   | 2020-08-30 08:42:33.085995
 Coffee   | 1st Street |    2   | 2020-08-29 09:42:33.085995
 Bagel    | HQ         |    3   | 2020-08-29 08:42:33.085995
(8 rows)

Raggruppando sulla colonna luogo, il database prende queste righe di input e identifica i luoghi unici tra essi — questi luoghi unici saranno i nostri "gruppi".

E le altre colonne nella tabella?

Se proviamo a selezionare una colonna come prodotto, che non abbiamo raggruppato...

SELECT
  luogo,
  prodotto
FROM vendite
GROUP BY luogo;

...incapperemo in questo errore:

ERROR:  column "vendite.prodotto" must appear in the GROUP BY clause or be used in an aggregate function

Il problema è che abbiamo otto righe che abbiamo compresso in tre righe.

Non possiamo ottenere le altre colonne in modo normale — avevamo otto righe e ora ne abbiamo tre.

Cosa facciamo con le rimanenti cinque righe di dati? Quali delle otto righe di dati dovrebbero essere visualizzate nelle tre righe distinte con i luoghi?

Non c'è una risposta chiara e definitiva.

Per utilizzare il resto della tabella di dati, dobbiamo scegliere anche i dati dalle colonne restanti per ottenere tre gruppi di luoghi.

Questo significa che dobbiamo aggregare o svolgere un calcolo per produrre qualche tipo di informazione di riepilogo per i dati restanti.

Aggregazioni (COUNT, SUM, AVG)

Una volta che abbiamo deciso come raggruppare i dati, possiamo svolgere delle aggregazioni sulle altre colonne.

Stiamo parlando di operazioni come contare il numero di righe per gruppo, sommare un valore specifico all'interno del gruppo oppure mediare un'informazione nel gruppo.

Per iniziare, troviamo il numero di vendite per luogo.

Dato che ogni dato nella nostra tabella vendite è una vendita, il numero di vendite per luogo sarà il numero di righe di ogni gruppo, per ognuno di essi.

Per fare questa operazione useremo la funzione di aggregazione COUNT() per contare il numero di righe in ogni gruppo:

SELECT
  luogo,
  COUNT(*) AS numero_di_vendite
FROM vendite
GROUP BY luogo;

Utilizziamo COUNT(*) che conta tutte le righe di input per un gruppo (COUNT() funziona anche con delle espressioni, ma ha un comportamento leggermente diverso).

Ecco come il database esegue questa query:

  • FROM vendite — prima di tutto, recupera tutti i dati dalla tabella vendite
  • GROUP BY luogo — poi, determina i gruppi di luogo unici
  • SELECT ... — infine, seleziona il nome dei luoghi e il conteggio del numero di righe nel gruppo

Abbiamo dato al conteggio delle righe un alias usando AS numero_di_vendite per rendere l'output più leggibile. Ed ecco quello che otteniamo:

  luogo     | numero_di_vendite
------------+-------------------
 1st Street |                2
 HQ         |                4
 Downtown   |                2
(3 rows)

Il luogo 1st Street ha due vendite, HQ ne ha quattro e Downtown ne ha due.

Qui possiamo vedere come abbiamo preso le colonne di dati rimanenti dalle otto righe indipendenti, condensandole in un'informazione riepilogativa utile per ogni luogo: il numero delle vendite.

SUM

Analogamente, invece di contare il numero delle righe in un gruppo, potremmo sommare le informazioni all'interno di ogni gruppo — ad esempio per ottenere il totale incassato dalle vendite di ogni luogo.

Per farlo possiamo usare la funzione SUM():

SELECT
  luogo,
  SUM(prezzo) AS incasso_totale
FROM vendite
GROUP BY luogo;

Invece di contare il numero di righe di ogni gruppo, sommiamo le entrate di ogni vendita per ottenere il totale delle vendite per luogo:

  luogo     | incasso_totale
------------+----------------
 1st Street |             5
 HQ         |             9
 Downtown   |             5
(3 rows)

Average (AVG)

Per trovare il prezzo medio delle vendite per luogo dobbiamo semplicemente sostituire la funzione  SUM() con la funzione AVG():

SELECT
  luogo,
  AVG(prezzo) AS incasso_medio_per_vendita
FROM vendite
GROUP BY luogo;

Lavorare con gruppi multipli

Finora abbiamo lavorato con un singolo gruppo: luogo.

E se volessimo dividere ulteriormente questo gruppo?

In modo simile al caso della "nazione di nascita e colore degli occhi" con cui abbiamo iniziato, come potremmo fare se volessimo trovare il numero di vendite per prodotto per luogo?

Tutto ciò di cui abbiamo bisogno è di una seconda condizione di raggruppamento nell'istruzione GROUP BY:

SELECT ...
FROM vendite
GROUP BY luogo, prodotto;

Aggiungendo una seconda colonna in GROUP BY dividiamo ulteriormente i gruppi luogo per prodotto.

Visto che ora stiamo raggruppando anche secondo la colonna proodotto, possiamo aggiungerla a SELECT!

SELECT
  luogo,
  prodotto
FROM vendite
GROUP BY luogo, prodotto
ORDER BY luogo, prodotto;
Nota: sto aggiungendo a queste query delle clausole ORDER BY per rendere l'output più leggibile.

Osservando il risultato del nuovo raggruppamento, possiamo vedere le combinazioni uniche luogo/prodotto:

   luogo    | prodotto
------------+-----------
 1st Street | Bagel
 1st Street | Coffee
 Downtown   | Bagel
 Downtown   | Coffee
 HQ         | Bagel
 HQ         | Coffee
(6 rows)

Ora che abbiamo i nostri gruppi, cosa vogliamo fare con il resto delle colonne di dati?

Bene, possiamo trovare il numero di vendite per prodotto per luogo usando le stesse funzioni di aggregazione di prima:

SELECT
  luogo,
  prodotto,
  COUNT(*) AS numero_di_vendite
FROM vendite
GROUP BY luogo, prodotto
ORDER BY luogo, prodotto;

   luogo    | prodotto | numero_di_vendite
------------+---------+-------------------
 1st Street |  Bagel   |               1
 1st Street |  Coffee  |               1
 Downtown   |  Bagel   |               1
 Downtown   |  Coffee  |               1
 HQ         |  Bagel   |               2
 HQ         |  Coffee  |               2
(6 rows)
Esercizio per il lettore: trova l'incasso totale per prodotto per luogo.

Usare delle funzioni in GROUP BY

Adesso, proviamo a trovare il numero totale di vendite per giorno.

Se seguiamo uno schema simile a quello usato per i luoghi e raggruppiamo la colonna venduto_il...

SELECT
  venduto_il,
  COUNT(*) AS vendite_per_giorno
FROM vendite
GROUP BY venduto_il
ORDER BY venduto_il;

...potremmo aspettarci di avere un gruppo per ogni giorno — invece otteniamo:

       venduto_il           | vendite_per_giorno
----------------------------+-------------------
 2020-08-29 08:42:33.085995 |               1
 2020-08-29 09:42:33.085995 |               1
 2020-08-30 08:42:33.085995 |               1
 2020-08-30 09:42:33.085995 |               1
 2020-08-31 09:42:33.085995 |               1
 2020-09-01 07:42:33.085995 |               1
 2020-09-01 08:42:33.085995 |               1
 2020-09-01 09:42:33.085995 |               1
(8 rows)

Sembra che i nostri dati non siano stati raggruppati affatto — otteniamo ogni riga singolarmente.

Ma i dati sono stati effettivamente raggruppati! Il problema è che ogni riga venduto_il è un valore unico — quindi ogni riga determina un gruppo!

GROUP BY sta funzionando correttamente, ma questo non è l'output che desideriamo.

Il responsabile è l'informazione unica ore/minuti/secondi della marca temporale.

Ogni marca temporale differisce per ore, minuti o secondi — così ogni vendita viene inserita in un nuovo gruppo.

Dobbiamo convertire ogni valore di data e ora in una data:

  • 2020-09-01 08:42:33.085995 => 2020-09-01
  • 2020-09-01 09:42:33.085995 => 2020-09-01

Una volta convertite in una data, tutte le marche temporali dello stesso giorno restituiranno la stessa data — e i dati delle vendite relative verranno inseriti nello stesso gruppo.

Per svolgere questa operazione, dobbiamo cambiare la marca temporale di venduto_il in una data:

SELECT
  venduto_il::DATE AS data,
  COUNT(*) AS vendite_per_giorno
FROM vendite
GROUP BY venduto_il::DATE
ORDER BY venduto_il::DATE;

Nella clausola GROUP BY utilizziamo ::DATE per troncare la marca temporale in corrispondenza del giorno. Questa espressione taglia effettivamente l'informazione ore/minuti/secondi della marca temporale restituendo solo il giorno.

In SELECT, possiamo includere la stessa espressione e darle un alias per avere un output più grazioso.

Per la stessa ragione per cui non potevamo restituire la colonna prodotto senza raggrupparla o eseguire qualche tipo di aggregazione, il database non ci permetterà di restituire venduto_il — tutto quello che si trova in SELECT deve essere in GROUP BY o in qualche tipo di aggregazione sui gruppi risultanti.

Il risultato è il numero di vendite per giorno che volevamo ottenere in origine:

    data    | vendite_per_giorno
------------+-------------------
 2020-08-29 |             2
 2020-08-30 |             2
 2020-08-31 |             1
 2020-09-01 |             3
(4 rows)

Filtrare i gruppi con HAVING

E ora vediamo come filtrare le righe raggruppate.

Per farlo, proviamo a trovare i giorni in cui c'è stata più di una vendita.

Di norma, senza raggruppare, avremmo filtrato le righe usando una clausola WHERE. Ad esempio:

SELECT *
FROM vendite
WHERE prodotto = 'Coffee';

Potremmo fare qualcosa simile a filtrare i gruppi in base al conteggio delle righe...

SELECT
  venduto_il::DATE AS data,
  COUNT(*) AS vendite_per_giorno
FROM vendite
WHERE COUNT(*) > 1      -- filtrare i gruppi?
GROUP BY venduto_il::DATE;

Sfortunatamente, questo approccio non funziona e otteniamo il seguente errore:

ERROR:  aggregate functions are not allowed in WHERE

Le funzioni di aggregazione non sono ammesse nelle clausole WHERE perché la clausola WHERE viene valutata prima della clausola GROUP BY — non ci sono ancora gruppi su cui effettuare delle operazioni.

Ma c'è un tipo di clausola che ci permette di filtrare, svolgere aggregazioni ed è valutata dopo la clausola GROUP BY: la clausola HAVING.

La clausola HAVING è come una clausola WHERE ma per i gruppi.

Per trovare i giorni in cui abbiamo più di una vendita, possiamo aggiungere una clausola HAVING che verifica il conteggio delle righe del gruppo:

SELECT
  venduto_il::DATE AS data,
  COUNT(*) AS vendite_per_giorno
FROM vendite
GROUP BY venduto_il::DATE
HAVING COUNT(*) > 1;

Questa clausola HAVING esclude ogni riga in cui il conteggio delle righe del gruppo non è maggiore di uno, e il risultato è:

    data    | vendite_per_giorno
------------+-------------------
 2020-09-01 |             3
 2020-08-29 |             2
 2020-08-30 |             2
(3 rows)

Per completezza, ecco l'ordine di esecuzione di tutte le parti di un'istruzione SQL:

  • FROM — recupera tutte le righe da una tabella
  • JOIN — esegue un collegamento
  • WHERE — filtra delle righe
  • GROUP BY - forma dei gruppi
  • HAVING - filtra dei gruppi
  • SELECT - seleziona i dati da restituire
  • ORDER BY - ordina le righe di output
  • LIMIT - restituisce un certo numero di righe

Aggregazioni con raggruppamento implicito

L'ultimo argomento che affronterò riguarda le aggregazioni che possono essere eseguite con GROUP BY — o più precisamente le aggregazioni con raggruppamento implicito.

Queste aggregazioni sono utili in casi in cui vuoi trovare una particolare aggregazione in una tabella — come l'ammontare totale delle entrate o il valore più grande o più piccolo di una colonna.

Ad esempio, potremmo trovare il totale delle entrate di tutte le location selezionando la somma dall'intera tabella:

SELECT SUM(prezzo)
FROM vendite;

 sum
-----
  19
(1 row)

Finora abbiamo incassato €19 dalle vendite in tutti i luoghi.

Un'altra cosa utile potrebbe essere ottenere il primo o l'ultimo dato di un qualche tipo.

Ad esempio, qual è la data della prima vendita?

Per trovarla usiamo la funzione MIN():

SELECT MIN(venduto_il)::DATE AS prima_vendita
FROM vendite;

 prima_vendita
--------------
  2020-08-29
(1 row)

Per trovare la data dell'ultima vendita basta sostituire MAX() a MIN().

Usare MIN / MAX

Mentre queste semplici query possono essere utili come query indipendenti, fanno spesso parte del filtraggio di query più ampie.

Ad esempio, proviamo a trovare il totale delle vendite dell'ultimo giorno in cui ci sono state vendite.

Un modo in cui potremmo scrivere questa query è:

SELECT
  SUM(prezzo)
FROM vendite
WHERE venduto_il::DATE = '2020-09-01';

Questa query funziona, ma abbiamo dovuto includere esplicitamente la data 2020-09-01.

09/01/2020 potrebbe essere l'ultima data in cui abbiamo avuto una vendita, ma non sarà sempre quella la data corretta, quindi abbiamo bisogno di una soluzione dinamica.

Possiamo ottenerla combinando questa query con la funzione MAX() in una subquery:

SELECT
  SUM(prezzo)
FROM vendite
WHERE venduto_il::DATE = (
  SELECT MAX(venduto_il::DATE)
  FROM vendite
);

Nella clausola WHERE troviamo la data più recente della tabella con la subquery: SELECT MAX(venduto_il::DATE) FROM vendite.

Poi, usiamo la data trovata per filtrare e sommare i prezzi delle vendite.

Raggruppamento implicito

Ho parlato di raggruppamento implicito perché se proviamo a selezionare dei valori aggregati con una colonna non aggregata in questo modo:

SELECT
  SUM(prezzo),
  luogo
FROM vendite;

otteniamo l'errore ormai familiare:

ERROR:  column "vendite.luogo" must appear in the GROUP BY clause or be used in an aggregate function

GROUP BY è uno strumento

Come molti altri argomenti dello sviluppo software, GROUP BY è uno strumento.

Ci sono molti modi per scrivere e riscrivere queste query usando combinazioni di GROUP BY, funzioni di aggregazione o altri strumenti come DISTINCT, ORDER BY e LIMIT.

Comprendere e lavorare con GROUP BY richiede un po' di pratica, ma una volta che ne hai capito il funzionamento scoprirai di essere in grado di risolvere un'intera serie di nuovi problemi!

Grazie per aver letto questo articolo!