Il blog di Nicola

Ecco degli esempi della funzione di aggregazione GROUPING associata all’utilizzo del GROUP BY WITH ROLLUP

GROUPING è una funzione di aggregazione che aggiunge una colonna al resultset, valorizzata ad 1 per ogni record aggiunto dalla clausola  CUBE o ROLLUP.

Questi esempi si applicano a SQL Server 2005. Le clausole ROLLUP e CUBE, e la funzione GROUPING sono presenti anche in altri DBMS.

Creiamo una tabella ORDINI con questi campi:

CREATE TABLE [ORDINI] (
[ID_ORDINE] [int] NOT NULL ,
[PRODOTTO] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[QTA] [int] NULL ,
[PREZZO] [money] NULL ,
CONSTRAINT [PK_ORDINI] PRIMARY KEY CLUSTERED
(
[ID_ORDINE],
[PRODOTTO]
) ON [PRIMARY]
) ON [PRIMARY]
GO


con i seguenti dati (con prezzi casuali):

INSERT INTO ORDINI VALUES (1, 'PS3', 1, 249.99)
INSERT INTO ORDINI VALUES (2, 'PS3', 1, 249.99)
INSERT INTO ORDINI VALUES (2, 'XBOX', 2, 499.99)
INSERT INTO ORDINI VALUES (3, 'WII', 1, 149.99)
INSERT INTO ORDINI VALUES (3, 'PS3', 1, 249.99)
INSERT INTO ORDINI VALUES (3, 'XBOX', 1, 199.99)

Quindi, tanto per dire, l’ordine 1 composto da una PS3, l’ordine 2 composto da una PS3 e due XBOX e l’ordine 3 con una WII, una PS3 e una XBOX.
Il risultato è questo:

SELECT * FROM ORDINI

ID_ORDINE	PRODOTTO	QTA	PREZZO
1	PS3	1	249.9900
2	PS3	1	249.9900
2	XBOX	2	499.9900
3	PS3	1	249.9900
3	WII	1	149.9900
3	XBOX	1	199.9900

Se eseguiamo un normale GROUP BY sul prodotto otteniamo:

SELECT PRODOTTO, SUM(QTA) AS QUANTITA, SUM(PREZZO) AS PREZZO
FROM ORDINI
GROUP BY PRODOTTO

PRODOTTO	QTA	PREZZO
PS3	3	749.9700
WII	1	149.9900
XBOX	3	699.9800

Aggiungendo la clausola WITH ROLLUP sul prodotto otteniamo la riga aggiuntiva con la somma del GROUP BY relativo (evidenziata in grassetto):

SELECT PRODOTTO, SUM(QTA) AS QUANTITA, SUM(PREZZO) AS PREZZO
FROM ORDINI
GROUP BY PRODOTTO WITH ROLLUP

PRODOTTO	QTA	PREZZO
PS3	3	749.9700
WII	1	149.9900
XBOX	3	699.9800
NULL	7	1599.9400

Adesso vediamo l’effetto della funzione GROUPING sul campo PRODOTTO, e vediamo che la colonna ‘GROUPING PRODOTTO è valorizzata ad 1 solo per il record aggiunto dal WITH ROLLUP:

SELECT PRODOTTO, SUM(QTA) AS QUANTITA, SUM(PREZZO) AS PREZZO, GROUPING(PRODOTTO) AS 'GROUPING PRODOTTO'
FROM ORDINI
GROUP BY PRODOTTO WITH ROLLUP

PRODOTTO	QTA	PREZZO	GROUPING PRODOTTO
PS3	3	749.9700	0
WII	1	149.9900	0
XBOX	3	699.9800	0
NULL	7	1599.9400	1

Adesso raggruppiamo oltre che per prodotto anche per id dell’ordine:

SELECT PRODOTTO, ID_ORDINE, SUM(QTA) AS QUANTITA, SUM(PREZZO) AS PREZZO
FROM ORDINI
GROUP BY PRODOTTO, ID_ORDINE

PRODOTTO	ID_ORDINE	QTA	PREZZO
PS3	1	1	249.9900
PS3	2	1	249.9900
XBOX	2	2	499.9900
PS3	3	1	249.9900
WII	3	1	149.9900
XBOX	3	1	149.9900

E aggiungiamo il ROLLUP. Ogni raggruppamento genera una riga di totale:

SELECT PRODOTTO, ID_ORDINE, SUM(QTA) AS QUANTITA, SUM(PREZZO) AS PREZZO
FROM ORDINI
GROUP BY PRODOTTO, ID_ORDINE WITH ROLLUP

