Dica do Mês – Conhecendo a Álgebra Relacional e seus operadores


Olá, boa noite, tudo bem comunidade?

Como é bom poder curtir alguns dias de descanso que mais um feriadão, aproveitar para recarregar as baterias, renovar as ideias e se preparar para mais um mês que esta chegando, e se estamos chegando ao final de mais um mês é hora de compartilhar e postar mais um post dedicado a dica do mês. Falando nisso, a dica do mês de hoje será um pouco diferente das últimas postados aqui no meu blog.

Atendendo aos pedidos de alguns internautas que me enviaram e-mails nos últimos dias, como também, para alegrar os meus alunos, vou postar um pouco de conteúdo acadêmico, algo mais conceitual relacionado a teoria de banco de dados. Neste post vou falar um pouco sobre um dos assuntos mais importantes relacionadas a teoria geral de banco de dados, estou me referindo a Álgebra Relacional, ou como eu gosto de dizer a matemática do relacionamento de banco de dados.

Então lá nessa, espero que você goste e também aprenda um pouco sobre este conceito extremamente importante para qualquer profissional da área de banco de dados.


Introdução

A álgebra relacional é uma linguagem de consulta procedural. Ela consiste em um conjunto de operações que tornam uma ou duas tabelas como entradas e produzem uma nova tabela como resultado. Essas operações baseiam-se na teoria dos conjuntos (as tabelas correspondem a conjuntos). Linguagem procedural: linguagem que requer sempre a existência de definição quanto à ordem em que as operações serão realizadas.

 

Operadores

São definidas nove operações ou operadores para se trabalhar com álgebra relacional, eles podem ser classificados da seguinte maneira:

Fundamentais: Através dela qualquer expressão de consulta de dados é permitida:

¤ 1. Projeção

¤ 2. Seleção

¤ 3. Produto Cartesiano

¤ 4.União

¤ 5. Diferença, Subtração

Derivados: Derivam dos operadores fundamentais, são definidos para facilitar a especificação de certos procedimentos:

¤ 6. Intersecção

¤ 7. Junção (normal e natural)

¤ 8. Divisão

Especiais: Operadores que não se enquadram nos itens anteriores:

¤ 9.Renomeação e alteração

 

Quanto ao número de relações (tabelas) operandas:

¤ Unários – operam em uma única tabela .

São eles: seleção, projeção, renomeação e alteração;

¤ Binários – operam em duas tabelas.

São eles: união, intersecção, diferença, produto cartesiano, junção e divisão.

 

Quanto à origem da área da matemática:

¤ Teoria dos Conjuntos – operadores usuais da teoria de conjuntos da matemática. São eles: união, intersecção, diferença e produto cartesiano;

¤ Especiais – operadores adicionais, definidos pela álgebra relacional para manipulação de dados. São eles: seleção, projeção, junção, divisão, renomeação e alteração.

Além desses operadores, é definido também o operador de atribuição que permite atribuir o resultado de uma expressão de álgebra a uma tabela.

 

Símbolos que representam os operadores

Cada um dos noves operadores apresenta um símbolo que identifica sua função e operação dentro da expressão relacional, ou melhor dizendo dentro da fórmula que identifica e apresenta a operação relacional que esta sendo realizada, a Tabela 1 apresentada abaixo ilustra os símbolos representativos de cada operador:

Operadores

Tabela 1 – Relação de símbolos representativos de cada operador.

Vamos agora conhecer um pouco sobre cada operador, sua forma de utilização, após a apresentação de todos os operadores, disponibilizarei um código de exemplo para que você posso praticar e conhecer de maneira operacional como cada operador pode ser utilizado, vamos conhecer pelo primeiro operador conhecido como Projeção.

 

Projeção

Pode ser entendida como uma operação que filtra as colunas de uma tabela de nosso banco de dados ou uma tabela resultante de uma outra operação relacional executada. Por operar em apenas um conjunto de entrada, a projeção é classificada como uma operação unária.

Sintaxe:       coluna1, coluna2,…, colunaN (Tabela)

 

Seleção / Restrição

Pode ser entendida como a operação que filtra, seleciona as linhas de uma tabela, realizando também uma projeção, e opera em um conjunto de dados, sendo portando uma operação unária.

Sintaxe:         <condição de seleção ou predicado> (Tabela)

 

Produto Cartesiano

