Consultas Agrupadas

Puede utilizar consultas agrupadas para definir grupos en sus datos, y luego puede realizar cálculos de análisis de datos por grupo. Agrupa los datos por un conjunto de atributos conocidos como un conjunto de agrupación. Las consultas T-SQL tradicionales definen un único conjunto de agrupación; es decir, agrupan los datos en una sola manera. Más recientemente, T-SQL introdujo soporte para características que le permiten definir varios conjuntos de agrupación en una sola consulta. Este post comienza cubriendo consultas que definen un único conjunto de agrupación, y luego se cubren consultas que definen varios conjuntos.

Trabajando con un Conjunto de Agrupación Simple

Con consultas agrupadas, puede organizar las filas que está consultando en grupos y aplicar cálculos de análisis de datos como funciones agregadas sobre estos grupos. Una consulta se convierte en una consulta agrupada cuando se utiliza una función de grupo, una cláusula GROUP BY, o ambas.

Una consulta que invoca una función de grupo, pero no tiene una cláusula GROUP BY explícita, organiza todas las filas en un grupo. Considere la siguiente consulta como un ejemplo.

USE TSQL2012;

SELECT COUNT(*) AS numpedidos

FROM Sales.Orders;

Esta consulta genera el siguiente resultado.

numpedidos

-----------

830

Debido a que no hay cláusula GROUP BY explícita, todas las filas consultadas de la tabla Sales.Orders están dispuestas en un grupo, y entonces la función COUNT (*) cuenta el número de filas en ese grupo. Las consultas agrupadas retornan una fila de resultado por grupo; y debido a que la consulta define solo un grupo, retorna solo una fila en el conjunto de resultado.

Usando una cláusula GROUP BY explícita, puede agrupar las filas basado en un conjunto de agrupación específico de expresiones. Por ejemplo, la siguiente consulta agrupa las filas por shipperid y cuenta el número de filas (pedidos, en este caso) por cada grupo distinto.

SELECT shipperid, COUNT(*) AS numpedidos

FROM Sales.Orders

GROUP BY shipperid;

Esta consulta genera la siguiente salida.

shipperid    numpedidos

----------- -----------

1            249

2            326

3            255

La consulta identifica tres grupos porque hay tres shipperid distintos.

El conjunto de agrupación puede estar hecho de varios elementos. Por ejemplo, la siguiente consulta agrupa las filas por shipperid y año de envío.

SELECT shipperid, YEAR(shippeddate) AS annoenvio,

   COUNT(*) AS numpedidos

FROM Sales.Orders

GROUP BY shipperid, YEAR(shippeddate);

Esta consulta genera el siguiente resultado.

shipperid   annoenvio   numpedidos

----------- ----------- -----------

1            2008         79

3            2008         73

1            NULL         4

3            NULL         6

1            2006         36

2            2007         143

2            NULL         11

3            2006         51

1            2007         130

2            2008         116

2            2006         56

3            2007         125

Observe que obtiene un grupo por cada combinación de shipperid distinto y de año de envío que existe en los datos, incluso cuando el año de envió es NULL. Recuerde que un NULL en la columna shippeddate representa pedidos no enviados; así un NULL en la columna annoenvio representa el grupo de pedidos no enviados, por el respectivo distribuidor.

Si necesita filtrar grupos enteros, necesita una opción de filtrado que es evaluada a nivel de grupo, a diferencia de la cláusula WHERE, que es evaluada a nivel de fila. Para esto, T-SQL proporciona la cláusula HAVING. Al igual que la cláusula WHERE, la cláusula HAVING utiliza un predicado pero evalúa el predicado por grupo en lugar de por fila. Esto significa que puede referirse a cálculos agregados porque los datos ya han sido agrupados.

Por ejemplo, suponga que necesita agrupar sólo los pedidos enviados por shipperid y el año de envío, y filtrar sólo los grupos que tienen menos de 100 pedidos. Puede utilizar la siguiente consulta para lograr esta tarea.

SELECT shipperid, YEAR(shippeddate) AS annoenvio,

   COUNT(*) AS numpedidos

FROM Sales.Orders

WHERE shippeddate IS NOT NULL

