Riepilogo dei dati con l'operatore CUBE
L'operatore CUBE genera un set di risultati in forma di cubo multidimensionale. Un cubo multidimensionale è un'espansione di dati di una tabella fatti o dati che registrano singoli eventi. L'espansione è basata sulle colonne che l'utente desidera analizzare, denominate dimensioni. Il cubo è un set di risultati contenente una tabella incrociata di tutte le possibili combinazioni delle dimensioni.
L'operatore CUBE viene specificato nella clausola GROUP BY di un'istruzione SELECT. L'elenco di selezione include le colonne delle dimensioni ed espressioni con funzioni di aggregazione. La clausola GROUP BY specifica le colonne delle dimensioni e le parole chiave WITH CUBE. Il set di risultati include tutte le possibili combinazioni dei valori contenuti nelle colonne delle dimensioni, nonché i valori di aggregazione delle righe sottostanti corrispondenti a ogni combinazione.
Una semplice tabella Inventory, ad esempio, contiene i valori seguenti:
Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 124
Table Red 223
Chair Blue 101
Chair Red 210
La query seguente restituisce un set di risultati che include il subtotale Quantity per tutte le combinazioni possibili di Item e Color:
SELECT Item, Color, SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE
Set di risultati:
Item Color QtySum
-------------------- -------------------- --------------------------
Chair Blue 101.00
Chair Red 210.00
Chair (null) 311.00
Table Blue 124.00
Table Red 223.00
Table (null) 347.00
(null) (null) 658.00
(null) Blue 225.00
(null) Red 433.00
Le righe del set di risultati riportate di seguito sono particolarmente interessanti:
Chair (null) 311.00
In questa riga viene indicato il subtotale di tutte le righe che includono il valore Chair nella dimensione Item. Per la dimensione Color viene restituito il valore null, a indicare che l'aggregazione della riga contiene altre righe con un valore della dimensione Color.
Table (null) 347.00
Questa riga è simile, ma indica il subtotale di tutte le righe che includono il valore Table nella dimensione Item.
(null) (null) 658.00
Questa riga indica il totale complessivo del cubo. Entrambe le dimensioni Item e Color includono il valore null. Ciò indica che tutti i valori di entrambe le dimensioni vengono riepilogati nella riga.
(null) Blue 225.00
(null) Red 433.00
Queste due righe includono i subtotali della dimensione Color. Entrambe includono il valore null nella dimensione Item, a indicare che i dati di aggregazione provengono da righe contenenti qualsiasi valore relativo alla dimensione Item.
Utilizzo della funzione GROUPING per distinguere i valori Null
I valori Null generati dall'operazione CUBE presentano un problema, ovvero la difficoltà di distinguere un valore Null generato dall'operazione CUBE da un valore Null restituito nei dati effettivi. Per risolvere questo problema, è possibile utilizzare la funzione GROUPING. La funzione GROUPING restituisce 0 se il valore della colonna proviene dalla tabella dei fatti, mentre restituisce 1 se si tratta di un valore Null generato da un'operazione CUBE. Un valore Null generato da un'operazione CUBE rappresenta tutti i valori. È possibile formulare l'istruzione SELECT affinché venga utilizzata la funzione GROUPING per sostituire con la stringa ALL tutti i valori Null generati. Poiché un valore Null proveniente dalla tabella dei fatti indica che il valore dei dati è sconosciuto, è possibile programmare l'istruzione SELECT in modo che restituisca la stringa UNKNOWN in corrispondenza di ogni valore Null proveniente dalla tabella dei fatti. Ad esempio:
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE
Cubi multidimensionali
È possibile utilizzare l'operatore CUBE per generare cubi a n dimensioni o con qualsiasi numero di dimensioni. Un cubo unidimensionale può essere utilizzato per generare un totale, ad esempio:
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item WITH CUBE
GO
L'istruzione SELECT restituisce un set di risultati in cui vengono indicati sia i subtotali di ogni valore della dimensione Item, sia il totale complessivo di tutti i valori di Item:
Item QtySum
-------------------- --------------------------
Chair 311.00
Table 347.00
ALL 658.00
Le istruzioni SELECT che includono un operatore CUBE con molte dimensioni possono produrre set di risultati molto estesi, in quanto generano righe per tutte le combinazioni dei valori di tutte le dimensioni. Tali set di risultati estesi possono includere una quantità eccessiva di dati, risultando di difficile lettura e comprensione. Per risolvere questo problema, è possibile includere l'istruzione SELECT in una vista:
CREATE VIEW InvCube AS
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE
È quindi possibile utilizzare la vista per richiedere solo i valori delle dimensioni desiderati:
SELECT *
FROM InvCube
WHERE Item = 'Chair'
AND Color = 'ALL'
Item Color QtySum
-------------------- -------------------- --------------------------
Chair ALL 311.00
(1 row(s) affected)
Vedere anche
Concetti
Riepilogo dei dati con l'operatore ROLLUP