Che cos'è la clausola SQL GROUPING SETS e come si usa?

Gli insiemi di raggruppamenti sono gruppi, o insiemi, di colonne in base ai quali le righe possono essere raggruppate. Invece di scrivere più query e combinare i risultati con una UNION, è possibile utilizzare semplicemente i GROUPING SETS.

GROUPING SETS in SQL può essere considerata un'estensione della clausola GROUP BY. Consente di definire più insiemi di raggruppamenti nella stessa query.

Vediamo la sua sintassi e come può essere equivalente a una GROUP BY con più clausole UNION ALL.

Sintassi degli insiemi di raggruppamenti SQL

La sintassi generale di GROUPING SETS è la seguente:

SELECT
    aggregate_function(column_1)
    column_2,
    column_3,
FROM
    table_name
GROUP BY
    GROUPING SETS (
        (column_2, column_3),
        (column_2),
        (column_3),
        ()
);

Si può notare come stiamo raggruppando in base ai diversi insiemi.

Questa sintassi è equivalente alla seguente query più lunga che utilizza GROUP BY con UNION ALL per combinare i risultati:

SELECT SUM(column_1), column_2, column_3
FROM table_name
GROUP BY
    column_2,
    column_3

UNION ALL

SELECT SUM(column_1), column_2, NULL
FROM table_name
GROUP BY column_2

UNION ALL

SELECT SUM(column_1), NULL, column_3
FROM table_name
GROUP BY column_3

UNION ALL

SELECT SUM(column_1), NULL, NULL
FROM table_name

Se si utilizza GROUP BY in questo modo, sono necessarie più clausole UNION ALL per combinare i dati provenienti da fonti diverse. UNION ALL richiede inoltre che tutti gli insiemi di risultati abbiano lo stesso numero di colonne con tipi di dati compatibili, quindi è necessario adattare le query aggiungendo un valore NULL dove richiesto.

Anche se la query funziona come ci si aspetta, presenta due problemi principali:

• È lunga e poco gestibile.
• Può comportare un problema di prestazioni, perché SQL deve scansionare ogni volta la tabella delle vendite.

La clausola GROUPING SETS risolve questi problemi. Ma come influisce sull'output rispetto alla tradizionale clausola GROUP BY? È ora di vedere un esempio!

Esempio di SQL GROUPING SETS

Abbiamo bisogno di alcuni dati di esempio. Creiamo una tabella chiamata payments che contiene tutti i pagamenti ricevuti dalla nostra azienda nei mesi di gennaio, febbraio e marzo degli ultimi quattro anni, dal 2018 al 2021. Il negozio esatto in cui è avvenuto il pagamento è indicato dalla colonna store_id.

Per creare questa tabella, eseguire la seguente query:

CREATE TABLE payment (payment_amount decimal(8,2), payment_date date, store_id int);

INSERT INTO payment
VALUES
(1200.99, '2018-01-18', 1),
(189.23, '2018-02-15', 1),
(33.43, '2018-03-03', 3),
(7382.10, '2019-01-11', 2),
(382.92, '2019-02-18', 1),
(322.34, '2019-03-29', 2),
(2929.14, '2020-01-03', 2),
(499.02, '2020-02-19', 3),
(994.11, '2020-03-14', 1),
(394.93, '2021-01-22', 2),
(3332.23, '2021-02-23', 3),
(9499.49, '2021-03-10', 3),
(3002.43, '2018-02-25', 2),
(100.99, '2019-03-07', 1),
(211.65, '2020-02-02', 1),
(500.73, '2021-01-06', 3);

È possibile visualizzare i dati utilizzando questa semplice clausola SELECT:

SELECT * FROM payment ORDER BY payment_date;

Eseguendo questa query si ottiene il risultato:

Si può notare che ci sono più voci per alcuni negozi. Immaginiamo di dover preparare un report e di voler vedere un totale per ogni negozio. La funzione aggregata SUM() può aiutarci in questo senso. Utilizzeremo anche la clausola GROUP BY per raggruppare i risultati per anno e negozio.

SELECT
  SUM(payment_amount),
  YEAR(payment_date) AS 'Payment Year',
  store_id AS 'Store'
FROM payment
GROUP BY YEAR(payment_date), store_id
ORDER BY YEAR(payment_date), store_id;

I risultati vengono aggregati per ogni combinazione unica di anno e negozio.

Tuttavia, non possiamo vedere i pagamenti totali per anno: i pagamenti totali per il 2018, 2019, 2020 o 2021. Non possiamo nemmeno vedere i totali per negozio, che sarebbe utile avere. L'utilizzo di GROUPING SETS ci permette di visualizzare questi totali.

