SQL Server

4 nov, 2019

Agrupando Dados com GROUPING SETS

Publicidade

Chega ser estranho pensar que trabalhemos com tanta frequência com sumarização de dados e ainda assim a opção GROUPING SETS seja uma ilustre desconhecida para a maioria dos mortais.

Uma explicação provável é que pouca gente entende de fato o seu potencial e, por esta razão, ela seria relegada ao segundo plano no mundo dos desenvolvedores de bancos de dados.

Pode até parecer à primeira vista que os GROUPING SETS sejam facilmente substituíveis por operadores mais antigos como os ROLLUP e CUBE. Mas é exatamente aí que você se engana ?.

SQL Server

Relembrando os Operadores ROLLUP e CUBE

Os operadores ROLLUP e CUBE existem no T-SQL desde as primeiras versões do SQL Server. Ambos funcionam em conjunto com a cláusula GROUP BY e a funcionalidade dos dois é bem parecida:

GROUP BY […] WITH ROLLUP: acrescenta à listagem de sumarização os totais parciais calculados da esquerda para direita. Se a consulta fizer um agrupamento sobre uma única coluna, o ROLLUP acrescenta o total geral para esta coluna. Quando são duas colunas, acrescenta o total geral e mais o total geral para cada valor da coluna 1, como mostra a imagem a seguir.

     

GROUP BY […] WITH CUBE: a diferença do CUBE é que ele acrescenta à listagem todas as possíveis combinações de totais parciais relacionadas aos valores distintos de cada coluna de agrupamento. A figura mostra esta ideia. Observe que existem mais linhas de totais parciais neste relatório, que são os totais por Mês

Enquanto trabalhamos com 2 colunas, é tudo muito simples. Com 3 colunas agrupadas, a complexidade é consideravelmente maior e ela cresce exponencialmente à medida que se acrescentam novas colunas. Não é raro que relatórios com 4 ou mais colunas se tornem tão complexos e lentos que simplesmente inviabiliza o uso destes operadores.

Os Desconhecidos GROUPING SETS

Apesar de pouco conhecida, esta opção da cláusula GROUP BY faz parte do padrão ANSI SQL 2006 e existe no SQL Server desde a versão SQL 2008! E ela é realmente bem fácil de usar.

A forma mais simples de explicar os GROUPING SETS é comparando-os com os operadores ROLLUP e CUBE. E a vantagem de usá-los está exatamente no controle que eles nos dão sobre o cálculo de totais parciais. Em outras palavras, você pode facilmente criar um relatório parecido com os que geramos com operadores ROLLUP e CUBE, mas GROUPING SETS permite que você tenha controle sobrequais totais parciaisserãocalculados.

Assim, a utilidade dos GROUPING SETS costuma aparecer apenas quando se trabalha com relatórios mais complexos. Para começar, vamos reescrever as consultas com ROLLUP e com CUBE para usar GROUPING SETS na geração de relatórios idênticosaos anteriores.

Sua sintaxe básica é esta:

 Exemplo 1: GROUPING SETS substituindo operador ROLLUP

Observe que, para simularmos os subtotais exibidos em um ROLLUP, precisamos informar um set com a combinação das colunas de GROUP BY (Estado vs Mês), um set com os subtotais para a primeira coluna do GROUP BY (Estado) e um terceiro set com o consolidado geral (que é expresso como um set vazio).

A declaração SQL e o relatório gerado são apresentados a seguir.

Exemplo 2: GROUPING SETS substituindo operador CUBE

Para simular o resultado de um operador CUBE em um relatório de 2 dimensões, só precisamos acrescentar mais um set, com os subtotais da coluna Mês.

     

Exemplo 3: GROUPING SETS exibindo total geral e os subtotais da coluna 1.

Considere, neste caso, que temos um relatório com 03 colunas sumarizadas, que são Produto, Estado e Mês. Queremos exibir só os totais parciais por Produto, mais o total geral.

Acontece que um relatório de 03 colunas é muito mais complexo do que os que vimos até aqui. Existe um número muito maior de combinações entre as colunas, além de que o tamanho do relatório depende da quantidade de itens distintos existem em cada uma das três colunas.

Considere um relatório com as colunas Produto, Estado e Mês. Tecnicamente, teremos os seguintes “sets” possíveis:

(Produto, Estado, Mês): nível do GROUP BY

(Produto, Estado): subtotais desta combinação de colunas

(Produto, Mês): subtotais desta combinação de colunas

(Estado, Mês): subtotais desta combinação de colunas

(Produto): subtotais desta coluna isolada

(Estado): subtotais desta coluna isolada

(Mês): subtotais desta coluna isolada

(): total geral

Se cada uma destas três colunas tivesse só 10 elementos distintos, o relatório todo teria ((10 x 10 x 10) + (10 x 10) + (10 x 10) + (10 x 10) + (10) + (10) + (10) + (1)), ou seja, 1331 linhas. Acontece que, no mundo real, geralmente as colunas tem muito mais do que apenas 10 elementos distintos ?.

Se você achou este relatório complexo, faça as contas para outro relatório com 04 colunas agrupadas. Agora sim você está pronto para entender a vantagem de se usar GROUPING SETS!

Retomando o exemplo, neste relatório 03 colunas agrupadas, queremos apenas os resultados do GROUP BY normal, mais os subtotais de produto e total geral. Com GROUPING SETS a solução é muito simples. Observe que mesmo mostrando só os subtotais por Produto, este relatório já chega 51.773 linhas. (Apenas para comparar, o mesmo relatório com o operador CUBE tem 65.462 linhas)!

GROUPING SETS e Performance

Uma característica especialmente interessante dos GROUPING SETS é que o custo de execução da consulta é proporcional à quantidade de subtotais solicitados. Portanto o “engine” do SQL Server é suficientemente esperto para calcular apenas o que é necessário.

Veja estes 03 exemplos:

Caso 1: Plano de Execução para um GROUP BY simples

Custo total é de 3,53849

Caso 2: Plano de Execução usando GROUPING SETS apenas para coluna Produto

Custo total é de 4,00761, pois foram adicionados 220 subtotais

Caso 3: Plano de Execução usando GROUPING SETS para colunas Produto e Estado

Custo total é de 4,54408, pois foram acrescentados mais 49 subtotais em relação ao exemplo anterior

Comentários Finais

A opção GROUPING SETS facilita muito a geração de subtotais em relatórios SQL, permitindo que o desenvolvedor tenha muito mais controle sobre o relatório gerado. A performance segue dentro dos padrões esperados, tendo em vista que só os subtotais desejados são calculados durante a execução da consulta.

Leituras Sugeridas

SELECT – GROUP BY – Transact-SQL por MICROSOFT.

GROUPING SETS in SQL Server 2008 por Craig Freedman.