CUBE y ROLLUP del SQL

Cuando tenemos que mostrar información de nuestra base de datos a los usuarios habitualmente creamos informes con Visual Basic, con Crystal Reports, con los Analysis Services de Microsoft SQL Server 2000 o con lo que queramos. Con estas herramientas podemos realizar cualquier tipo de cálculo sobre los datos almacenados, y especialmente calcular sumas, totales, promedios. . .

Pero el lenguaje SQL también nos proporciona herramientas para hacer la mayor parte del trabajo en el servidor (ahorrándonos posteriores problemas). Como ya conoceréis tenemos cláusulas como GROUP BY para agrupar, las funciones de agregado para contar, sumar, promediar. y la cláusula COMPUTE BY para hacer resúmenes. ¿Y si queremos calcular subtotales y totales generales en una misma consulta? Pues también existen las cláusulas CUBE y ROLLUP que son las que vamos a tratar aquí.

¿Qué preguntas solucionamos con CUBE y ROLLUP?

Vamos a trabajar con la siguiente tabla por no complicar los ejemplos ni el código SQL. Además una tabla como esta aunque no sea real sirve perfectamente para mostrar como funciona WITH CUBE.

Id            TipoTransaccion      Divisa           Cantidad    
------------- -------------------- ---------------- ----------- 
1             Entrada              Euro             200
2             Entrada              Euro             1300
3             Salida               Dolar            2000
4             Entrada              Libra            500
5             Salida               Dolar            1000
6             Entrada              Euro             300
7             Entrada              Dolar            5000
8             Salida               Libra            500
9             Entrada              Euro             700
10            Entrada              Libra            400
11            Entrada              Yen              20000
12            Salida               Libra            300
13            Entrada              Euro             4000
14            Salida               Yen              30000
15            Entrada              Libra            3000
16            Entrada              Euro             400
17            Entrada              Euro             900
18            Salida               Dolar            4000
19            Entrada              Dolar            1200
20            Salida               Libra            900
21            Entrada              Euro             2100
22            Entrada              Libra            200
23            Entrada              Yen              25000
24            Entrada              Libra            400
25            Entrada              Euro             700
26            Entrada              NULL             2000
(26 filas afectadas)

Es una tabla en la que se guarda información sobre transacciones económicas en las que tenemos tres tipos de datos. Si la transacción es de entrada o de salida, la moneda en la que se hace y la cantidad. Ahora vamos a empezar a hacer preguntas de tipo OLAP (on-line analytical processing) para analizar los datos que tenemos almacenados.

Primera pregunta: ¿Cuántos transacciones tenemos de entrada y de salida?

Bueno, para resolver esto podemos escribir una consulta sencilla con un GROUP BY y un COUNT

SELECT TipoTransaccion, COUNT(IdTransaccion) Cantidad
	FROM Movimientos GROUP BY TipoTransaccion
TipoTransaccion                        Cantidad
-------------------------------------- ----------- 
Entrada                                18
Salida                                 7
(2 filas afectadas)

Segunda Pregunta: ¿Qué divisa es la más usada?

Esta es un poco más elaborada pero de nuevo nos basta con usar un GROUP BY. También utilizamos TOP 1 para quedarnos sólo con el valor más alto después de ordenar la suma de manera descendente.

SELECT TOP 1 Divisa, SUM(Cantidad) Suma
	FROM Movimientos GROUP BY Divisa
	ORDER BY SUM(Cantidad) DESC
Divisa                                 Suma  
-------------------------------------- ----------- 
Yen                                    75000
(1 filas afectadas)
		

Tercera pregunta: ¿Cantidad de cada tipo de transacción en cada tipo de divisa?

Aquí tenemos que agrupar por TipoTransaccion y Divisa para obtener la suma de las cantidades por esos conceptos. Podemos ver cuantos Euros han salido o cuantos dólares han entrado.

SELECT  TipoTransaccion, Divisa, SUM(Cantidad) Cantidad
	FROM Movimientos GROUP BY TipoTransaccion, Divisa 
	ORDER BY TipoTransaccion
	
TipoTransaccion             Divisa                 Cantidad    
--------------------------- ------------------ ----------- 
Entrada                          Dolar                  6200
Entrada                          Euro                   10600
Entrada                          Libra                  4500
Entrada                          Yen                    45000
Salida                           Dolar                  7000
Salida                           Libra                  1700
Salida                           Yen                    30000
(7 filas afectadas)
		

Sin embargo esto no responde a todas las preguntas que podemos hacer.

Cuarta pregunta: ¿Y si quiero la información agrupada de más maneras?