PRODOTTO	ID_ORDINE	QTA	PREZZO
PS3	1	1	249.9900
PS3	2	1	249.9900
PS3	3	1	249.9900
PS3	NULL	3	749.9700
WII	3	1	149.9900
WII	NULL	1	149.9900
XBOX	2	2	499.9900
XBOX	3	1	199.9900
XBOX	NULL	3	699.9800
NULL	NULL	7	1599.9400

E adesso aggiungiamo due GROUPING, uno per PRODOTTO e uno per ID_ORDINE. Ecco il risultato.

SELECT PRODOTTO, ID_ORDINE, SUM(QTA) AS QUANTITA, SUM(PREZZO) AS PREZZO, GROUPING(ID_ORDINE) AS 'GROUPING ORDINE', GROUPING(PRODOTTO) AS 'GROUPING PRODOTTO'
FROM ORDINI
GROUP BY PRODOTTO, ID_ORDINE WITH ROLLUP

PRODOTTO	ID_ORDINE	QTA	PREZZO	GROUPING ORDINE	GROUPING PRODOTTO
PS3	1	1	249.9900	0	0
PS3	2	1	249.9900	0	0
PS3	3	1	249.9900	0	0
PS3	NULL	3	749.9700	1	0
WII	3	1	149.9900	0	0
WII	NULL	1	149.9900	1	0
XBOX	2	2	499.9900	0	0
XBOX	3	1	199.9900	0	0
XBOX	NULL	3	699.9800	1	0
NULL	NULL	7	1599.9400	1	1

Nella stessa query abbiamo, per ogni prodotto, una riga col dettaglio per ogni ordine, ed una riga di totale, più una riga di totale generale.

Transact-SQL: L’operatore ROLLUP

L’operatore ROLLUP aggiunge ai record generati dal GROUP BY i totali relativi ai raggruppamenti, ed il totale generale dei valori calcolati. Non costruisce l’intero cubo su tutte le combinazioni dei campi inseriti nel GROUP BY, ma segue l’ordine del GROUP BY in questo modo:

