Escrever consultas que especificam vários agrupamentos com conjuntos de agrupamentos

  • 7 minutos

Use a cláusula GROUP BY em uma instrução SELECT no Transact-SQL para organizar linhas em grupos, normalmente para dar suporte a agregações. No entanto, se você precisar agrupar por diferentes atributos ao mesmo tempo, por exemplo, para relatar em níveis diferentes, normalmente precisará de várias consultas combinadas com UNION ALL. Em vez disso, se você precisar produzir agregações de vários agrupamentos na mesma consulta, poderá usar a subcláusula GROUPING SETS da cláusula GROUP BY no Transact-SQL. GROUPING SETS fornece uma alternativa ao uso de UNION ALL para combinar resultados de várias consultas individuais, cada uma com sua própria cláusula GROUP BY.

Use a subcláusula GROUPING SETS

Para usar GROUPING SETS, especifique as combinações de atributos nos quais agrupar, como no exemplo de sintaxe a seguir:

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 exemplo, suponha que você queira usar GROUPING SETS para agregar nas colunas Category e Cust de uma tabela Sales.CategorySales , além da notação entre parênteses vazios para agregar todas as linhas:

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

O resultado pode ser assim:

Categoria Cust TotalQty
NULL NULL 999
NULL 1 80
NULL 2 12
NULL 3 154
NULL 4 241
NULL 5 512
Bebidas NULL 513
Condimentos NULL 114
Confeitaria NULL 372

Observe a presença de NULLs nos resultados. NULLs podem ser retornados porque um NULL foi armazenado na fonte subjacente ou porque é um espaço reservado em uma linha gerada como um resultado agregado. Por exemplo, nos resultados anteriores, a primeira linha exibe NULL, NULL, 999. Isso representa uma linha total geral. O NULL nas colunas Categoria e Cust são espaços reservados porque nem Categoria nem Cust participam da agregação.

Gorjeta

Se você quiser saber se um NULL marca um espaço reservado ou vem dos dados subjacentes, você pode usar GROUPING_ID. Visite a página de referência para GROUPING_ID para obter mais informações.

Use as subcláusulas CUBE e ROLLUP

Como GROUPING SETS, as subcláusulas CUBE e ROLLUP também permitem vários agrupamentos para agregar dados. No entanto, CUBE e ROLLUP não precisam que você especifique cada conjunto de atributos para agrupar. Em vez disso, dado um conjunto de colunas, o CUBE determinará todas as combinações e agrupamentos de saída possíveis. O ROLLUP cria combinações, assumindo que as colunas de entrada representam uma hierarquia. Portanto, CUBE e ROLLUP podem ser pensados como atalhos para AGRUPAR SETS.

Para usar CUBE, acrescente a palavra-chave CUBE à cláusula GROUP BY e forneça uma lista de colunas para agrupar. Por exemplo, para agrupar todas as combinações de colunas Categoria e Cust, use a seguinte sintaxe na consulta:

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

Isto produz agrupamentos para as seguintes combinações: (Categoria, Cust), (Cust, Categoria), (Cust), (Categoria) e o agregado em todos os vazios ():

Categoria Cust TotalQty
Bebidas 1 36
Condimentos 1 44
NULL 1 80
Bebidas 2 5
Confeitaria 2 7
NULL 2 12
Bebidas 3 105
Condimentos 3 4
Confeitaria 3 45
NULL 3 154
...
NULL NULL 999
Bebidas NULL 513
Condimentos NULL 114
Confeitaria NULL 372

Para usar ROLLUP, você acrescentaria a palavra-chave ROLLUP à cláusula GROUP BY e forneceria uma lista de colunas para o grupo. Por exemplo, para agrupar combinações das colunas Categoria, Subcategoria e Produto, use a seguinte sintaxe na consulta:

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

Isso resultaria em agrupamentos para as seguintes combinações: (Categoria, Subcategoria, Produto), (Categoria, Subcategoria), (Categoria) e o agregado em todos os vazios (). A ordem em que as colunas são fornecidas importa: ROLLUP assume que as colunas são listadas em uma ordem que expressa uma hierarquia. Fornece subtotais para cada agrupamento, juntamente com um total geral para todos os agrupamentos no final.