GROUP BY shipperid, YEAR(shippeddate)

HAVING COUNT(*) < 100;

Esta consulta genera la siguiente salida.

shipperid    annoenvio  numpedidos

----------- ----------- -----------

1            2008         79

3            2008         73

1            2006         36

3            2006         51

2            2006         56

Observe que la consulta filtra sólo los pedidos enviados en la cláusula WHERE. Este filtro es aplicado a nivel de fila conceptualmente antes que los datos sean agrupados. Luego la consulta agrupa los datos por shipperid y año de envío. Entonces la cláusula HAVING filtra sólo los grupos que tienen un conteo de filas (pedidos) que sea menor que 100. Por último, la cláusula SELECT retorna el shipperid, año de envío, y el conteo de pedidos por cada grupo hallado.

T-SQL soporta un número de funciones agregadas. Estos incluyen COUNT(*) y algunas funciones de conjunto generales (ya categorizados por el SQL estándar) como COUNT, SUM, AVG, MIN y MAX. Las funciones de conjunto generales son aplicadas a una expresión e ignoran los NULLs.

La siguiente consulta invoca la función COUNT(*), además de un número de funciones de conjunto generales, incluyendo COUNT.

SELECT shipperid,

  COUNT(*) AS numpedidos,

  COUNT(shippeddate) AS pedidosenviados,

  MIN(shippeddate) AS fechaprimerpedido,

  MAX(shippeddate) AS fechaultimopedido,

  SUM(val) AS valortotal

FROM Sales.OrderValues

GROUP BY shipperid;

Esta consulta genera la siguiente salida (fechas formateadas para facilitar la lectura).

shipperid numpedidos pedidosenviados fechaprimerpedido fechaultimopedido valortotal

--------- ---------- --------------- ----------------- ---------------- -----------

3          255       249             2006-07-15              2008-05-01    383405.53

1          249       245             2006-07-10              2008-05-04    348840.00

2          326       315             2006-07-11              2008-05-06    533547.69

Note la diferencia entre los resultados de COUNT(shippeddate) y COUNT(*). El primero ignora NULLs en la columna shippeddate, y por lo tanto los conteos son menores o iguales que, a los producidos por el último.

Con funciones de conjunto generales, puede trabajar con ocurrencias distintas por especificar una cláusula DISTINCT antes de la expresión, de la siguiente manera.

SELECT shipperid, COUNT(DISTINCT shippeddate) AS numfechasenvio

FROM Sales.Orders

GROUP BY shipperid;

Esta consulta genera la siguiente salida.

shipperid    numfechasenvio

----------- -----------------

1            188

2            215

3            198

Note que la opción DISTINCT está disponible no sólo en la función COUNT, sino también para otras funciones de conjunto generales. Sin embargo, es más común utilizarlo con COUNT.

Desde una perspectiva de procesamiento de consulta lógico, la cláusula GROUP BY es evaluada después de las cláusulas FROM y WHERE, y antes de las cláusulas HAVING, SELECT y ORDER BY. Así que las últimas tres cláusulas ya trabajan con una tabla agrupada, y por lo tanto las expresiones que soportan son limitadas. Cada grupo está representado por solo una fila de resultado; por lo tanto, todas las expresiones que aparecen en tales cláusulas deben garantizar un solo valor de resultado por grupo. No hay problema en referenciar directamente a los elementos que aparecen en la cláusula GROUP BY, porque cada uno de estos, retorna sólo un valor distinto por grupo. Pero si desea referirse a elementos de las tablas originarias que no aparecen en la lista GROUP BY, debe aplicarles una función agregada. Así es como se puede estar seguro de que la expresión retorna un solo valor por grupo. Como ejemplo, la siguiente consulta no es válida.

SELECT S.shipperid, S.companyname, COUNT(*) AS numorders

FROM Sales.Shippers AS S

  JOIN Sales.Orders AS O

    ON S.shipperid = O.shipperid

GROUP BY S.shipperid;

Esta consulta genera el siguiente error.

Mens. 8120, Nivel 16, Estado 1, Línea 1

La columna 'Sales.Shippers.companyname' de la lista de selección no es válida, porque no está contenida en una función de agregado ni en la cláusula GROUP BY.

