Clausola GROUP BY: Quanto la conoscete?

Il database può eseguire diversi tipi di calcoli: può sommare e sottrarre, moltiplicare e dividere, può eseguire calcoli sulle date, può contare le righe e sommare i valori delle righe e così via. Può anche eseguire statistiche piuttosto sofisticate. La clausola GROUP BY è il modo più semplice per calcolare le statistiche in SQL. Può essere piuttosto difficile per i principianti, ma è davvero potente. Vediamo i dettagli della clausola GROUP BY, partendo dalle basi.

Le basi: come funziona GROUP BY

Abbiamo una tabella con le medaglie della Coppa del Mondo di salto con gli sci delle ultime quattro stagioni.

 country |         person        | season  | place
---------+-----------------------+---------+-------
Norway   | Anders Bardal         | 2011-12 |   1
Austria  | Gregor Schlierenzauer | 2011-12 |   2
Austria  | Andreas Kofler        | 2011-12 |   3
Austria  | Gregor Schlierenzauer | 2012-13 |   1
Norway   | Anders Bardal         | 2012-13 |   2
Poland   | Kamil Stoch           | 2012-13 |   3
Poland   | Kamil Stoch           | 2013-14 |   1
Slovenia | Peter Prevc           | 2013-14 |   2
Germany  | Severin Freund        | 2013-14 |   3
Germany  | Severin Freund        | 2014-15 |   1
Slovenia | Peter Prevc           | 2014-15 |   2
Austria  | Stefan Kraft          | 2014-15 |   3

Voglio scoprire quante medaglie ha ottenuto la Polonia:

SELECT count(*)
FROM medals
WHERE country = 'Poland';

Se volessi scoprire il numero di medaglie della Germania, dovrei fare questa query:

SELECT count(*)
FROM medals
WHERE country = 'Germany';

Se volessi scoprire il numero di medaglie per ogni Paese, potrei fare sei interrogazioni simili. Oppure potrei usare un GROUP BY.

SELECT country, count(*)
FROM medals
GROUP BY country;

La clausola GROUP BY viene subito dopo la clausola WHERE nelle query SQL. In questo caso, la clausola WHERE manca, quindi è subito dopo FROM.

Il risultato:

 country | count
---------+-------
Poland   |   2
Germany  |   2
Austria  |   4
Norway   |   2
Slovenia |   2

