Resumir datos con CUBE
El operador CUBE genera un conjunto de resultados que es un cubo multidimensional. Un cubo multidimensional es una expansión de datos de hechos o datos que registran eventos individuales. La expansión se basa en columnas que el usuario desea analizar. Estas columnas se llaman dimensiones. El cubo es un conjunto de resultados que contiene una tabla cruzada de todas las combinaciones de dimensiones posibles.
El operador CUBE se especifica en la cláusula GROUP BY de una instrucción SELECT. La lista de selección contiene las columnas de dimensión y las expresiones de funciones de agregado. GROUP BY especifica las columnas de dimensión y las palabras clave WITH CUBE. El conjunto de resultados contiene todas las combinaciones posibles de los valores de las columnas de dimensiones, junto con los valores de agregado de las filas subyacentes que coinciden con esa combinación de valores de dimensión.
Por ejemplo, una tabla simple Inventory contiene lo siguiente:
Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 124
Table Red 223
Chair Blue 101
Chair Red 210
La siguiente consulta devuelve un conjunto de resultados que contiene el subtotal de Quantity de todas las combinaciones posibles de Item y Color:
SELECT Item, Color, SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE
Éste es el conjunto de resultados.
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
Las filas siguientes del conjunto de resultados tienen especial interés:
Chair (null) 311.00
Esta fila informa del subtotal de todas las filas que tengan el valor Chair en la dimensión Item. Se devuelve el valor null para la dimensión Color para indicar que el agregado indicado por la fila incluye las filas con cualquier valor de la dimensión Color.
Table (null) 347.00
Esta fila es parecida, aunque informa del subtotal de todas las filas que tengan Table en la dimensión Item.
(null) (null) 658.00
Esta fila informa del total general del cubo. Ambas dimensiones, Item y Color, tienen el valor null. Esto demuestra que se resumen en la fila todos los valores de ambas dimensiones.
(null) Blue 225.00
(null) Red 433.00
Estas dos filas indican los subtotales de la dimensión Color. Ambas tienen null en la dimensión Item para indicar que los datos de agregado proceden de filas que tienen cualquier valor para la dimensión Item.
Usar GROUPING para distinguir valores NULL
Los valores NULL que genera la operación CUBE presentan el siguiente problema: ¿cómo se puede diferenciar un valor NULL generado por la operación CUBE de otro valor NULL devuelto por los datos reales? Esto se consigue con la función GROUPING. La función GROUPING devuelve 0 si el valor de la columna proviene de los datos de hechos y 1 si el valor de la columna es un valor NULL generado por la operación CUBE. En una operación CUBE, un valor NULL generado representa todos los valores. Es posible escribir la instrucción SELECT para que utilice la función GROUPING a fin de aplicar la cadena ALL en lugar de cualquier valor NULL generado. Puesto que un NULL de los datos de hechos indica que el valor de los datos es desconocido, la instrucción SELECT también se puede codificar para que devuelva la cadena UNKNOWN en lugar de un valor NULL de los datos de hechos. Por ejemplo:
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
Cubos multidimensionales
El operador CUBE se puede usar para generar cubos n-dimensionales, o cubos con cualquier número de dimensiones. Un cubo con una única dimensión se puede utilizar para generar un total, por ejemplo:
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
Esta instrucción SELECT devuelve un conjunto de resultados que muestra los subtotales de cada valor de Item y el total general de todos los valores de Item:
Item QtySum
-------------------- --------------------------
Chair 311.00
Table 347.00
ALL 658.00
Las instrucciones SELECT que contienen un operador CUBE con muchas dimensiones pueden generar conjuntos de resultados grandes, porque estas instrucciones generan filas para todas las combinaciones de los valores de todas las dimensiones. Estos conjuntos de resultados grandes pueden contener demasiados datos como para que resulten fáciles de leer y entender. Una solución a este problema es colocar la instrucción SELECT en 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
A continuación, la vista se puede utilizar para consultar sólo los valores de las dimensiones que resulten de interés:
SELECT *
FROM InvCube
WHERE Item = 'Chair'
AND Color = 'ALL'
Item Color QtySum
-------------------- -------------------- --------------------------
Chair ALL 311.00
(1 row(s) affected)