A pesar de que sabe que no puede haber más de un nombre de compañía distinto por cada shipperid distinto, T-SQL no lo sabe. Debido a que la columna S.companyname nunca aparece en la lista GROUP BY ni es contenida en una función agregada, no está permitida en las cláusulas HAVING, SELECT y ORDER BY.

Puede utilizar un número de soluciones. Una solución es agregar la columna S.companyname a la lista GROUP BY, de la siguiente manera.

SELECT S.shipperid, S.companyname, COUNT(*) AS numpedidos

FROM Sales.Shippers AS S

  INNER JOIN Sales.Orders AS O

    ON S.shipperid = O.shipperid

GROUP BY S.shipperid, S.companyname;

Esta consulta genera la siguiente salida.

shipperid   companyname    numpedidos

----------- -------------- -----------

1            Shipper GVSUA  249

2            Shipper ETYNR  326

3            Shipper ZHISN  255

Otra solución es aplicar una función agregada como MAX a la columna, de la siguiente manera.

SELECT S.shipperid,

  MAX(S.companyname) AS empresaenvio,

  COUNT(*) AS numorders

FROM Sales.Shippers AS S

  INNER JOIN Sales.Orders AS O

    ON S.shipperid = O.shipperid

GROUP BY S.shipperid;

En este caso, la función agregada es artificial, porque no puede haber más de un nombre de compañía distinto por cada shipperid distinto. La primera solución, sin embargo, tiende a producir los planes más óptimos, y también parece ser la solución más natural.

La tercera solución es agrupar y agregar las filas de la tabla Orders primero, definir una expresión de tabla basado en la consulta agrupada, y luego unir la expresión de tabla con la tabla Shippers para obtener los nombres de compañías de envíos. Aquí está el código de la solución.

WITH C AS

(

  SELECT shipperid, COUNT(*) AS numpedidos

  FROM Sales.Orders

  GROUP BY shipperid

)

SELECT S.shipperid, S.companyname, numpedidos

FROM Sales.Shippers AS S  

  INNER JOIN C 

    ON S.shipperid = C.shipperid;

SQL Server generalmente optimiza la tercera solución, como lo hace con la primera. La primera solución podría ser preferible, ya que implica mucho menos código.

Trabajando con Varios Conjuntos de Agrupación

Con T-SQL, puede definir varios conjuntos de agrupación en la misma consulta. En otras palabras, puede utilizar una consulta para agrupar los datos en más de una forma. T-SQL soporta tres cláusulas que permiten definir varios conjuntos de agrupamiento: GROUPING SETS, CUBE y ROLLUP. Los utilizará en la cláusula GROUP BY.

Puede utilizar la cláusula GROUPING SETS para listar todos los conjuntos de agrupación que desea definir en la consulta. Como ejemplo, la siguiente consulta define cuatro conjuntos de agrupación.

SELECT shipperid, YEAR(shippeddate) AS annoenvio, COUNT(*) AS numpedidos

FROM Sales.Orders

GROUP BY GROUPING SETS

(

  (shipperid, YEAR(shippeddate)   ),

  (shipperid                      ),

  (YEAR(shippeddate)              ),

  (                               )

);

Lista los conjuntos de agrupación separados por comas en el par exterior de paréntesis, pertenecientes a la cláusula GROUPING SETS. Se utiliza un par interior de paréntesis para encerrar cada conjunto de agrupación. Si no indica un par interior de paréntesis, cada elemento individual es considerado un conjunto de agrupación separado.

Esta consulta define cuatro conjuntos de agrupación. Uno de ellos es el conjunto de agrupación vacío, que define un grupo con todas las filas para el cálculo de grandes agregados. La consulta genera la siguiente salida.

shipperid   annoenvio   numpedidos

----------- ----------- -----------

1            NULL         4

2            NULL         11

3            NULL         6

NULL         NULL         21

1            2006         36

2            2006         56

3            2006         51

NULL         2006         143

1            2007         130

2            2007         143

3            2007         125

NULL         2007         398

1            2008         79

2            2008         116

3            2008         73

NULL         2008         268

NULL         NULL         830

1            NULL         249