Con la query GROUP BY il database divide i dati in gruppi. Le righe con la stessa colonna GROUP BY (paese nell'esempio) vengono inserite in un unico gruppo. Quindi, utilizzando il nostro esempio, i medagliati della Polonia vengono inseriti in un gruppo, quelli della Germania in un altro e così via. Ecco il raggruppamento ottenuto con questa query:

 country |        person         | season  | place
–--------+-----------------------+---------+-------
Poland   | Kamil Stoch           | 2012-13 |   3
         | Kamil Stoch           | 2013-14 |   1
–--------+-----------------------+---------+-------
Germany  | Severin Freund        | 2013-14 |   3
         | Severin Freund        | 2014-15 |   1
–--------+-----------------------+---------+-------
Austria  | Gregor Schlierenzauer | 2012-13 |   1
         | Stefan Kraft          | 2014-15 |   3
         | Gregor Schlierenzauer | 2011-12 |   2
         | Andreas Kofler        | 2011-12 |   3
–--------+-----------------------+---------+-------
Norway   | Anders Bardal         | 2012-13 |   2
         | Anders Bardal         | 2011-12 |   1
–--------+-----------------------+---------+-------
Slovenia | Peter Prevc           | 2013-14 |   2
         | Peter Prevc           | 2014-15 |   2
–--------+-----------------------+---------+-------

Con GROUP BY gli aggregati (count, sum, avg, min, max e altri) vengono calcolati per ogni singolo gruppo. Nell'esempio, il database conta il numero di righe in ciascun gruppo.

Raggruppamento con più colonne

È possibile raggruppare le righe in base a più colonne.

Ad esempio, se si vuole scoprire quante medaglie ha ottenuto ogni nazione in ogni stagione, la query potrebbe essere simile a questa:

SELECT country, season, count(*)
FROM medals
GROUP BY country, season;

Le righe con lo stesso Paese e la stessa stagione vengono inserite in un gruppo. Il raggruppamento ha questo aspetto:

 country | season  |        person         | place
–--------+---------+-----------------------+-------
Poland   | 2012-13 | Kamil Stoch           |   3
–--------+---------+-----------------------+-------
Poland   | 2013-14 | Kamil Stoch           |   1
–--------+---------+-----------------------+-------
...
–--------+---------+-----------------------+-------
Austria  | 2011-12 | Gregor Schlierenzauer |   2
         | 2011-12 | Andreas Kofler        |   3
–--------+---------+-----------------------+------

Il risultato finale:

 country | season  | count
---------+---------+-------
 Poland  | 2012-13 |   1
 Austria | 2011-12 |   2
 ...
 Poland  | 2013-14 |   1

Valori NULL in GROUP BY

Un promemoria: Nella condizione WHERE non vengono considerati due NULL uguali. Per quanto strano possa sembrare, la query

SELECT *
FROM medals
WHERE place = place;

selezionerà tutte le righe tranne quelle con posto NULL. Per SQL il valore NULL significa "Sconosciuto" e se è sconosciuto, SQL non può presumere di sapere con certezza quale sarà il suo risultato. (In particolare, non può avere la certezza che il risultato sia VERO).

Con GROUP BY è diverso. Le righe con valori NULL vanno tutte in un gruppo e gli aggregati vengono calcolati per questo gruppo, come per qualsiasi altro. Funziona anche con i GROUP BY a più colonne.

Per questa tabella:

    country    |   city  |    person    | earnings
---------------+---------+--------------+----------
 NULL          | Warsaw  | John Doe     |   1000
 United States | NULL    | Maria Jane   |   1000
 Germany       | Berlin  | Hans Schmitt |   2430
 United States | NULL    | Bill Noir    |   1000
 United States | Chicago | Rob Smith    |   3000
 NULL          | Warsaw  | Sophie Doe   |   2000
 Germany       | Berlin  | Jane Dahl    |   1500

la query

SELECT country, city, sum(earnings)
FROM employees
GROUP BY country, city;

rende questi gruppi:

    country    |   city  |    person    | earnings
---------------+---------+--------------+----------
 NULL          | Warsaw  | John Doe     |   1000
               |         | Sophie Doe   |   2000
–--------------------------------------------------
 United States | NULL    | Maria Jane   |   1000
               |         | Bill Noir    |   1000
–--------------------------------------------------
 United States | Chicago | Rob Smith    |   3000
–--------------------------------------------------
 Germany       | Berlin  | Hans Schmitt |   2430
               |         | Jane Dahl    |   1500

e questo risultato

    country    |   city  |  sum
---------------+---------+-------
 NULL          | Warsaw  |  3000
 United States | NULL    |  2000
 United States | Chicago |  3000
 Germany       | Berlin  |  3930

Mito: Le colonne selezionate devono apparire nella clausola GROUP BY o sotto una funzione aggregata?

La saggezza comune dice che le colonne selezionate in una query GROUP BY devono apparire nella clausola GROUP BY o sotto una funzione aggregata. Quindi questa query non è corretta:

SELECT user_account.id, email, count(*)
FROM user_account JOIN address
ON user_account.id = address.user_id
GROUP BY email;

La query raggruppa i risultati per e-mail, ma seleziona la colonna id, che non è presente nella clausola GROUP BY.

Questa saggezza era la regola dello standard SQL92. È così che molti database implementano oggi il comportamento GROUP BY. Si otterrà un errore simile a questo:

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

Da dove deriva questa regola? Vediamo un esempio di dati.

|       user_account      |   |         address         |
+----+--------------------+   +----+----------+---------+
| id |        Email       |   | id |   city   | user_id |
+----+--------------------+   +----+----------+---------+
| 1  | john@example.com   |   | 1  | London   | 1       |
| 2  | mary@example.co.uk |   | 2  | Brussels | 2       |
| 3  | john@example.com   |   | 3  | Cairo    | 3       |
|    |                    |   | 4  | Dublin   | 1       |

Raggruppiamo i dati per e-mail

 
user_account.email |user_account.id |address.id|address.city|address.user_id|
-------------------+----------------+----------+------------+---------------+
john@example.com   | 1              | 1        | A          | 1             |
                   +----------------+----------+------------+---------------+
                   | 1              | 4        | D          | 1             |
                   +----------------+----------+------------+---------------+
                   | 3              | 3        | C          | 3             |
-------------------+----------------+----------+------------+---------------+
mary@example.com   | 2              | 2        | B          | 2             |

Il database crea un gruppo per ogni e-mail. Ma ci sono più id di account_utente in ogni gruppo. Il database non sa quale id deve restituire. Lo standard SQL vuole che il risultato SQL sia deterministico, quindi vieta di eseguire una query come questa.

Lo standard SQL99 ha modificato la formulazione della regola. Ora dice che qualsiasi colonna che appare sotto SELECT deve apparire sotto la funzione aggregata o essere funzionalmente dipendente dalle colonne nella clausola GROUP BY. La regola non prevede più che tutte le colonne non aggregate siano ripetute nella clausola GROUP BY.

Cosa significa colonne funzionalmente dipendenti nella clausola BY by? Significa che se si fissano dei valori per le colonne nella clausola GROUP BY, deve esserci un solo valore per l'altra colonna. Ad esempio, l'indirizzo e-mail determina il valore del nome del suo proprietario. Ma c'è un problema: il database deve essere a conoscenza di questa dipendenza. Nel contesto dei database, la dipendenza si traduce in chiavi primarie e chiavi uniche. Se raggruppo per una chiave primaria, so che le altre colonne della tabella hanno valori fissi.

Il nostro esempio iniziale non è ancora valido in base alla nuova regola. MA: se impongo il vincolo di unicità sulla colonna email, la query diventa valida in base alla nuova regola. Se la colonna email è unica nella tabella user_account, la fissazione del valore dell'email determina tutte le altre colonne della tabella user_account. Naturalmente, se aggiungo il vincolo di unicità, anche i miei dati di esempio non sono validi. Non posso avere due righe diverse con la stessa e-mail.

I database supportano la nuova regola GROUP BY?

Alcuni sì, altri no. La nuova regola è contenuta nello standard SQL99. MySQL dalla versione 5.7.4 supporta il nuovo comportamento. Anche Postgres dalla versione 9.1. Postgres considera la colonna come funzionalmente dipendente dalle colonne raggruppate se un sottoinsieme delle colonne raggruppate è una chiave primaria della tabella da cui proviene la colonna.

Per quanto ne so, Oracle e SQL Server si attengono ancora alla vecchia versione.

Dovreste usare la nuova versione o la vecchia versione della regola nelle vostre query? In pratica, questa modifica non cambia nulla. Entrambe le regole garantiscono che ogni volta che si seleziona una colonna non aggregata in una query GROUP BY, il suo valore sia univoco in ogni gruppo. La vecchia regola obbliga ad aggiungere la colonna nella clausola GROUP BY, ma questo GROUP BY non cambia la semantica della query. Il risultato è lo stesso, solo che con la nuova regola si deve digitare un po' di più.

In generale, è meglio attenersi alla vecchia regola. Le query verranno eseguite sulla maggior parte dei database. Ma è bene sapere che non è necessario.

Estensioni di GROUP BY

SQL-99 ha aggiunto ROLLUP, CUBE e GROUPING SETS come opzioni per le istruzioni SELECT.

GROUP BY ROLLUP

La sintassi di ROLLUP è

SELECT <columns>
FROM <tables>
WHERE <condition>
GROUP BY ROLLUP (<group-by columns>);

L'uso di ROLLUP (a,b,c) genera le clausole GROUP BY: (a, b, c), (a, b), (a) e una riga per l'aggregazione di tutte le righe selezionate. È equivalente a quattro query SELECT con diverse clausole GROUP BY.

Per questa tabella

department | year | sales
–----------+--------------
 IT        | 2012 |  25000
 IT        | 2013 |  26000
 Retail    | 2012 |  35000
 Retail    | 2013 |  15000
 IT        | 2014 |  18000

e questa query

SELECT department, year, sum(sales)
FROM sales
GROUP BY ROLLUP (department, year);

otteniamo il risultato:

 department | year |   sum   
------------+------+--------
 IT         | 2012 |   25000
 IT         | 2014 |   18000
 IT         | 2013 |   26000
 IT         | NULL |   69000 <-  group by department
 Retail     | 2013 |   15000
 Retail     | 2012 |   35000
 Retail     | NULL |   50000 <-  group by department
 NULL       | NULL |  119000 <-  group by (), i.e. all rows selected

Le righe aggiuntive sono talvolta chiamate superaggregati.

ROLLUP è supportato da SQL Server, Oracle e DB2.

In MySQL è possibile utilizzare la sintassi WITH ROLLUP:

SELECT <columns>
FROM <tables>
WHERE <condition>
GROUP BY a,b,c WITH ROLLUP;

PostgreSQL non supporta ROLLUP.

GROUP BY CUBE

La sintassi per CUBE è

SELECT <columns>
FROM <tables>
WHERE <condition>
GROUP BY CUBE (a, b, c);

Funziona in modo simile a ROLLUP, ma genera tutte le possibili combinazioni di colonne: (a,b,c), (a,b), (a,c), (b,c), (a), (b), (c) e una riga per tutte le righe selezionate.

La query

SELECT department, year, sum(sales)
FROM sales
GROUP BY CUBE (department, year);

restituirà questo risultato:

 department | year |   sum   
------------+------+--------
 IT         | 2012 |   25000
 IT         | 2014 |   18000
 IT         | 2013 |   26000
 IT         | NULL |   69000  <-  group by department
 Retail     | 2013 |   15000
 Retail     | 2012 |   35000
 Retail     | NULL |   50000  <-  group by department
 NULL       | NULL |  119000  <-  group by ()
 NULL       | 2014 |   18000 |
 NULL       | 2012 |   60000 | <= three new rows added by CUBE
 NULL       | 2013 |   41000 |    

CUBE è supportato da SQL Server, Oracle e DB2. MySQL e Postgres non lo supportano.

GROUP BY GROUPING SETS

GROUPING SETS funziona in modo simile, ma consente di specificare quali combinazioni di colonne devono essere utilizzate nel risultato. Gli insiemi di raggruppamento devono essere separati da virgole. Se c'è più di una colonna in un insieme di raggruppamenti, questo insieme di raggruppamenti deve essere messo tra parentesi. Le parentesi vuote indicano il record generale con gli aggregati per l'intero insieme.

Esempio di query:

SELECT <columns>
FROM <tables>
WHERE <condition>
GROUP BY GROUPING SETS ((a, b), c, ());

GROUPING SETS è supportato da SQL Server, Oracle e DB2. MySQL e Postgres non lo supportano.