Ir al contenido principal

Agrupando datos con WITH CUBE, WITH ROLLUP y GROUPING

Este capítulo explica los operadores WITH CUBE y WITH ROLLUP, que junto a la función GROUPING resulta de gran utilidad en muchos casos. Resultan una alternativa muy interesante a la utilización de la cláusula COMPUTE BY, y además, están disponible desde SQL Server 2000. Se explica el tratamiento de los nulos (NULL) en las consultas WITH CUBE y WITH ROLLUP, y se incluyen varios ejemplos de WITH CUBE y WITH ROLLUP, para facilitar su uso.

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.

Comentarios

Entradas populares de este blog

¿En qué puerto TCP escucha SQL Server 2005? ¿Cómo cambiar o configurar el puerto TCP de escucha de una Instancia de SQL Server 2005?

Una buena práctica inmediatamente después de instalar SQL Server 2005 es cambiar el Puerto TCP de escucha, por múltiples motivos: Seguridad, Configuración de reglas de acceso de Firewall, Aplicaciones cliente que requieren un puerto TCP estático para SQL Server, etc. En este Artículo se explica cómo averiguar en qué puerto TCP escucha SQL Server 2005, cómo cambiar el puerto TCP de escucha de SQL Server 2005, etc. Resulta de gran interés ser capaz de responder a la pregunta  ¿En qué puerto TCP escucha SQL Server?  Por defecto, una  Instancia por Defecto de SQL Server 2005  queda configurada durante la instalación para escuchar en el  puerto TCP-1433 , sin embargo,  las Instancias con Nombre  quedan configuradas durante el proceso de instalación para escuchar en  puertos TCP dinámicos , por lo tanto, cada vez que se inicie la Instancia puede que escuche en un puerto diferente. Esta situación puede resultar problemática, por un lado desde el punto de vista de la seguridad (el hecho d

¿Qué es el nivel de aislamiento (Isolation Level) de una Transacción? ¿Qué niveles de aislamiento ofrece SQL Server?

Esta capítulo explica qué es el nivel de aislamiento (isolation level) de una transacción, el comportamiento de SQL Server en operaciones de lectura o de escritura, se detallan los diferentes niveles de aislamiento basados en bloqueos (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE) y los niveles de aislamiento basados en versionado de filas (READ COMMITTED SNAPSHOT, SNAPSHOT), se explican los males de la concurrencia (lecturas sucias, lecturas no repetibles, lecturas fantasma, y conflictos de actualización), como establecer el nivel de aislamiento deseado (SET TRANSACTION ISOLATION LEVEL y las opciones de base de datos READ_COMMITTED_SNAPSHOT y ALLOW_SNAPSHOT_ISOLATION), cómo conecer el tiempo máximo de bloqueo (@@LOCK_TIMEOUT) y como establecer el tiempo máximo de bloqueo (SET LOCK_TIMEOUT), etc. El nivel de aislamiento de una transacción (transaction isolation level)  define el grado en que se aísla una transacción de las modificaciones de recursos o datos rea

Aumentar y Reducir la TempDB en SQL Server?

Una buena práctica después de instalar SQL Server (y que interesa revisar periódicamente) es tener bien dimensionada la base de datos TEMPDB, es decir que el  tamaño inicial de TEMPDB  sea suficiente, y en consecuencia no sea necesario que TEMPDB crezca ni tampoco reducir TEMPDB (SHRINK). Esta artículo explica brevemente  para qué sirve TEMPDB , explica camo cambiar el tamaño inicial de TEMPDB (aumentar o reducir),  cómo reducir TEMPDB , cuántos ficheros son recomendables para TEMPDB, etc. ¿Para qué sirve TEMPDB?  La base de datos TEMPDB es un elemento de gran importancia en una Instancia de SQL Server, ya que  TEMPDB es la encargada de almacenar tanto los objetos temporales  (tablas temporales, procedimientos almacenados temporales, etc.),  como los resultados intermedios  que pueda necesitar crear el motor de base de datos, por ejemplo durante la ejecución de consultas que utilizan las cláusulas GROUP BY, ORDER BY, DISTINCT, etc. (es decir, las tablas temporales o WorkTables que