2            NULL         326

3            NULL         255

La salida combina los resultados de agrupación y agregación de los datos de cuatro conjuntos de agrupación diferentes. Como puede ver en la salida, los NULLs son utilizados como marcadores de posición en filas donde un elemento no es parte del conjunto de agrupaciones. Por ejemplo, en las filas de resultados que están asociados con el conjunto de agrupación (shipperid), la columna de resultado annoenvio se establece a NULL. Del mismo modo, en las filas que están asociados con el conjunto de agrupación (YEAR(shippeddate)), la columna shipperid se establece a NULL.

Podría conseguir el mismo resultado escribiendo cuatro consultas agrupadas separadas, cada una definiendo solo un único conjunto de agrupación, y unificando sus resultados con un operador UNION ALL. Sin embargo, esta solución implicaría mucho más código y no estaría optimizada tan eficientemente como la consulta con la cláusula GROUPING SETS.

T-SQL soporta dos cláusulas adicionales llamadas CUBE y ROLLUP, que se pueden considerar como abreviaciones de la cláusula GROUPING SETS. La cláusula CUBE acepta una lista de expresiones como entradas y define todos los conjuntos de agrupación posibles que pueden ser generados de las entradas, incluyendo el conjunto de agrupación vacío. Por ejemplo, la siguiente consulta es un equivalente lógico de la consulta previa que utiliza la cláusula GROUPING SETS.

SELECT shipperid, YEAR(shippeddate) AS shipyear, COUNT(*) AS numorders

FROM Sales.Orders

GROUP BY CUBE(shipperid, YEAR(shippeddate));

La cláusula CUBE define los cuatro posibles conjuntos de agrupación de las dos entradas:

1.    (shipperid, YEAR(shippeddate))

2.    (shipperid)

3.    (YEAR(shippeddate))

4.    ( )

La cláusula ROLLUP es también una abreviación de la cláusula GROUPING SETS, pero lo utiliza cuando hay una jerarquía formada por los elementos de entrada. En tal caso, sólo un subconjunto de los posibles conjuntos de agrupación es realmente interesante. Considere, por ejemplo, una jerarquía de ubicación hecha de los elementos shipcountry, shipregion y shipcity, en este orden. Solo es interesante cubrir los datos en una sola dirección, calculando agregados para los siguientes conjuntos de agrupación:

1.    (shipcountry, shipregion, shipcity)

2.    (shipcountry, shipregion)

3.    (shipcountry)

4.    ( )

Los otros conjuntos de agrupación simplemente no son interesantes. Por ejemplo, a pesar de que el mismo nombre de ciudad puede aparecer en diferentes lugares del mundo, no es interesante agregar todas las ocurrencias, independientemente de la región y el país.

Así que, cuando los elementos forman una jerarquía, se utiliza la cláusula ROLLUP y de esta manera evita calcular agregados innecesarios. He aquí un ejemplo de una consulta utilizando la cláusula ROLLUP basada en la jerarquía antes mencionada.

SELECT shipcountry, shipregion, shipcity, COUNT(*) AS numpedidos

FROM Sales.Orders

GROUP BY ROLLUP(shipcountry, shipregion, shipcity);

Esta consulta genera la siguiente salida (que se muestra aquí en forma abreviada).

shipcountry    shipregion       shipcity        numpedidos

--------------- --------------- --------------- -----------

Argentina       NULL             Buenos Aires        16

Argentina       NULL             NULL                16

Argentina       NULL             NULL                16

...

USA             AK               Anchorage           10

USA             AK               NULL                10

USA             CA               San Francisco       4

USA             CA               NULL                4

USA             ID               Boise               31

USA             ID               NULL                31

...

USA             NULL             NULL                122

...

NULL            NULL             NULL                830

Como se ha mencionado, los NULLs son utilizados como marcadores de posición, cuando un elemento no es parte del conjunto de agrupación. Si todas las columnas agrupadas no aceptan NULLs en la tabla originaria, puede identificar las filas que están asociados con un único conjunto de agrupación, basado en una combinación única de NULLs y no NULLs en estas columnas. Un problema surge en la identificación de las filas que están asociadas con un único conjunto de agrupación, cuando una columna agrupada permite NULLs, como es el caso con la columna shipregion. ¿Cómo saber si un NULL en el resultado representa un marcador de posición (significando "todas las regiones") o un NULL original de la tabla (significando "región inaplicable")? T-SQL proporciona dos funciones para ayudar a resolver este problema: GROUPING y GROUPING_ID.

