Escritura de consultas que especifiquen varias agrupaciones con conjuntos de agrupación

  • 7 minutos

La cláusula GROUP BY de una instrucción SELECT en Transact-SQL se usa para organizar filas en grupos, normalmente para admitir agregaciones. Sin embargo, si necesita agrupar por atributos diferentes al mismo tiempo, por ejemplo, para informar en distintos niveles, normalmente necesitará varias consultas combinadas con UNION ALL. En su lugar, si necesita generar agregados de varias agrupaciones en la misma consulta, puede usar la subcláusula GROUPING SETS de la cláusula GROUP BY en Transact-SQL. GROUPING SETS proporciona una alternativa al uso de UNION ALL para combinar los resultados de varias consultas individuales, cada una con su propia cláusula GROUP BY.

Uso de la subcláusula GROUPING SETS

Para usar GROUPING SETS, especifique las combinaciones de atributos en los que se va a agrupar, como en el ejemplo de sintaxis siguiente:

SELECT <column list with aggregate(s)>
FROM <source>
GROUP BY 
GROUPING SETS(
    (<column_name>),--one or more columns
    (<column_name>),--one or more columns
    () -- empty parentheses if aggregating all rows
        );

Por ejemplo, supongamos que quiere usar GROUPING SETS para agregar en las columnas Category y Cust de una tabla Sales.CategorySales, además de la notación de paréntesis vacíos para agregar todas las filas:

SELECT Category, Cust, SUM(Qty) AS TotalQty
FROM Sales.CategorySales
GROUP BY 
    GROUPING SETS((Category),(Cust),())
ORDER BY Category, Cust;

El resultado podría tener este aspecto:

Categoría ID CantidadTotal
NULO NULO 999
NULO 1 80
NULO 2 12
NULO 3 154
NULO 4 241
NULO 5 512
Bebidas NULO 513
Condimentos NULO 114
Repostería NULO 372

Observe la presencia de direcciones URL en los resultados. Los VALORES NULL se pueden devolver porque se ha almacenado un valor NULL en el origen subyacente o porque es un marcador de posición en una fila generada como resultado agregado. Por ejemplo, en los resultados anteriores, la primera fila muestra NULL, NULL, 999. Esto representa una fila total general. Los valores NULL de las columnas Category y Cust son marcadores de posición porque ni Category ni Cust participan en la agregación.

Sugerencia

Si desea saber si un valor NULL marca un marcador de posición o procede de los datos subyacentes, puede usar GROUPING_ID. Visite la página de referencia de GROUPING_ID para más información.

Uso de las subcláusulas CUBE y ROLLUP

Al igual que GROUPING SETS, las subcláusulas CUBE y ROLLUP también permiten varias agrupaciones para agregar datos. Sin embargo, CUBE y ROLLUP no necesitan especificar cada conjunto de atributos para agrupar. En su lugar, dado un conjunto de columnas, CUBE determinará todas las combinaciones y agrupaciones de salida posibles. ROLLUP crea combinaciones, suponiendo que las columnas de entrada representan una jerarquía. Por lo tanto, CUBE y ROLLUP se pueden ver como accesos directos a GROUPING SETS.

Para usar CUBE, anexe la palabra clave CUBE a la cláusula GROUP BY y proporcione una lista de columnas para agrupar. Por ejemplo, para agrupar en todas las combinaciones de columnas Category y Cust, usaría la sintaxis siguiente en la consulta:

SELECT Category, Cust, SUM(Qty) AS TotalQty
FROM Sales.CategorySales
GROUP BY CUBE(Category,Cust);

Esto genera agrupaciones para las combinaciones siguientes: (Category, Cust), (Cust, Category), (Cust), (Category) y el agregado en todos los () vacíos:

Categoría ID CantidadTotal
Bebidas 1 36
Condimentos 1 44
NULO 1 80
Bebidas 2 5
Repostería 2 7
NULO 2 12
Bebidas 3 105
Condimentos 3 4
Repostería 3 45
NULO 3 154
...
NULO NULO 999
Bebidas NULO 513
Condimentos NULO 114
Repostería NULO 372

Para usar ROLLUP, anexaría la palabra clave ROLLUP a la cláusula GROUP BY y proporcionaría una lista de columnas para agrupar. Por ejemplo, para agrupar las combinaciones de las columnas Category, Subcategory y Product, usaría la siguiente sintaxis en la consulta:

SELECT Category, Subcategory, Product, SUM(Qty) AS TotalQty
FROM Sales.ProductSales
GROUP BY ROLLUP(Category,Subcategory, Product);

Esto generaría agrupaciones para las combinaciones siguientes: (Category, Subcategory, Product), (Category, Subcategory), (Category) y el agregado en todos los () vacíos. El orden en el que se proporcionan las columnas es importante: ROLLUP supone que las columnas se enumeran en un orden que expresa una jerarquía. Proporciona subtotales para cada agrupación, junto con un total general para todas las agrupaciones al final.