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  CUBEROLLUP.

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

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>