Utiliza a mesma notação de operação matemática de dois conjuntos, tendo como resultado do produto cartesiano de duas tabelas uma terceira tabela contendo as cominações possíveis entre os elementos das tabelas originais.

Essa tabela resultante possui um número de colunas que é igual à soma do número de colunas das tabelas iniciais e um número de linhas igual ao produto do número de linhas das duas tabelas.

Sintaxe: (Tabela 1) X (Tabela 2)

 

União

É uma operação binária, ou seja cria uma tabela a partir de duas outras tabelas união compatíveis levando as linhas comuns e não comuns a ambas. As informações duplicadas aparecerão somente uma vez no resultado. Tabelas União Compatíveis: tabelas cuja quantidade, disposição e domínio dos atributos/ campos sejam os mesmos.

Sintaxe: (Tabela 1)     (Tabela 2)

 

Intersecção

É uma operação binária, ou seja cria uma tabela a partir de duas outras tabelas levando sem repetição as linhas, que pertençam a ambas as tabelas presentes na operação.

Sintaxe: (Tabela 1)     (Tabela 2)

 

Diferença

Essa operação permite encontrarmos linhas que estão em uma tabela mas não estão em outra. A expressão Tabela 1 Tabela 2 resulta em uma tabela que contém todas as linhas que estão na tabela 1 e não estão na Tabela 2. Observamos que Tabela 1 – Tabela 2 (Exemplo1) é diferente de Tabela 2 – Tabela 1 (Exemplo2).

Sintaxe: (Tabela 1) – (Tabela 2)

 

Junção

Essa operação interage com o modelo relacional, ou seja trabalha com o modelo de relações entre tabelas realizando um produto cartesiano, combinando as linhas e somando as colunas de duas tabelas, só que partindo de campos comuns de ambas para realizar essa “seleção relacional. Essa operação possui uma condição onde se colocam os campos das tabelas que estão sendo usados para se efetivar a junção. Chamamos essa junção de junção com predicado.

(Sintaxe 1): (Junção com Predicado): (Tabela 1) |x| <condição de junção> (Tabela 2)

Outro tipo de junção é a junção natural:

Sintaxe : (Junção Natural): (Tabela 1) |x| (Tabela 2)

Nela não há especificação de condição sendo usado para isso todas as colunas comuns às duas tabelas. As colunas resultantes são a soma das colunas das duas tabelas sem a repetição das colunas idênticas (aparecerão uma vez somente). Não deve ser empregada quando se deseja associar duas tabelas apenas por um ou alguns dos seus atributos idênticos, caso isso seja feito os resultados são imprevisíveis.

 

Divisão

Essa operação produz como resultado a projeção de todos os elementos da primeira tabela que se relacionam com todos os elementos da segunda tabela. Essa operação também pode ser obtida através de outras operações de álgebra relacional.

Sintaxe: (Tabela 1) ÷ (Tabela 2)

 

Renomeação / Atribuição

Renomeação ( p ): É a operação que renomeia uma tabela.

Sintaxe: p Nome(Tabela)

Atribuição ( ß ): É utilizada para simplificar comandos muito extensos definindo então passos de comando.

Sintaxe: Variável ß Tabela


 

Colocando a mão na massa

Agora que já conhecemos um pouco sobre cada operador, suas sintaxes e formas de utilização, vamos colocar a mão na massão e utilizar nosso código de exemplo apresentado a seguir:

— Criando o Banco de Dados – AlgebraRelacional —

Create Database AlgebraRelacional

Go

 

— Acessando o Banco de Dados – AlgebraRelacional —

Use AlgebraRelacional

Go

 

— Criando a Tabela Cargos —

Create Table Cargos

(CodigoCargo Char(2) Primary Key Not Null,

DescricaoCargo Varchar(50) Not Null,

VlrSalario Numeric(6,2) Not Null)

Go

 

— Criando a Tabela Departamentos —

Create Table Departamentos

(CodigoDepartamento Char(2) Primary Key Not Null,

DescricaoDepartamento Varchar(30) Not Null,

RamalTel SmallInt Not Null)

Go

 

— Criando a Tabela Funcionarios —

Create Table Funcionarios

(NumeroRegistro Int Primary Key Not Null,

NomeFuncionario Varchar(80) Not Null,

DtAdmissao Date Default GetDate(),

Sexo Char(1) Not Null Default ‘M’,

CodigoCargo Char(2) Not Null,

CodigoDepartamento Char(2) Not Null)

