Transact-SQL: esempi di ROLLUP e GROUPING
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.
Leave a Reply