La función GROUPING acepta un solo elemento como entrada y retorna 0 cuando el elemento es parte del conjunto de agrupación y 1 cuando no lo es. La siguiente consulta muestra el uso de la función de agrupación.

SELECT

  shipcountry, GROUPING(shipcountry) AS grplocation,

  shipregion, GROUPING(shipregion) AS grplocation,

  shipcity, GROUPING(shipcity) AS grplocation,

  COUNT(*) AS numpedidos

FROM Sales.Orders

GROUP BY ROLLUP(shipcountry, shipregion, shipcity);

Esta consulta genera la siguiente salida (que se muestra aquí en forma abreviada).

shipcountry  grplocation shipregion  grplocation shipcity      grplocation numpedidos

------------ ---------- ------------ ---------- -------------- ---------- ---------

Argentina    0           NULL      0          Buenos Aires      0         16

Argentina    0           NULL      0          NULL              1         16

Argentina    0           NULL      1          NULL              1         16

...

USA          0           AK        0          Anchorage         0         10

USA          0           AK        0          NULL              1         10

USA          0           CA        0          San Francisco     0         4

USA          0           CA        0          NULL              1         4

USA          0           ID        0          Boise             0         31

USA          0           ID        0          NULL              1         31

...

USA          0           NULL      1          NULL              1         122

...

NULL         1           NULL      1          NULL              1         830

Ahora puede identificar un conjunto de agrupación por la búsqueda de 0s en los elementos que son parte del conjunto de agrupación y 1s en el resto.

Otra función que puede utilizar para identificar los conjuntos de agrupación es GROUPING_ID. Esta función acepta la lista de columnas agrupadas como entradas y retorna un entero representando un bitmap. El bit más a la derecha representa la entrada más a la derecha. El bit es 0 cuando el elemento respectivo es parte del conjunto de agrupación y 1 cuando no lo es. Cada bit representa 2 elevado a la potencia de la posición del bit menos 1; por lo que el bit más a la derecha representa 1, el de su izquierda 2, luego 4, a continuación, 8, y así sucesivamente. El entero resultante es la suma de los valores que representan elementos que no son parte del conjunto de agrupación debido a que sus bits están activados. He aquí una consulta que demuestra el uso de esta función.

SELECT GROUPING_ID(shipcountry, shipregion, shipcity) AS grp_id,

shipcountry, shipregion, shipcity,

COUNT(*) AS numpedidos

FROM Sales.Orders

GROUP BY ROLLUP(shipcountry, shipregion, shipcity);

Esta consulta genera la siguiente salida (mostrada aquí en forma abreviada).

grp_id       shipcountry   shipregion        shipcity       numpedidos

----------- --------------- --------------- --------------- -----------

0            Argentina      NULL              Buenos Aires        16

1            Argentina      NULL              NULL                16

3            Argentina      NULL              NULL                16

...

0            USA            AK                Anchorage           10

1            USA            AK                NULL                10

0            USA            CA                San Francisco       4

1            USA            CA                NULL                4

0            USA            ID                Boise               31

1            USA            ID                NULL                31

...

3            USA            NULL              NULL                122

...

7            NULL           NULL              NULL                830

La última fila de esta salida representa el conjunto de agrupación vacía, ninguno de los tres elementos es parte del conjunto de agrupaciones. Por lo tanto, los bits respectivos (valores 1, 2, y 4) están activados. La suma de los valores que esos bits representan es 7.

Algebra de Conjuntos de Agrupación

Puede especificar varias cláusulas GROUPING SETS, CUBE, ROLLUP en la cláusula GROUP BY separadas por comas. De esta manera, consigue un efecto multiplicador. Por ejemplo, la cláusula CUBE(a, b, c) define ocho conjuntos de agrupación y la cláusula ROLLUP(x, y, z) define cuatro conjuntos de agrupación. Al especificar una coma entre los dos, como en CUBE (a, b, c), ROLLUP (x, y, z), son multiplicados y obtiene 32 conjuntos de agrupación.