SELECT , , SUM(
FROM
GROUP BY ,, WITH ROLLUP


Come per il CUBE questa versione viene definita Non-ISO-Compliant dalla Microsoft e da SQL Server 2008 la sua sintassi è:

SELECT , , SUM(
FROM
GROUP BY ROLLUP(,,)


Riprendendo lo stesso esempio fatto il CUBE:

CREATE TABLE VENDITE (AGENTE VARCHAR(20), PRODOTTO VARCHAR(20), PAESE VARCHAR(20), QUANTITA INT)

INSERT INTO VENDITE VALUES ('AGENTE 1', 'PS3', 'ITALIA', 20)
INSERT INTO VENDITE VALUES ('AGENTE 1', 'PS3', 'SPAGNA', 30)
INSERT INTO VENDITE VALUES ('AGENTE 1', 'PS3', 'FRANCIA', 10)
INSERT INTO VENDITE VALUES ('AGENTE 1', 'PS3', 'GERMANIA', 15)
INSERT INTO VENDITE VALUES ('AGENTE 1', 'WII', 'ITALIA', 10)
INSERT INTO VENDITE VALUES ('AGENTE 1', 'WII', 'SPAGNA', 25)
INSERT INTO VENDITE VALUES ('AGENTE 1', 'WII', 'INGHILTERRA', 8)
INSERT INTO VENDITE VALUES ('AGENTE 1', 'WII', 'GERMANIA', 35)

INSERT INTO VENDITE VALUES ('AGENTE 2', 'PS3', 'ITALIA', 22)
INSERT INTO VENDITE VALUES ('AGENTE 2', 'PS3', 'SPAGNA', 32)
INSERT INTO VENDITE VALUES ('AGENTE 2', 'PS3', 'FRANCIA', 16)
INSERT INTO VENDITE VALUES ('AGENTE 2', 'PS3', 'GERMANIA', 19)
INSERT INTO VENDITE VALUES ('AGENTE 2', 'WII', 'ITALIA', 23)
INSERT INTO VENDITE VALUES ('AGENTE 2', 'WII', 'SPAGNA', 14)
INSERT INTO VENDITE VALUES ('AGENTE 2', 'WII', 'INGHILTERRA', 7)
INSERT INTO VENDITE VALUES ('AGENTE 2', 'WII', 'GERMANIA', 33)


dove ogni Agente figura con la quantità venduta per prodotto e nazione.

La query

SELECT AGENTE, PRODOTTO, PAESE, SUM(QUANTITA)
FROM VENDITE
GROUP BY AGENTE, PRODOTTO, PAESE WITH ROLLUP


restituisce un record con la somma del campo quantità per ogni raggruppamento in ordine di GROUP BY, quindi (AGENTE) e (AGENTE/PRODOTTO), visto che (AGENTE/PRODOTTO/PAESE) è una totalizzazione che viene già generata dal GROUP BY. Ecco il risultato (in grassetto i campi aggiunto da WITH ROLLUP):

AGENTE 1 PS3 FRANCIA     10
AGENTE 1 PS3 GERMANIA    15
AGENTE 1 PS3 ITALIA      20
AGENTE 1 PS3 SPAGNA      30
AGENTE 1 PS3 NULL        75
AGENTE 1 WII GERMANIA    35
AGENTE 1 WII INGHILTERRA 8
AGENTE 1 WII ITALIA      10
AGENTE 1 WII SPAGNA      25
AGENTE 1 WII NULL        78
AGENTE 1 NULL NULL       153
AGENTE 2 PS3 FRANCIA     16
AGENTE 2 PS3 GERMANIA    19
AGENTE 2 PS3 ITALIA      22
AGENTE 2 PS3 SPAGNA      32
AGENTE 2 PS3 NULL        89
AGENTE 2 WII GERMANIA    33
AGENTE 2 WII INGHILTERRA 7
AGENTE 2 WII ITALIA      23
AGENTE 2 WII SPAGNA      14
AGENTE 2 WII NULL        77
AGENTE 2 NULL NULL       166
NULL NULL NULL           319


Quindi, per ogni AGENTE:

AGENTE 1 NULL NULL 153
AGENTE 2 NULL NULL 166


Per ogni raggruppamento AGENTE/PRODOTTO:

AGENTE 1 PS3 NULL 75
AGENTE 1 WII NULL 78
AGENTE 2 PS3 NULL 89
AGENTE 2 WII NULL 77


E infine è presente anche il totale dei totali:

NULL NULL NULL 319


Transact-SQL: L’operatore CUBE

L’operatOre CUBE restituisce un cubo multidimensionale costruito sui campi di un aggregazione GROUP BY, in questo modo:

SELECT CAMPO1, CAMPO2 , CAMPO3, SUM(VALORE)
FROM TABELLA
GROUP BY CAMPO1, CAMPO2, CAMPO3 WITH CUBE


Questa versione viene definita Non-ISO-Compliant dalla Microsoft e da SQL Server 2008 la sua sintassi è:

SELECT CAMPO1, CAMPO2 , CAMPO3, SUM(VALORE)
FROM TABELLA
GROUP BY CUBE(CAMPO1, CAMPO2, CAMPO3)


Il risultato è la creazione di un cubo multidimensionale (una dimensione per ogni campo presente nel CUBE) contenente le sommarizzazioni su tutte combinazioni possibili con gli altri campi.
Un esempio pratico:

CREATE TABLE VENDITE (AGENTE VARCHAR(20), PRODOTTO VARCHAR(20), PAESE VARCHAR(20), QUANTITA INT)

INSERT INTO VENDITE VALUES ('AGENTE 1', 'PS3', 'ITALIA', 20)
INSERT INTO VENDITE VALUES ('AGENTE 1', 'PS3', 'SPAGNA', 30)
INSERT INTO VENDITE VALUES ('AGENTE 1', 'PS3', 'FRANCIA', 10)
INSERT INTO VENDITE VALUES ('AGENTE 1', 'PS3', 'GERMANIA', 15)
INSERT INTO VENDITE VALUES ('AGENTE 1', 'WII', 'ITALIA', 10)
INSERT INTO VENDITE VALUES ('AGENTE 1', 'WII', 'SPAGNA', 25)
INSERT INTO VENDITE VALUES ('AGENTE 1', 'WII', 'INGHILTERRA', 8)
INSERT INTO VENDITE VALUES ('AGENTE 1', 'WII', 'GERMANIA', 35)

INSERT INTO VENDITE VALUES ('AGENTE 2', 'PS3', 'ITALIA', 22)
INSERT INTO VENDITE VALUES ('AGENTE 2', 'PS3', 'SPAGNA', 32)
INSERT INTO VENDITE VALUES ('AGENTE 2', 'PS3', 'FRANCIA', 16)
INSERT INTO VENDITE VALUES ('AGENTE 2', 'PS3', 'GERMANIA', 19)
INSERT INTO VENDITE VALUES ('AGENTE 2', 'WII', 'ITALIA', 23)
INSERT INTO VENDITE VALUES ('AGENTE 2', 'WII', 'SPAGNA', 14)
INSERT INTO VENDITE VALUES ('AGENTE 2', 'WII', 'INGHILTERRA', 7)
INSERT INTO VENDITE VALUES ('AGENTE 2', 'WII', 'GERMANIA', 33)


dove ogni Agente figura con la quantità venduta per prodotto e nazione.

La query

SELECT AGENTE, PRODOTTO, PAESE, SUM(QUANTITA)
FROM VENDITE
GROUP BY AGENTE, PRODOTTO, PAESE WITH CUBE


restituisce un record con la somma del campo quantità per ogni possile combinazione dei campi AGENTE, PRODOTTO e PAESE, ovvero dei campi convolti nel group by (in grassetto i record aggiunti da WITH CUBE):

AGENTE 1 PS3 FRANCIA 10
AGENTE 1 PS3 GERMANIA 15
AGENTE 1 PS3 ITALIA 20
AGENTE 1 PS3 SPAGNA 30
AGENTE 1 PS3 NULL 75
AGENTE 1 WII GERMANIA 35
AGENTE 1 WII INGHILTERRA 8
AGENTE 1 WII ITALIA 10
AGENTE 1 WII SPAGNA 25
AGENTE 1 WII NULL 78
AGENTE 1 NULL NULL 153
AGENTE 2 PS3 FRANCIA 16
AGENTE 2 PS3 GERMANIA 19
AGENTE 2 PS3 ITALIA 22
AGENTE 2 PS3 SPAGNA 32
AGENTE 2 PS3 NULL 89
AGENTE 2 WII GERMANIA 33
AGENTE 2 WII INGHILTERRA 7
AGENTE 2 WII ITALIA 23
AGENTE 2 WII SPAGNA 14
AGENTE 2 WII NULL 77
AGENTE 2 NULL NULL 166
NULL NULL NULL 319
NULL PS3 FRANCIA 26
NULL PS3 GERMANIA 34
NULL PS3 ITALIA 42
NULL PS3 SPAGNA 62
NULL PS3 NULL 164
NULL WII GERMANIA 68
NULL WII INGHILTERRA 15
NULL WII ITALIA 33
NULL WII SPAGNA 39
NULL WII NULL 155
AGENTE 1 NULL FRANCIA 10
AGENTE 2 NULL FRANCIA 16
NULL NULL FRANCIA 26
AGENTE 1 NULL GERMANIA 50
AGENTE 2 NULL GERMANIA 52
NULL NULL GERMANIA 102
AGENTE 1 NULL INGHILTERRA 8
AGENTE 2 NULL INGHILTERRA 7
NULL NULL INGHILTERRA 15
AGENTE 1 NULL ITALIA 30
AGENTE 2 NULL ITALIA 45
NULL NULL ITALIA 75
AGENTE 1 NULL SPAGNA 55
AGENTE 2 NULL SPAGNA 46
NULL NULL SPAGNA 101


Quindi, per ogni coppia AGENTE/PRODOTTO esiste un record che riporta il totale su tutti i paesi:

AGENTE 1 PS3 NULL 75
AGENTE 1 WII NULL 78
AGENTE 2 PS3 NULL 89
AGENTE 2 WII NULL 77


Per ogni coppia AGENTE/NAZIONE esiste un record che riporta il totale su tutti i prodotti:

AGENTE 1 NULL FRANCIA 10
AGENTE 2 NULL FRANCIA 16
AGENTE 1 NULL GERMANIA 50
AGENTE 2 NULL GERMANIA 52
AGENTE 1 NULL INGHILTERRA 8
AGENTE 2 NULL INGHILTERRA 7
AGENTE 1 NULL ITALIA 30
AGENTE 2 NULL ITALIA 45
AGENTE 1 NULL SPAGNA 55
AGENTE 2 NULL SPAGNA 46


Per ogni coppia PRODOTTO/NAZIONE esiste un record che riporta il totale su tutti i prodotti:

NULL PS3 FRANCIA 26
NULL PS3 GERMANIA 34
NULL PS3 ITALIA 42
NULL PS3 SPAGNA 62
NULL PS3 NULL 164
NULL WII GERMANIA 68
NULL WII INGHILTERRA 15
NULL WII ITALIA 33
NULL WII SPAGNA 39
NULL WII NULL 155


Il totale per ogni AGENTE;

AGENTE 1 NULL NULL 153
AGENTE 2 NULL NULL 166


Il totale per ogni PRODOTTO

NULL PS3 NULL 164
NULL WII NULL 155


E il totale per ogni paese

NULL NULL FRANCIA 26
NULL NULL GERMANIA 102
NULL NULL INGHILTERRA 15
NULL NULL ITALIA 75
NULL NULL SPAGNA 101


E infine è presente anche il totale dei totali:

NULL NULL NULL 319


Nota: Il numero di record creati da CUBE sono 2^n ovvero il numero di combinazioni non ripetute + 1 record di totale.