SELECT
  SUM(payment_amount),
  YEAR(payment_date) AS 'Payment Year',
  store_id AS 'Store'
FROM payment_new
GROUP BY GROUPING SETS (YEAR(payment_date), store_id)
ORDER BY YEAR(payment_date), store_id;

I risultati sono cambiati drasticamente! Ora vediamo solo i totali generali per ogni negozio e i totali generali per ogni anno.

Per le colonne per le quali le righe non sono raggruppate, vengono visualizzati i valori NULL.

Ricordare che è possibile includere più clausole GROUP BY nella propria GROUPING SETS. Applicando questo metodo alla nostra query si ottiene quanto segue:

SELECT
  SUM(payment_amount),
  YEAR(payment_date) AS 'Payment Year',
  store_id AS 'Store'
FROM payment_new
GROUP BY GROUPING SETS
(
(YEAR(payment_date), store_id),
(YEAR(payment_date)),
(store_id)
)
ORDER BY YEAR(payment_date), store_id;

Prima di concludere questo tutorial, è opportuno menzionare altre due estensioni di SQL GROUP BY che potrebbero rivelarsi utili per un progetto o uno scenario particolare: ROLLUP e CUBE. Questi argomenti sono trattati in modo molto dettagliato in questa traccia di apprendimentoAdvanced SQL da LearnSQL.it che presenta funzioni di finestra, estensioni di GROUP BY e query ricorsive.

Esempio di ROLLUP SQL

Analogamente a GROUPING SETS, è possibile utilizzare l'opzione ROLLUP in una singola query per generare più insiemi di raggruppamenti.

ROLLUP assume una gerarchia tra le colonne di input. Ad esempio, se le colonne di input sono:

GROUP BY ROLLUP(column_1,column_2)

la gerarchia è column_1 > column_2, e ROLLUP genera i seguenti set di raggruppamento:

(column_1, column_2)
(column_1)
()

ROLLUP genera tutti i gruppi di raggruppamento che hanno senso in questa gerarchia. Il programma genera una riga di subtotale ogni volta che il valore di column_1 cambia; questa è la gerarchia che abbiamo fornito. Per questo motivo, spesso si usa ROLLUP per generare subtotali e totali generali nei report. L'ordine delle colonne in ROLLUP è molto importante.

Vediamo una query che utilizza ROLLUP:

SELECT
  SUM(payment_amount),
  YEAR(payment_date) AS 'Payment Year',
  store_id AS 'Store'
FROM payment
GROUP BY ROLLUP (YEAR(payment_date), store_id)
ORDER BY YEAR(payment_date), store_id

Il totale generale è mostrato nella parte superiore del risultato:

Il resto del risultato è strutturato come segue. Innanzitutto, viene mostrato il totale annuale:

Seguono i totali per negozio e per anno:

Come si può notare, ROLLUP genera una riga di subtotale ogni volta che il valore di Payment Year cambia, poiché questa è la gerarchia che abbiamo fornito. Questo esempio mostra quanto possa essere utile ROLLUP per la creazione di report.

Esempio di SQL CUBE

Simile a ROLLUP, CUBE è un'estensione della clausola GROUP BY. Consente di generare subtotali per tutte le combinazioni delle colonne di raggruppamento specificate nella clausola GROUP BY.

La clausola CUBE è come una combinazione di GROUPING SETS e ROLLUP. Mostra l'output dettagliato di entrambe.

SELECT
  SUM(payment_amount),
  YEAR(payment_date) AS 'Payment Year',
  store_id AS 'Store'
FROM payment
GROUP BY CUBE (YEAR(payment_date), store_id)
ORDER BY YEAR(payment_date), store_id

La differenza principale di questo output rispetto all'esempio ROLLUP è che qui viene mostrato anche il totale generale per ogni negozio.

A parte queste righe, tutte le righe di questo risultato sono uguali a quelle di ROLLUP.

Questo conclude il nostro confronto tra GROUPING SETS, ROLLUP e CUBE! In questo articolo troverete altri esempi di raggruppamento, rotazione e cubatura dei dati.

Raggruppare i dati in modo efficace con le estensioni GROUP BY di SQL

La padronanza delle estensioni SQL GROUP BY richiede un po' di pratica. Opzioni come GROUPING SETS, ROLLUP e CUBE consentono di manipolare i risultati delle query in modi diversi. Sapere come usare queste estensioni in modo efficace riduce la necessità di formattare manualmente i dati prima di trasmetterli agli stakeholder.

Per approfondire le vostre conoscenze in quest'area, prendete in considerazione questo corso sulle estensioni GROUP BY di LearnSQL.it che tratta GROUPING SETS, ROLLUP e CUBE.