Realizando agrupamento de dados com Grouping Sets no SQL Server 2008 e R2.

Realizando agrupamento de dados com Grouping Sets no SQL Server 2008 e R2.

Anúncios

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 –

Set NoCount On;

If OBJECT_ID(‘dbo.OrdemProducao’,’U’) Is Not Null

Drop Table dbo.OrdemProducao

Go

Create Table OrdemProducao

(NumProducao Int Not Null,

Data Date Not Null,

CodFuncionario Int Not Null,

Setor Varchar(2) Not Null,

Quantidade Int Not Null

)

Go

Alter Table OrdemProducao

Add Constraint [PK_OrdemProducao_NumProducao] Primary Key Clustered (NumProducao)

Go

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)

Values

(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)

Insert Into OrdemProducao (NumProducao, Data, CodFuncionario, Setor, Quantidade)

Values

(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

Group By GROUPING Sets

(

(Setor, CodFuncionario, YEAR(DATA))  –Definindo o Agrupamento simples.

)

Go

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 –

Select Setor, CodFuncionario, YEAR(Data) As ‘Ano de Produção’, SUM(Quantidade) As Soma

from dbo.OrdemProducao

Group By GROUPING Sets

(

(Setor, CodFuncionario, YEAR(DATA)), — Primeiro Agrupamento

(YEAR(Data)) – Segundo Agrupamento

)
Go

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 –

Select Setor, CodFuncionario, YEAR(Data) As ‘Ano de Produção’, SUM(Quantidade) As Soma

from dbo.OrdemProducao

Group By Setor, CodFuncionario, YEAR(Data)

Union All

Select Null As Setor, Null As CodFuncionario, YEAR(Data) As ‘Ano de Produção’, SUM(Quantidade) As Soma

from dbo.OrdemProducao

Group By YEAR(Data)

Go;

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.

Valeu.

Autor: Junior Galvão - MVP

Profissional com vasta experiência na área de Tecnologia da Informação e soluções Microsoft. Graduado no Curso Superior em Gestão da Tecnologia de Sistemas de Informação. Pós-Graduado no Curso de Gestão e Engenharia de Processos para Desenvolvimento de Software com RUP na Faculdade FIAP - Faculdade de Informática e Administração Paulista de São Paulo. Pós-Graduado em Gestão da Tecnologia da Informação Faculdade - ESAMC Sorocaba. Formação MCDBA Microsoft, autor de artigos acadêmicos e profissionais postados em Revistas, Instituições de Ensino e WebSistes. Meu primeiro contato com tecnologia ocorreu em 1995 após meus pais comprarem nosso primeiro computador, ano em que as portas para este fantástico mundo se abriram. Neste mesmo ano, comecei o de Processamento de Dados, naquele momento a palavra TI não existia, na verdade a Tecnologia da Informação era conhecida como Computação ou Informática, foi assim que tudo começou e desde então não parei mais, continuando nesta longa estrada até hoje. Desde 2001 tenho atuado como Database Administrator - Administrador de Banco de Dados - SQL Server em tarefas de Administração, Gerenciamento, Migração de Servidores e Bancos de Dados, Estratégias de Backup/Restauração, Replicação, LogShipping, Implantação de ERPs que utilizam bancos SQL Server, Desenvolvimento de Funções, Stored Procedure, Triggers. Experiência na Coordenação de Projetos de Alta Disponibilidade de Dados, utilizando Database Mirroring, Replicação Transacional e Merge, Log Shipping. Atualmente trabalho como Administrador de Banco de Dados no FIT - Instituto de Tecnologia da Flextronics, como também, Consultor em Projetos de Tunnig e Performance para clientes, bem como, Professor Titular na Fatec São Roque. Desde 2008 exerço a função de Professor Universitário, para as disciplinas de Banco de Dados, Administração, Modelagem de Banco de Dados, Programação em Banco de Dados, Sistemas Operacionais, Análise e Projetos de Sistemas, entre outras. Possuo titulação Oficial Microsoft MVP - SQL Server renovada desde 2007.

4 comentários em “Realizando agrupamento de dados com Grouping Sets no SQL Server 2008 e R2.”

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s