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