Go

 

— Criando os relacionamentos —

Alter Table Funcionarios

Add Constraint [FK_Funcionarios_Cargos] Foreign Key (CodigoCargo)

References Cargos(CodigoCargo)

Go

 

Alter Table Funcionarios

Add Constraint [FK_Funcionarios_Departamentos] Foreign Key (CodigoDepartamento)

References Departamentos(CodigoDepartamento)

Go

 

— Inserindo os Dados —

Insert Into Cargos (CodigoCargo, DescricaoCargo, VlrSalario)

Values (‘C1’, ‘Aux.Vendas’, 1350.00),

(‘C2’, ‘Vigia’, 600.00),

(‘C3’, ‘Vendedor’, 1200.00),

(‘C4’, ‘Aux.Cobrança’, 1550.00),

(‘C5’, ‘Gerente’, 1200.00),

(‘C6’, ‘Diretor’, 2500.00),

(‘C7’, ‘Presidente’,5500.00)

Go

 

Insert Into Departamentos (CodigoDepartamento,DescricaoDepartamento,RamalTel)

Values (‘D1’, ‘Assist.Técnica’, 246),

(‘D2’, ‘Estoque’, 589),

(‘D3’, ‘Administração’, 772),

(‘D4’, ‘Segurança’, 810),

(‘D5’, ‘Vendas’, 512),

(‘D6’, ‘Cobrança’, 683)

Go

 

Insert Into Funcionarios (NumeroRegistro, NomeFuncionario, DtAdmissao, Sexo, CodigoCargo, CodigoDepartamento)

Values (1, ‘Cezar Sampaio’, ‘2013-08-10’, ‘M’, ‘C3’, ‘D5’),

(4, ‘Luis Alves Pereira’, ‘2014-03-02’, ‘M’, ‘C4’, ‘D6’),

(34, ‘Pedro Luiz Alves’, ‘2012-05-03’, ‘M’, ‘C5’, ‘D1’),

(21, ‘Silva Souza’, ‘2011-12-10’, ‘M’, ‘C3’, ‘D5’),

(95, ‘Eduardo Oliveira Silveira’, ‘2012-01-05’, ‘F’, ‘C1’, ‘D5’),

(39, ‘Ana Marta Silva’, ‘2013-01-12’, ‘F’, ‘C4’, ‘D6’),

(23, ‘Sergio Mota’, ‘2013-06-29’, ‘M’, ‘C7’, ‘D3’),

(48, ‘Larissa Roberto Aleluia’, ‘2012-06-01’, ‘F’, ‘C4’, ‘D6’),

(5, ‘Ricardo Martins’, ‘2013-10-15’, ‘M’, ‘C3’, ‘D5’),

(2, ‘Simone Gasparini Tune’, ‘2010-02-10’, ‘M’, ‘C2’, ‘D4’)

Go

 

— Exemplo – Operador – Projeção — Qual o nome e data de admissão dos funcionários? —

Select NomeFuncionario, DtAdmissao From Funcionarios

Go

 

— Exemplo – Operador – Seleção/Restrição — Quais os funcionários de sexo masculino? —

Select NumeroRegistro, NomeFuncionario, DtAdmissao,

Sexo, CodigoCargo, CodigoDepartamento

From Funcionarios

Where Sexo = ‘M’

Go

 

— Exemplo – Operadores – Projeção e Seleção — Quais os nomes e data de admissão dos funcionários de sexo masculino? —

Select NomeFuncionario, DtAdmissao From Funcionarios

Where Sexo = ‘M’

Go

 

— Exemplo – Operador – Produto Cartesiano – Trazer as informações dos funcionários e de seus cargos: Linhas de Funcionarios X Linhas de Cargos —

Select F.NumeroRegistro,

F.NomeFuncionario,

F.DtAdmissao,

F.Sexo,

F.CodigoCargo,

F.CodigoDepartamento

From Funcionarios F Cross Join Cargos C

Order By F.NumeroRegistro Desc

Go

 

— Exemplo – Operador – União — Combinação de Tabelas e Linhas entre selects distintos —

Select CodigoCargo, DescricaoCargo, VlrSalario

From Cargos

Where CodigoCargo In (‘C1’,‘C3’,‘C5’,‘C7’)

Union

Select CodigoCargo, DescricaoCargo, VlrSalario

From Cargos