Por ejemplo para saber el total entrante, o el balance de Yenes habrá que hacer cálculos adicionales o bien con nuevas consultas o bien en nuestra aplicación cliente. Pero ahora tenemos WITH CUBE que nos permite crear nuevas dimensiones en nuestras consultas. Cuando usamos esta cláusula es como si estuviésemos haciendo a la vez todos los GROUP BY posibles y además mostrándolos en un único resultset. Añadamos el WITH CUBE a la sentencia anterior

SELECT  TipoTransaccion, Divisa, SUM(Cantidad) Cantidad
	FROM Movimientos GROUP BY TipoTransaccion, Divisa 
	WITH CUBE
TipoTransaccion                Divisa             Cantidad    
------------------------------ ------------------ ----------- 
Entrada                        Dolar              6200
Entrada                        Euro               10600
Entrada                        Libra              4500
Entrada                        Yen                45000
Entrada                        NULL               66300
Salida                         Dolar              7000
Salida                         Libra              1700
Salida                         Yen                30000
Salida                         NULL               38700
NULL                           NULL               105000
NULL                           Dolar              13200
NULL                           Euro               10600
NULL                           Libra              6200
NULL                           Yen                75000
(14 filas afectadas)
		

Aquí hay unos cuantos cambios. Primero vemos que sin necesidad de decirlo los datos se han ordenado por TipoTransaccion y dentro de TipoTransaccion por Divisa. Además aparecen varios NULL por el medio de la tabla. Pero no os preocupéis que todo va bien y vamos a explicar este resultado con calma. Ahora cada fila es una de las posibles combinaciones de TipoTransaccion con Divisa, y las filas que contienen un NULL se tienen que leer pensando que donde está el NULL debería poner "todas". Es decir la fila

NULL                           Dolar               13200	
		

Quiere decir que la cantidad total de Dólares tanto en entradas como en salidas (todos los TiposTransaccipn) es 13200 La fila

Entrada                        NULL                66300
		

Quiere decir que hay un valor total de 66300 para todas las entradas (es decir, para todas las divisas) y por último la fila

NULL                           NULL                105000
		

Nos indica que el total de movimientos (todos los TipoTransaccion) de entrada y salida en cualquier divisa (todas las divisas) es de 105000

Como vemos el NULL representa un super agregado en la columna en la que está colocado. Este tipo de NULL no lo debemos confundir con un NULL normal. Ya sabéis que un NULL normal indica que desconocemos el valor mientras que este NULL indica una agrupación.

Vamos a insertar una nueva fila en nuestra tabla

INSERT INTO Movimientos (TipoTransaccion, Divisa, Cantidad)
	VALUES ('Entrada', NULL, 2000)
		

¿Qué ocurre ahora si repetimos la consulta?

Quinta pregunta: ¿cómo sabemos cual de los NULL es un super agregado y cual es un NULL de verdad?

Hay una función llamada GROUPING que nos dice cuando nuestro NULL es de verdad y cuando no. Esta función nos devuelve un 1 si el nombre de la columna pasada como parámetro se usa como resumen y un 0 si no es así. Veamos un ejemplo

SELECT  TipoTransaccion,
	Divisa, 'Todas las Divisas'=GROUPING(Divisa), 
	SUM(Cantidad) Cantidad
	FROM Movimientos GROUP BY TipoTransaccion, Divisa 
	WITH CUBE

TipoTransaccion         Divisa         Todas las Divisas    Cantidad    
----------------------- -------------- -------------------- ----------- 
Entrada                 NULL           0                    2000
Entrada                 Dolar          0                    6200
Entrada                 Euro           0                    10600
Entrada                 Libra          0                    4500
Entrada                 Yen            0                    45000
Entrada                 NULL           1                    68300
Salida                  Dolar          0                    7000
Salida                  Libra          0                    1700
Salida                  Yen            0                    30000
Salida                  NULL           1                    38700
NULL                    NULL           1                    107000
NULL                    NULL           0                    2000
NULL                    Dolar          0                    13200
NULL                    Euro           0                    10600
NULL                    Libra          0                    6200
NULL                    Yen            0                    75000
(16 filas afectadas)
		

Como veis hay dos tipos de NULL en la columna de divisas. Las que corresponden al último registro que insertamos que tiene un NULL en divisa, y al que la función GROUPING le asocia un 0, y el NULL que podemos traducir por "Todas las divisas" al que la función GROUPING le asocia un 1.

Sexta pregunta: ¿Podemos mejorar el aspecto del resultado?

Ahora mezclamos estas funciones nuevas con dos funciones conocidas, CASE e ISNULL para darle un aspecto más elegante al Resulset obtenido.

