En la realización de consultas de grupo (es decir, utilizando la cláusula GROUP BY), habitualmente resulta necesario obtener valores totales o subtotales adicionales a los que se pueden obtener sólo con la cláusula GROUP BY. Ante esta situación, es posible utilizar la cláusula COMPUTE BY, sin embargo, COMPUTE BY ofrece múltiples conjuntos de resultados que hace difícil su procesamiento. Por suerte, disponemos también de los operadores WITH CUBE y WITH ROLLUP, que junto con la función GROUPING nos permite obtener el resultado deseado utilizando un único conjunto de resultados.
Antes de empezar, vamos a poner un escenario de ejemplo, que nos sirva didácticamente para comprender con claridad el problema. Suponemos una tabla que almacena el stock de un almacén, la cual contiene los campos Fabricante, Color y Cantidad, entre otros muchos campos.
Podemos realizar una consulta utilizando la cláusula GROUP BY Fabricante, Color para obtener el SUM(Cantidad), y así conocer para cada Fabricante y Color cuantos Productos se disponen en el almacén.
SELECT Fabricante, Color, SUM(Cantidad)
FROM Almacen
GROUP BY Fabricante, Color |
El problema es ¿y si deseamos obtener valores totales y subtotales adicionales? Es decir, ¿y si deseamos obtener adicionalmente el total por Fabricante? ¿o quizás el total por Color? ¿o quizás el total de todo?
Quizás la solución más inmediata, sea realizar múltiples consultas.
La siguiente alternativa, podría ser realizar una única consulta con una o varias cláusulas COMPUTE BY. En este caso, aunque se trate de una única conjunta, la ejecución de la misma devolverá múltiples conjuntos de resultados, algo que puede complicar su procesamiento si ejecutamos dicha consulta desde una aplicación (ej: a través de ADO o de ADO.Net).
La última alternativa es utilizar una única consulta con los operadores WITH CUBE y WITH ROLLUP, combinados o no con la función GROUPING. Así, dispondremos de una única consulta que devuelve un único conjunto de resultados. Tanto el operador WITH CUBE como WITH ROLLUP se utilizan dentro de la cláusula GROUP BY de la consulta. Recordar, que esta funcionalidad existe desde SQL Server 2000 (no es necesario disponer de SQL Server 2005). A continuación, explicamos estas alternativas en mayor detalle.
El operador WITH CUBE permite generar un conjunto de resultados multidimensional, es decir, genera todas las combinaciones posibles con los campos utilizados en la cláusula GROUP BY, incluyendo resultados parciales y totales. Para los resultados parciales y totales se utilizará el valor NULL, de tal modo, que el resultado de la consulta con el operador WITH CUBE es el mismo resultado que la misma consulta sin el operador WITH CUBE, pero añadiendo varias filas adicionales correspondientes a resultados parciales y totales. A continuación mostramos una consulta de ejemplo:
SELECT Fabricante, Color, SUM(Cantidad)
FROM Almacen
GROUP BY Fabricante, Color WITH CUBE |
En este caso:
- Para cada Fabricante se agregará una fila adicional con el valor de Color a NULL, que mostrará el subtotal para dicho Fabricante (es decir, habrá tantas filas adicionales como distintos Fabricantes).
- Para cada Color se agregará una fila adicional con el valor de Fabricante a NULL, que mostrará el subtotal para cada Color (es decir, habrá tantas filas adicionales como distintos Colores).
- Se agregará una fila adicional con el valor NULL tanto para el Fabricante como para el Color, que mostrará el total.
Llegados a este punto, surge la siguiente duda: Si la tabla original contiene valores NULL en los campos Fabricante y/o Color, ¿cómo se comporta la consulta? Si recordamos lo que antes dijimos, obtendremos los resultados propios de la consulta sin el operador WITH CUBE (la cual devolverá filas con Fabricante y/o Color a NULL, en función de los datos contenidos en la tabla base), y adicionalmente se añadirán las filas de subtotales y totales (también con valores NULL para Fabricante y/o Color). Este comportamiento implica que obtendremos filas repetidas con valores NULL, unas correspondientes al comportamiento natural de GROUP BY más las adicionales del operador WITH CUBE. En esta situación, surge la necesidad de poder diferenciar claramente en la consulta qué filas son las obtenidas por el GROUP BY y qué filas son obtenidas por el operador WITH CUBE, es decir, ¿cómo podemos diferenciar dichas filas? Para resolver este problema podemos utilizar la función GROUPING, como se muestra en la siguiente consulta de ejemplo:
SELECT
CASE WHEN (GROUPING(Fabricante) = 1) THEN 'ALL'
ELSE ISNULL(Fabricante, 'UNKNOWN')
END AS Fabricante,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Cantidad)
FROM Almacen
GROUP BY Fabricante, Color WITH CUBE
|
De este modo, se consigue identificar las filas correspondientes a los subtotales o totales (las añadidas por el operador WITH CUBE) con el texto ALL substituyendo al valor NULL, y del mismo modo, se identifican con el texto UNKNOWN las filas originales del comportamiento natural de GROUP BY (en vez del valor NULL), y el problema queda solucionado.
Explicado el funcionamiento del operador WITH CUBE, es el momento de explicar el funcionamiento del operador WITH ROLLUP.
El operador WITH ROLLUP permite generar un conjunto de resultados similar al producido por el operador WITH CUBE, pero incluyendo menos resultados subtotales. A continuación mostramos una consulta de ejemplo:
SELECT Fabricante, Color, SUM(Cantidad)
FROM Almacen
GROUP BY Fabricante, Color WITH ROLLUP |
En este caso:
- Para cada Fabricante se agregará una fila adicional con el valor de Color a NULL, que mostrará el subtotal para dicho Fabricante (es decir, habrá tantas filas adicionales como distintos Fabricantes).
- Se agregará una fila adicional con el valor NULL tanto para el Fabricante como para el Color, que mostrará el total.
Como vemos, la única diferencia con el operador WITH CUBE es que no se agrega una fila adicional para cada Color con el valor de Fabricante a NULL. Es decir, mientras el operador WITH CUBE muestra un resultado de aspecto multidimensional, el operador WITH ROLLUP muestra un resultado de aspecto jerárquico.
Por supuesto, con el operador WITH ROLLUP también se puede utilizar la función GROUPING para el tratamiento de los valores nulos, como vimos anteriormente con el operador WITH CUBE.