Where CodigoCargo In (‘C2’,‘C4’,‘C6’)

Go

 

Select CodigoCargo, DescricaoCargo, VlrSalario

From Cargos

Where CodigoCargo In (‘C1’,‘C3’,‘C5’,‘C7’)

Union All

Select CodigoCargo, DescricaoCargo, VlrSalario

From Cargos

Where CodigoCargo In (‘C2’,‘C4’,‘C6’)

Union All

Select CodigoCargo, DescricaoCargo, VlrSalario

From Cargos

Where CodigoCargo In (‘C2’,‘C4’,‘C6’)

Go

 

— Exemplo – Operador – Intersecção — Combinação de Tabelas e Linhas entre selects distintos sem repetição de dados no resultado —

Select CodigoCargo, DescricaoCargo, VlrSalario

From Cargos

Where CodigoCargo In (‘C2’,‘C4’,‘C6’)

Intersect

Select CodigoCargo, DescricaoCargo, VlrSalario

From Cargos

Where CodigoCargo In (‘C2’,‘C3’,‘C6’,‘C7’)

Go

 

— Exemplo – Operador – Diferença – Linhas existentes em uma Tabela que não existem em outra —

Insert Into Cargos (CodigoCargo, DescricaoCargo, VlrSalario)

Values (‘C8’, ‘Aux.Vendas II’, 550.00)

Go

Select F.NumeroRegistro,

F.NomeFuncionario,

F.DtAdmissao,

F.Sexo,

F.CodigoCargo,

F.CodigoDepartamento

From Funcionarios F

Where Exists (Select CodigoCargo From Cargos)

Go

 

— Exemplo – Operador – Junção – Combinação de Linhas e Colunas entre tabelas que possuem algum tipo de vínculo relacional —

Select F.NumeroRegistro,

F.NomeFuncionario,

F.DtAdmissao,

F.Sexo,

F.CodigoCargo,

F.CodigoDepartamento

From Funcionarios F Inner Join Cargos C

On F.CodigoCargo = C.CodigoCargo

Order By F.NomeFuncionario, F.CodigoCargo Asc

Go

 

— Exemplo – Operador – Divisão – Relação Completa de todos os dados da Tabela da Esquerda com todos os dados da Tabela da Direita —

Create Table Projetos

(CodigoProjeto Char(8) Primary Key Not Null,

DescricaoProjeto Varchar(50) Not Null)

Go

 

Create Table Equipe

(Codigo Int Primary Key Identity(1,1) Not Null,

NumeroRegistroFuncionario Int Not Null,

CodigoProjeto Char(8) Not Null)

Go

 

— Criando os relacionamentos —

Alter Table Equipe

Add Constraint [FK_Equipe_Funcionarios] Foreign Key (NumeroRegistroFuncionario)

References Funcionarios(NumeroRegistro)

Go

 

Alter Table Equipe

Add Constraint [FK_Equipe_Projetos] Foreign Key (CodigoProjeto)

References Projetos(CodigoProjeto)

Go

 

— Inserindos os Dados —

Insert Into Projetos (CodigoProjeto, DescricaoProjeto)

Values (‘Projeto1’, ‘Suporte’),

(‘Projeto2’, ‘Manutenção’),

(‘Projeto3’, ‘Desenvolvimento’)

Go

 

Insert Into Equipe (NumeroRegistroFuncionario, CodigoProjeto)

Values (‘101’, ‘Projeto1’),

(‘104’, ‘Projeto1’),

(‘134’, ‘Projeto1’),

(‘101’, ‘Projeto2’),

(‘104’, ‘Projeto2’),

(‘101’, ‘Projeto3’)

Go

 

Select E.NumeroRegistroFuncionario,

E.CodigoProjeto,

P.DescricaoProjeto

From Equipe E Inner Join Projetos P

On E.CodigoProjeto = P.CodigoProjeto

Where E.CodigoProjeto = ‘Projeto1’

Go

 

— Exemplo – Operador – Renomeação —

sp_rename ‘Cargos’,‘NovosCargos’

Go

Select * from NovosCargos

 

— Exemplo – Operador – Atribuição —

Select F.NumeroRegistro As ‘Número de Registro’,

F.NomeFuncionario,

F.DtAdmissao As ‘Data de Admissão’,

F.Sexo,

F.CodigoCargo,

F.CodigoDepartamento,

