GuilleSQL :: Microsoft SQL Server, SSIS, y más !!

SQL Server FAQ: Agrupando datos con WITH CUBE, WITH ROLLUP y GROUPING

Volver a: [SQL Server FAQ :: Preguntas y Respuestas Frecuentes de SQL Server :: Manual SQL Server]


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.

Volver a: [SQL Server FAQ :: Preguntas y Respuestas Frecuentes de SQL Server :: Manual SQL Server]


[Fecha del Artículo (UTC): 09/01/2008]
[Autor: GuilleSQL]



Escribir un Comentario

Para poder escribir un comentario, debe Iniciar Sesión con un usuario.

Si no dispone de un usuario, puede Registrarse y hacerse miembro.

Si dispone de un usuario, pero no recuerda sus credenciales de acceso, puede Restablecer su Contraseña.

Miembros de
Miembros de GITCA (Global IT Community Association)

Menu de Usuario
  Iniciar Sesión
  Registrarse
  Restablecer Contraseña
  Ventajas de Registrarse

Acerca de
  Contigo desde Oct 2007
  771 usuarios registrados
  86146 pageloads/mes
  Ranking Alexa 498160

Social Networks
Sigue a Portal GuilleSQL en Linkedin !!
Sigue a Portal GuilleSQL en Twitter !!



Archivo

Marzo de 2017 (3)
Enero de 2017 (4)
Junio de 2016 (1)
Mayo de 2016 (2)
Abril de 2016 (2)
Septiembre de 2015 (2)
Agosto de 2015 (2)
Junio de 2015 (10)
Mayo de 2015 (4)
Abril de 2015 (8)
Marzo de 2015 (11)
Octubre de 2014 (3)
Septiembre de 2014 (7)
Agosto de 2014 (5)
Julio de 2014 (2)
Mayo de 2014 (4)
Abril de 2014 (4)
Marzo de 2014 (4)
Febrero de 2014 (1)
Enero de 2014 (5)
Diciembre de 2013 (8)
Noviembre de 2013 (2)
Octubre de 2013 (7)
Septiembre de 2013 (6)
Agosto de 2013 (1)
Julio de 2013 (6)
Junio de 2013 (11)
Mayo de 2013 (7)
Abril de 2013 (6)
Febrero de 2013 (5)
Enero de 2013 (7)
Diciembre de 2012 (12)
Noviembre de 2012 (13)
Octubre de 2012 (5)
Septiembre de 2012 (3)
Agosto de 2012 (6)
Julio de 2012 (4)
Junio de 2012 (1)
Mayo de 2012 (2)
Abril de 2012 (7)
Marzo de 2012 (16)
Febrero de 2012 (9)
Enero de 2012 (5)
Diciembre de 2011 (10)
Noviembre de 2011 (10)
Octubre de 2011 (4)
Septiembre de 2011 (5)
Agosto de 2011 (2)
Julio de 2011 (2)
Junio de 2011 (4)
Mayo de 2011 (2)
Abril de 2011 (6)
Marzo de 2011 (4)
Febrero de 2011 (10)
Enero de 2011 (5)
Diciembre de 2010 (6)
Noviembre de 2010 (4)
Octubre de 2010 (8)
Septiembre de 2010 (4)
Agosto de 2010 (1)
Julio de 2010 (3)
Mayo de 2010 (5)
Abril de 2010 (6)
Marzo de 2010 (8)
Febrero de 2010 (3)
Enero de 2010 (1)
Diciembre de 2009 (9)
Noviembre de 2009 (14)
Octubre de 2009 (2)
Septiembre de 2009 (8)
Agosto de 2009 (2)
Julio de 2009 (10)
Junio de 2009 (9)
Mayo de 2009 (10)
Abril de 2009 (9)
Marzo de 2009 (3)
Febrero de 2009 (2)
Enero de 2009 (3)
Noviembre de 2008 (2)
Octubre de 2008 (2)
Septiembre de 2008 (2)
Agosto de 2008 (5)
Julio de 2008 (5)
Junio de 2008 (1)
Mayo de 2008 (3)
Abril de 2008 (2)
Marzo de 2008 (2)
Febrero de 2008 (2)
Enero de 2008 (5)
Noviembre de 2007 (2)
Octubre de 2007 (2)






Esta información se proporciona "como está" sin garantías de ninguna clase, y no otorga ningún derecho.
This information is provided "AS IS" with no warranties, and confers no rights.

Copyright © 2007 GuilleSQL, todos los derechos reservados.
GuilleSQL.com y GuilleSQL.net son también parte de Portal GuilleSQL.

Visitas recibidas (Page Loads) en GuilleSQL (fuente: StatCounter):

screen resolution stats
Visitas