SELECT  TipoTransaccion,
	'Divisa'= CASE 
			WHEN GROUPING(Divisa)=1 THEN 'Todas'
			ELSE ISNULL(Divisa, 'N/D')
		END,			 
	SUM(Cantidad) Cantidad
	FROM Movimientos GROUP BY TipoTransaccion, Divisa 
	WITH CUBE

TipoTransaccion      Divisa               Cantidad    
-------------------- -------------------- ----------- 
Entrada              N/D                  2000
Entrada              Dolar                6200
Entrada              Euro                 10600
Entrada              Libra                4500
Entrada              Yen                  45000
Entrada              Todas                68300
Salida               Dolar                7000
Salida               Libra                1700
Salida               Yen                  30000
Salida               Todas                38700
NULL                 Todas                107000
NULL                 N/D                  2000
NULL                 Dolar                13200
NULL                 Euro                 10600
NULL                 Libra                6200
NULL                 Yen                  75000
(16 filas afectadas)
		

Todo queda más claro en este resultado. Donde pone "todas" en la columna de divisas quiere decir precisamente eso, y donde pone "N/D" pues quiere decir no disponible.

Séptima pregunta: ¿Podemos saberlo todo?

Pues ahora ya sí. Vamos a poner la consulta que nos devuelve toda la información que podemos pedir a los datos iniciales.

SELECT	'TipoTransacion'= CASE 
			WHEN GROUPING(TipoTransaccion)=1 THEN 'Todas'
			ELSE ISNULL(TipoTransaccion, 'N/D')
		END,
	'Divisa'= CASE 
			WHEN GROUPING(Divisa)=1 THEN 'Todas'
			ELSE ISNULL(Divisa, 'N/D')
		END,			 
	SUM(Cantidad) Cantidad
	FROM Movimientos GROUP BY TipoTransaccion, Divisa 
	WITH CUBE

TipoTransacion          Divisa           Cantidad    
----------------------- ---------------- ----------- 
Entrada                 N/D              2000
Entrada                 Dolar            6200
Entrada                 Euro             10600
Entrada                 Libra            4500
Entrada                 Yen              45000
Entrada                 Todas            68300
Salida                  Dolar            7000
Salida                  Libra            1700
Salida                  Yen              30000
Salida                  Todas            38700
Todas                   Todas            107000
Todas                   N/D              2000
Todas                   Dolar            13200
Todas                   Euro             10600
Todas                   Libra            6200
Todas                   Yen              75000
(16 filas afectadas)
		

De esta consulta podemos sacar todas las respuestas a cualquier pregunta que nos hagan sobre los datos iniciales.

Octava pregunta: ¿Y qué pasa con el ROLLUP?

Mientras que WITH CUBE genera un conjunto de resultados que muestra agregados para todas las combinaciones de valores de las columnas seleccionadas, WHIT ROLLUP genera un conjunto de resultados que muestra agregados para una jerarquía de valores de las columnas seleccionadas.

Es decir, con CUBE aparecen los resultado totalizados por TipoTransaccion, por Divisa, y por totales absolutos, mientras que con ROLLUP sólo aparecerían los totales agrupados por lo que nosostros indiquemos. Veámoslo agrupando por TipoTransaccion:

SELECT	'TipoTransacion'= CASE 
			WHEN GROUPING(TipoTransaccion)=1 THEN 'Todas'
			ELSE ISNULL(TipoTransaccion, 'N/D')
		END,
	'Divisa'= CASE 
			WHEN GROUPING(Divisa)=1 THEN 'Todas'
			ELSE ISNULL(Divisa, 'N/D')
		END,			 
	SUM(Cantidad) Cantidad
	FROM Movimientos 
	GROUP BY TipoTransaccion, Divisa 
	WITH ROLLUP

TipoTransacion            Divisa             Cantidad    
------------------------- ------------------ ----------- 
Entrada                   N/D                2000
Entrada                   Dolar              6200
Entrada                   Euro               10600
Entrada                   Libra              4500
Entrada                   Yen                45000
Entrada                   Todas              68300
Salida                    Dolar              7000
Salida                    Libra              1700
Salida                    Yen                30000
Salida                    Todas              38700
Todas                     Todas              107000
(11 filas afectadas)
		

Obtenemos menos información que con WITH CUBE pero de manera más clara. Además muchas veces con esto será suficiente

Novena pregunta: ¿Esto se parece al COMPUTE BY verdad?

MEste tipo de consultas a alguno le traerá a la memoria una cláusula de SQL Server llamada COMPUTE BY, que hace prácticamente lo mismo pero de diferente manera. Ejecutemos esta consulta

SELECT	TipoTransaccion, Divisa, Cantidad 
	FROM Movimientos 
	ORDER BY TipoTransaccion, Divisa
	COMPUTE SUM(Cantidad) BY TipoTransaccion, Divisa