Ejercicio 1: Agregar Información Acerca de Pedidos de Clientes

En este ejercicio, agrupa y agrega datos que implican clientes y pedidos. Cuando se le da una tarea, prueba primero con su propia solución de consulta antes de mirar a la consulta proporcionada.

1.    Abra el SSMS y conéctese a la base de datos de muestra TSQL2012.

2.    Escriba una consulta que calcule el número de pedidos por cada cliente para los clientes de España.

Para lograr esta tarea, primero necesita unir las tablas Sales.Customers y Sales.Orders basados en coincidencias entre el custid del cliente y el custid del pedido. Entonces, filtrar sólo las filas donde el país del cliente es España. Entonces agrupar las filas restantes por custid. Porque hay una columna custid en ambas tablas de entrada, necesita dar prefijo a la columna con el origen de tabla. Por ejemplo, si prefiere utilizar la tabla Sales.Customers, y darle alias de C a esa tabla, necesita especificar C.custid en la cláusula GROUP BY. Por último, retornar el custid y el conteo de filas en la lista SELECT. A continuación mostramos la consulta completa.

USE TSQL2012;

SELECT C.custid, COUNT(*) AS numpedidos

FROM Sales.Customers AS C

  INNER JOIN Sales.Orders AS O

    ON C.custid = O.custid

WHERE C.country = N'Spain'

GROUP BY C.custid;

Esta consulta genera la siguiente salida.

custid       numpedidos

----------- -----------

8            3

29           5

30           10

69           5

3.    Agregue la información de la ciudad en la salida de la consulta. En primer lugar, trate de solo agregar C.city a la lista SELECT, como sigue.

SELECT C.custid, C.city, COUNT(*) AS numpedidos

FROM Sales.Customers AS C

  INNER JOIN Sales.Orders AS O

    ON C.custid = O.custid

WHERE C.country = N'Spain'

GROUP BY C.custid;

Obtiene el siguiente error.

Mens. 8120, Nivel 16, Estado 1, Línea 1

La columna 'Sales.Customers.city' de la lista de selección no es válida, porque no está contenida en una función agregada ni en la cláusula GROUP BY.

4.    Encontrar una solución que podría retornar la ciudad también.

Una posible solución es agregar la ciudad a la cláusula GROUP BY, como sigue.

SELECT C.custid, C.city, COUNT(*) AS numpedidos

FROM Sales.Customers AS C

  INNER JOIN Sales.Orders AS O

    ON C.custid = O.custid

WHERE C.country = N'Spain'

GROUP BY C.custid, C.city;

Esta consulta genera la siguiente salida.

custid       city             numpedidos

----------- --------------- -----------

8            Madrid           3

29           Barcelona        5

30           Sevilla          10

69           Madrid           5

Ejercicio 2: Definir Varios Conjuntos de Agrupación

En este ejercicio, se definen varios conjuntos de agrupación.

·         Su punto de partida es la consulta que escribió en el paso 4 del Ejercicio 1. Además de los conteos por cliente retornado por esta consulta, también incluye en la misma salida el conteo total. Necesita que la salida muestre primero los conteos por cliente y luego el conteo total.

Puede utilizar la cláusula GROUPING SETS para definir dos conjuntos de agrupación: uno para (C.custid, C.city), y otro para el conjunto de agrupación vacío (). Para ordenar los conteos de clientes antes del conteo total, ordenar los datos por GROUPING(C.custid). Aquí está la consulta completa.

SELECT C.custid, C.city, COUNT(*) AS numpedidos

FROM Sales.Customers AS C

  INNER JOIN Sales.Orders AS O

    ON C.custid = O.custid

WHERE C.country = N'Spain'

GROUP BY GROUPING SETS((C.custid, C.city),())

ORDER BY GROUPING(C.custid);

Esta consulta genera el siguiente resultado.

custid       city             numpedidos

----------- --------------- -----------

8            Madrid           3

29           Barcelona        5

30           Sevilla          10

69           Madrid           5

NULL         NULL             23