Transact-SQL: L’operatore CUBE

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.