SQL Server

4 nov, 2019

Agrupando Dados com GROUPING SETS

100 visualizações
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.