TipoTransaccion        Divisa              Cantidad    
---------------------- ------------------- -----------
Entrada                NULL                2000
                                                      sum
                                                      ===========
                                                      2000
TipoTransaccion        Divisa              Cantidad    
---------------------- ------------------- ----------- 
Entrada                Dolar               1200
Entrada                Dolar               5000
                                                      sum
                                                      ===========
                                                      6200
TipoTransaccion        Divisa              Cantidad    
---------------------- ------------------- ----------- 
Entrada                Euro                 4000
Entrada                Euro                 300
Entrada                Euro                 400
Entrada                Euro                 900
Entrada                Euro                 200
Entrada                Euro                 1300
Entrada                Euro                 700
Entrada                Euro                 2100
Entrada                Euro                 700
                                                        sum
                                                        ===========
                                                        10600
TipoTransaccion        Divisa              Cantidad    
---------------------- ------------------- ----------- 
Entrada                Libra               400
Entrada                Libra               200
Entrada                Libra               400
Entrada                Libra               500
Entrada                Libra               3000
                                                         sum
                                                         ===========
                                                         4500
TipoTransaccion        Divisa               Cantidad    
---------------------- -------------------- ----------- 
Entrada                Yen                  20000
Entrada                Yen                  25000
                                                          sum
                                                          ===========
                                                          45000
TipoTransaccion        Divisa               Cantidad    
---------------------- -------------------- ----------- 
Salida                 Dolar                1000
Salida                 Dolar                2000
Salida                 Dolar                4000
                                                           sum
                                                           ===========
                                                           7000
TipoTransaccion        Divisa               Cantidad    
---------------------- -------------------- ----------- 
Salida                 Libra                500
Salida                 Libra                300
Salida                 Libra                900
                                                            sum
                                                            ===========
                                                            1700
TipoTransaccion        Divisa               Cantidad    
---------------------- -------------------- ----------- 
Salida                 Yen                  30000
                                                            sum
                                                            ===========
                                                            30000
(34 filas afectadas)
		

Es otra manera de obtener resultados agrupados, pero tiene un par de inconvenientes. El primero y más importante es que el ROLLUP produce una salida una salida relacional que se pueda almacenar como una vista, utilizar en el FROM de otra consulta o enviar al cliente para ser manejada como un Recordset de Visual Basic. El resultado de un COMPUTE BY tiene una serie de filas adicionales un poco "incómodas" si queremos usar ese resultado para algo más que mostrarselo al usuario.

Y la segunda razón para no usarla es que Microsoft incluye el COMPUTE BY por compatibilidad y no recomienda su uso (en .Net olvidaros de usar COMPUTE BY)

Décima pregunta: Muy bonito todo esto, pero si lo hago con un montón de datos en varias tablas, ¿no será muy lento?

Sí. Si la cantidad de datos a tratar es muy grande este tipo de consultas pueden consumir muchos recursos y tiempo, pero hay muchas soluciones para que esto no sea un problema. Por ejemplo cuando tenemos una consulta que va a resumir una serie de datos y la vamos a necesitar habitualmente podemos convertirla en una vista o guardar el resultado en una tabla, y así podemos recurrir al resultado sin perder tiempo volviendo a ejecutar la sentencia SQL.

Siguiendo con nuestro ejemplo podemos almacenar el resultado que obtuvimos con el CUBE en una tabla nueva con SELECT . . . INTO

SELECT	'TipoTransacion'= CASE 
			WHEN GROUPING(TipoTransaccion)=1 THEN 'Todas'
			ELSE ISNULL(TipoTransaccion, 'N/D')
		END,
	'Divisa'= CASE 
			WHEN GROUPING(Divisa)=1 THEN 'Todas'
			ELSE ISNULL(Divisa, 'N/D')
		END,			 
	SUM(Cantidad) Cantidad
	INTO Resumen
	FROM Movimientos GROUP BY TipoTransaccion, Divisa 
	WITH CUBE
select * from resultado
		

Ahora podemos recurrir a la información ya tratada leyendo la tabla Resumen

Última pregunta: ¿Es esto todo?

No, ni mucho menos.

Aquí sólo tenemos una muestra de lo que se puede hacer con lenguaje SQL para analizar la información que hay en nuestros datos. Y si alguien está interesado en esto del procesado analítico lo visto aquí ni siquiera es la punta del iceberg!!

De todos modos es un comienzo

COMPARTE ESTE ARTÍCULO

ENVIAR A UN AMIGO
COMPARTIR EN FACEBOOK
COMPARTIR EN TWITTER
COMPARTIR EN GOOGLE +
ARTÍCULO ANTERIOR

¡SÉ EL PRIMERO EN COMENTAR!
Conéctate o Regístrate para dejar tu comentario.