Data=(Select GetDate()) — Atribuindo um valor para uma coluna

From Funcionarios F Inner Join NovosCargos C

On F.CodigoCargo = C.CodigoCargo

Order By F.NomeFuncionario, F.CodigoCargo Asc

Go

 

Legal, legal, você pode reparar que este código de exemplo é bastante simples e abrangente, demonstrando como podemos trabalhar com cada operador e de que forma o SQL Server se enquadra perfeitamente neste conceitos, algo muito comum para qualquer banco de dados ou sistema gerenciador de banco de dados, mas que pode variar a sua forma de análise e utilização.


Sendo assim chegamos ao final de mais uma dica do mês.

Espero que você tenha gostado, que as informações e exemplos publicados aqui possam de alguma maneira ajudar e colaborar com suas atividades diárias sejam elas: profissionais ou acadêmicas.

O conhecimento técnico é muito importante para qualquer profissional, mas o conceito é algo que sempre devemos valorizar e conhecer, a diferença entre um bom profissional e um profissional reconhecido e respeitado no mercado de trabalho é saber aliar o conhecimento teórico com o conhecimento prática, como muitos costumam dizer aliar a téoria a prática, sendo este o objetivo deste post.

Desejo um forte abraço, agradeço mais uma vez a sua visita.

Até mais.

Material de Apoio – Janeiro – 2016


Olá, boa tarde comunidade.

Estamos no começo de 2016 e pelo andar da carroagem não serão 366 dias fáceis, acredito que todos estão se perguntando até quando vamos continuar vivendo em um país tão injusto e desigual.

Mas vamos em frente, lutar para superar tudo isso!!!

Estou iniciando mais um ano com a mesma missão e objetivo dos anos anteriores, aprender, compartilhar e repassar o conhecimento para todos, algo que se devemos sempre fazer sem qualquer tipo de interesse, compartilhar nossas experiências, dificuldades, vivências e soluções.

Neste primeiro post relacionado a sessão “Material de Apoio”, gostaria de destacar que todos os scripts disponibilizados foram obtidos através do SQLServerCentral.com, na sessão Question of the Day, sendo este uma das minhas principais fontes de aperfeiçoamento em relação ao SQL Server,  onde reservo pelo menos uma hora do meu dia para responder e aprender com as questões postados pelos participantes deste portal.

Aproveito para deixar esta dica, procure reservar um pouco do seu tempo para se capacitar cada vez mais, mesmo que seja uma hora por dia, se você analisar isso no decorrer do ano será uma grande oportunidade para se aprimorar.

Muito bem, voltando a falar do post de hoje, você vai poder observar que os assuntos tratados por cada arquivo são bem distintos e podem ser aplicados em diversas situações. Dentre eles, existe um bem interesse que é a possibilidade de se criar uma árvove de natal através de um código Transact-SQL, muito legal isso.

Por questões de segurança e compatibilidade com a plataforma do WordPress.com, todos os arquivos foram renomeados, recebendo ao final do seu nome a extensão .doc, após o download basta remover esta extensão mantendo a extensão padrão .sql. Vale ressaltar que todos os scritps foram testados a partir da versão 2008 do Microsoft SQL Server, apresentando total compatibilidade com as versões: 2008, 2008 R2, 2012 e 2016.

A seguir a relação de arquivos, fique a vontade para compartilhar estes arquivos, como também, em adicionar sua crítica, dúvida ou sugestão.

Exemplo – Básico – Descobrindo e Alterando o Database Owner.sql

Exemplo – Básico – Utilizando comando Like na junção de tabelas.sql

Exemplo – Criando uma árvore de asterísticos no SQL Server.sql

Exemplo – Intermediário – Concatenação de string diretamente no select.sql

Exemplo – Intermediário – CTE Recursiva para geração de linhas + Option MaxRecursion.sql

Exemplo – Intermediário – Session Settings – Utilizando ANSI_Defaults forçando SQL Server trabalhar com SET IMPLICIT_TRANSACTIONS.sql

Exemplo – Intermediário – Trabalhando com a função Hashbytes para conversão de dados string para hexadecimal.sql

Exemplo – Intermediário – Utilizando Try..Catch + Transaction + Print para apresentar mensagem.sql

Como de costume, quero agradecer sua visita e interesse por este material, espero que os arquivos disponibilizados possam ser úteis e venham a ajudar em suas atividades.

Até a próxima.

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.


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.