Realizando agrupamento de dados com Grouping Sets no SQL Server 2008 e R2. – Junior Galvão – MVP – Data Platform
Junior Galvão - MVP	Dicas, VIRTUAL PASS BR	 18 18America/Sao_Paulo maio 18America/Sao_Paulo 2011 18 18America/Sao_Paulo maio 18America/Sao_Paulo 2011 7 minutos
Quando pensamos em realizar a agregação de dados armazenados em nossas tabelas, estamos na verdade planejando utilizar e aplicar o agrupamento de dados, criando, subtotais, média de valores, entre outros tipos de resultados.
Até o SQL Server 2005, era possível realizar agregação ou agrupamento de valores através da claúsula Group By, definindo um simples conjunto de valores agrupados. Outra possibilidade existente seria utilizar o operador Union para realizar a junção de nossas querys formando este conjunto de agrupamento de valores, o que normalmente representava um custo de processamento por parte do SQL Server um pouco maior.
Pois bem, a partir do SQL Server 2008 foi introduzida uma nova funcionalidade chamada Grouping Sets, ou
conjunto de agrupamento, o que mudou de forma radical todas as possibilidades existentes para se criar e desenvolver agrupamento de valores em bancos de dados relacionais.
Através desta nova funcionalidade temos a possibilidade de utilizar algumas das técnicas aplicadas a bases de dados análiticas(OLAP), como por exemplo um data warehouse criado no SQL Server Analysis Services, criando os conjuntos de agrupamento de valores na mesma instrução T-SQL, agregando valores por anos, meses, dias, entre outras formas.
A partir da implementação do Grouping Sets como operador da claúsula Group By, foram também acrescentados outros dois operadores que podem ser utilizados em conjunto com o Group by, conhecidos como: Rollup e Cube. Os operadores ROLLUP, CUBE ou GROUPING SETS podem gerar o mesmo conjunto de resultados como ao usar UNION ALL para combinar
agrupamentos de consultas individuais; entretanto, o uso de um operador GROUP BY normalmente é mais eficiente.
O operador GROUPING SETS pode gerar o mesmo conjunto de resultados gerado por uma operação simples de GROUP BY, ROLLUP ou CUBE. Quando todos os agrupamentos gerados pelo uso de operadores ROLLUP ou CUBE não são necessários,
você pode usar GROUPING SETS para especificar somente os agrupamentos que quiser usar.
Importante destacar que em nenhum momento os operadores Grouping Sets, Rollup e Cube possuem a finalidade de substituir qualquer tipo de funções ou recursos existente nos processos executados pelo SSAS em suas bases de dados multidimensionais, ao qual aplicamos a Linguagem MDX(Multimensional Expressions), sendo esta, considerada uma linguagem de alta performance para processos de manipulação de dados em grandes bancos de dados analíticos.
Neste artigo, vou destacar a utilização do operador Grouping Sets, suas formas de utilização, com criar agregação de valores com parâmetros simples e múltiplos e utilizar operador Union All simulando o mesmo resultado do Grouping Sets.
Para ilustrar e demonstrar a utilização do Grouping Sets vamos criar um simples ambiente de trabalho, utilizando a Tabela OrdemProducao, composta pela estrutura apresentada a seguir na Listagem 1.
— Listagem 1 – Criação da Tabela OrdemProducao e sua estrutura de campos –
If OBJECT_ID('dbo.OrdemProducao','U') Is Not Null
Drop Table dbo.OrdemProducao
Create Table OrdemProducao
(NumProducao Int Not Null,
CodFuncionario Int Not Null,
Setor Varchar(2) Not Null,
Quantidade Int Not Null
Alter Table OrdemProducao
Add Constraint [PK_OrdemProducao_NumProducao] Primary Key Clustered (NumProducao)
Nossa tabela OrdemProducao foi criada e sua estrutura já esta pronta para receber os dados necessários para nosso ambiente, vale ressaltar que a coluna NumProducao foi definida como chave primária clusterizada desta tabela.
Vamos continuar preparando nosso ambiente, neste próximo passo, realizaremos a inserção da massa de dados na tabela OrdemProducao para posteriormente criarmos os agrupamento de valores, conforme apresenta a Listagem 2.
— Listagem 2 – Inserção de dados na Tabela OrdemProducao —
Insert Into OrdemProducao (NumProducao, Data, CodFuncionario, Setor, Quantidade)
(1000, '20091001',1,'A',20),
(1001, '20091002',2,'B',30),
(1002, '20091003',3,'D',15),
(1003, '20091005',2,'D',12),
(2000, '20100210',4,'C',40),
(2001, '20100211',4,'C',35),
(2002, '20100312',2,'A',22),
(2004, '20100414',2,'B',12),
(3005, '20110601',1,'C',50),
(3006, '20110602',3,'B',60)
(10000, '20091001',1,'A',20),
(10001, '20091002',2,'B',30),
(10002, '20091003',3,'D',15),
(10003, '20091005',2,'D',12),
(20000, '20100210',4,'C',40),
(20001, '20100211',4,'C',35),
(20002, '20100312',2,'A',22),
(20004, '20100414',2,'B',12),
(30005, '20110601',1,'C',50),
(30006, '20110602',3,'B',60)
Os dados foram inseridos em nossa tabela, agora podemos começar a utilizar o Grouping Sets, inicialmente realizaremos
o agrupamento com um conjunto simples, através das colunas: Setor, Codfuncionario e Data. Observação: A coluna Data será utilizada somente o ano de produção, conforme apresenta a Listagem 3.
Listagem 3 – Utilizando o Grouping Sets para agrupamento com conjunto simples
de dados –
Select Setor, CodFuncionario, YEAR(Data) As 'Ano de Produção', SUM(Quantidade) As Soma
from dbo.OrdemProducao
(Setor, CodFuncionario, YEAR(DATA)) –Definindo o Agrupamento simples.
Após executarmos a Listagem 3, podemos observar o resultado gerado pelo SQL Server através da Figura 1, como definimos em nosso código, o agrupamento foi realizado nas colunas Setor, CodFuncionar e Ano dentro da coluna Data. Observe que o
Grouping Sets respeito a nossa definição de hierarquias de grupos, realizando a ordenação do Resultado com base nas declarações, o que nada impede em utilizar o comando Order By.
Figura 1 – Resultado após a execução do Grouping Sets(Setor, CodFuncionario, Year(Data)).
Neste código, ficou fácil entender como o Grouping Sets esta sendo utilizado, mas por outro lado caso tenhamos a necessidade de realizar o mesmo agrupamento de valores, mas aplicando um novo conjunto de agrupamento, como por exemplo, obter a soma do total de produção realizada por ano?
Para responder esta pergunta, vamos utilizar a Listagem 4, que apresenta o Grouping Sets com múltiplos conjuntos de agrupamentos, neste caso:
Primerio conjunto: (Setor, CodFuncionario, Year(Data)),
Segundo conjunto: (Year(Data)).
— Listagem 4 – Utilizando o Grouping Sets para agrupamentos com múltiplos conjuntos de dados –
(Setor, CodFuncionario, YEAR(DATA)), — Primeiro Agrupamento
(YEAR(Data)) – Segundo Agrupamento
Figura 2 – Resultado após a execução do Grouping Sets(Setor, CodFuncionario, Year(Data), Year(Data)).
Após executar a Listagem 4, conforme apresenta a Figura 2, foi possível observar o agrupamento dos dados e a totalização da produção por Ano.
Mas não devemos entender que aplicar agrupamentos múltiplos nos ajudam a criar os totais ou subtotais, o que na verdade isso não acontece de forma tão simples. Para que estes resultados e seus totais e subtotais possam ser gerados é necessário utilizar as funções de agregação: Max, Min, Sum, Avg, Count em nossa query.
Por outro lado, como diz o meu avô, nem tudo é 100% e sempre temos algumas incompatibilidades, desta vez, estas mesmas funções de agregação não podem ser utilizadas dentro dos conjuntos de agrupamento, ou seja, o Grouping Sets não consegui gerar valores com valores agregados por funções agregadas.
Para finalizarmos este artigo, vou apresentar através da Listagem 5, como podemos realizar o mesmo tipo de agrupamento e agregação de valores utilizando o comando Union All, posteriormente vamos comparar os resultados apresentados na Figura 3 e nossa última análise será feita com base nos planos de execução executados pela Listagem 5 e Listagem 6 conforme
apresenta a Figura 4.
— Listagem 5 – Simulando o Grouping Sets utilizando o comando Union All –
Group By Setor, CodFuncionario, YEAR(Data)
Select Null As Setor, Null As CodFuncionario, YEAR(Data) As 'Ano de Produção', SUM(Quantidade) As Soma
Group By YEAR(Data)
Figura 3 – Resultados após a execução da Listagem 4 e Listagem 5.
Com a Figura 3 fica fácil comparamos os resultados apresentados após executarmos as Listagens 4 e 5, a quantidade de linhas retornadas foram as mesmas, mas a principal diferença se relaciona aos totais apresentados na Listagem 5 nas últimas linhas de resultado.
Agora vamos analisar o plano de Execução gerado por cada transação, conforme apresenta a Figura 4, apresentada a seguir.
Figura 4 – Planos de Execução gerados após a execução das Listagens 4 e 5.
Ao observarmos a Figura 4, conseguimos rapidamente notar diversas diferenças entre os planos de execução gerados, compilados e executados para cada Listagem, principalmente na Listagem 5, listagem utilizada para simular os Grouping Sets utilizando o comando Union All. Temos a nítida percepção que a quantidade de passos definidos na composição do Plano de
Execução, definidos pelo Query Optimizer em seus processos de simplicação e definição do melhor caminho e posteriormente encaminhados e executados pelo Query Processor, muito bem maior que os passos realizados na Listagem 4.
Somente esta análise não poderá refletir em uma possível perda de performance ou aumento de processamento, pois se levarmos em considerados os operadores Clustered Index Scan e Sort, ambos executados em suas transações, podemos incluir que o mesmo custo de processamento foi executado em cada transação, indicador que nos mostra para esta análise que tanto o Grouping Sets como também o Union All, conseguiram executar processos distintos, com a mesma massa de dados praticamente no mesmo tempo, onde na Listagem 4 os operadores:
Clustered Index Scan – Consumiu 22% do tempo de processamento;
Sort – Consumiu 77% do tempo de processamento.
Na Listagem 5 os mesmos operadores, foram divididos conforme os selects realizados, apresentando os seguintes valores:
Clustered Index Scan – Consumiu 11% do tempo de processamento para cada Select;
Sort – Consumiu 39% do tempo de processamento para cada Select.
Foi justamente o operador Sort executado na Listagem 5 que apresentou uma diferença de 1% a mais de processamento em relação ao mesmo processo na Listagem 4, mesmo tento o seu processamento dividido em dois Selects. Com isso podemos dizer que o processo de ordenação dos dados utilizados pelo Grouping Sets foi ligeramente mais rápido com o Union.
Muito bem, após todas estas definições, análises e considerações sobre Grouping Sets, chegamos ao final deste artigo, no decorrer dele apresentei como podemos trabalhar com agregação e agrupamento de dados em múltiplos conjuntos, realizamos análises para identificar a forma de utilização do Grouping Sets, suas possíveis incompatibilidades, o caminho percorrido pelo
Plano de Execução ao executar uma query com Grouping Sets.
Ao final do artigo realizamos uma análise e comparação entre Grouping Sets e Union All, simulando a mesma forma de agregação de dados trabalhando com este dois recursos, o que nos demonstrou que ambos podem ser aplicados para o nosso cenário, cada qual, tratando os dados da sua forma, com uma pequena diferença de processamento e retorno dos dados.
Acredito que o objetivo principal deste artigo foi atingido, espero que você tenha gostado, que as informações apresentadas possam ser úteis no seu trabalho e estudados. Agradeço a sua visita, até o próximo artigo.
Publicado 18 18America/Sao_Paulo maio 18America/Sao_Paulo 2011 18 18America/Sao_Paulo maio 18America/Sao_Paulo 2011
Post anterior Resposta – Script Challenge – Número 9
Próximo post Introdução ao Data Collection no Microsoft SQL Server 2008 – Parte II.
4 comentários em "Realizando agrupamento de dados com Grouping Sets no SQL Server 2008 e R2."
Pingback: Realizando agrupamento de dados com Grouping Sets no SQL Server 2008 e R2. | DicasON
23 23America/Sao_Paulo janeiro 23America/Sao_Paulo 2013 às 3:04 PM
Dyesten Paulon Torres disse:
11 11America/Sao_Paulo agosto 11America/Sao_Paulo 2014 às 8:26 PM
Simplismente sensacional! Ficou muito claro sobre o uso e os ganhos que este recurso oferece! Obrigado!
11 11America/Sao_Paulo agosto 11America/Sao_Paulo 2014 às 9:32 PM
Dyesten,
Obrigado por sua visita e feedback, fico feliz em poder ajudar, espero que realmente tenha gostado do post.