Novidades – Microsoft SQL Server 2012 – Novas Windows Functions

Pessoal, boa tarde.

Estou de volta, com mais algumas novidades do Microsoft SQL Server 2012, hoje mais especificamente sobre as Novas Windows Functions: Lag e Lead.

Nesta última versão do time de desenvolvimento do SQL Server, introduziu no Microsoft SQL Server 2012, as funções Lag e Lead, ambas com a finalidade de análise análista de dados, o que normalmente não é realizado de forma simples para um Banco de Dados.

Até o Microsoft SQL Server 2005 e 2008, tinhamos a nossa disposição algumas Windows Functions, como por exemplo:

  • Row_Number;
  • Rank;
  • Dense_Rank; e
  • NTILE.

Sendo que estas 4 Windows Functions, são normalmente utilizadas em processo de Ranking de dados, possibilitando por exemplo definir um Ranqueamento de Valores, Contagem de Linhas ou Número de Linhas.

Além disso estas funções fazem uso da cláusula OVER, para realizar o agrupamento dos dados para possibilitar o retorno dos valores. Desta mesma forma, as funções Lead e Lag, também fazem uso da claúsula Over em sua sintaxe de comando, mas um diferencial, pois agora a cláusula Over possui suporte completo a funções de agregação com ORDER BY.

Depois desta pequena introdução, vamos conhecer um pouco mais sobre estas funções, começando pela função Lag:

Conhecendo a Windows Function Lag:

Por padrão, esta função acessa os dados de uma linha anterior no mesmo conjunto de resultados sem o uso de uma autojunção no SQL Server 2012. A Lag fornece acesso a uma linha a um determinado deslocamento físico que antecede a linha atual.

Use essa função analítica em uma instrução SELECT para comparar valores na linha atual com valores em uma linha anterior. Caso o conjunto de valores analisados pelo Lag, apresente algum valor solicitado inexistente, o SQL Server irá retornar Null como valor.

Windows Function Lag – Sintaxe:

LAG (scalar_expression [,offset] [,default])
OVER ( [ partition_by_clause ] order_by_clause )

Argumentos:

scalar_expression:O valor a ser retornado com base no deslocamento especificado.É uma expressão de qualquer tipo que retorna um único valor (escalar).scalar_expression não pode ser uma função analítica.

Offset:O número de linhas atrás da linha atual da qual obter um valor.Se não for especificado, o padrão será 1.offset pode ser uma coluna, subconsulta ou outra expressão que avalia para um inteiro positivo ou pode ser convertida implicitamente em bigint.offset não pode ser um valor negativo ou uma função analítica.

Default:O valor a ser retornado quando scalar_expression a offset é NULL.Se um valor padrão não for especificado, NULL será retornado.default pode ser uma coluna, subconsulta ou outra expressão, mas não pode ser uma função analítica.default deve ser de tipo compatível com scalar_expression.

OVER ( [ partition_by_clause ] order_by_clause):partition_by_clause divide o conjunto de resultados produzido pela cláusula FROM em partições nas quais a função é aplicada.Se não for especificado, a função tratará todas as linhas do conjunto de resultados da consulta como um único grupo. O order_by_clause determina a ordem dos dados antes de a função ser aplicada.Se partition_by_clause é especificado, determina a ordem dos dados na partição. A order_by_clause é obrigatória.

Muito bem, já conhecemos um pouco da Windows Function Lag, agora com base na Tabela 1 apresentada abaixo, foi desenvolvido o código exemplo utilizado para ilustrar o uso desta função.

Data INCC Lag Lead
2011-04-01 0,75 NULL 2,03
2011-05-01 2,03 0,75 1,43
2011-06-01 1,43 2,03 0,59
2011-07-01 0,59 1,43 0,16
2011-08-01 0,16 0,59 NULL

Tabela 1 – Relação de valores para demonstração de exemplos para funções Lag e Lead.

— Declarando a variável do tipo Table —

DECLARE @Valores TABLE  (Data DATE,  Valor DECIMAL(4,2))

 — Inserindo valores na variável —

INSERT INTO @Valores

VALUES (‘2012-04-01’,0.55),

(‘2012-05-01’,4.07),

(‘2012-06-01’,10.22),

(‘2012-07-01’,2.59),

(‘2012-08-01’,5.29)

— Executando o comando Select fazendo uso da Função Lag —

SELECT Data, Valor, LAG(Valor) OVER (ORDER BY Data) As ‘Posição Inicial’,

LAG(Valor,2) OVER (ORDER BY Data) As ‘Posição Intermediária’

FROM @Valores

—————————————————————————————————————————————————————————-

Conhecendo a Windows Funciton Lead:

A função Lead, acessa os dados de uma linha subsequente no mesmo conjunto de resultados sem o uso de uma autojunção no SQL Server 2012. A Lead fornece acesso a uma linha a um determinado deslocamento físico que segue a linha atual.

Use essa função analítica em uma instrução SELECT para comparar valores na linha atual com valores em uma linha seguinte. Da mesma forma que a função Lag, a função Lead, quando não encontra o dado solicitado retorna o valor Null, como valor não encontrado ou não especificado.

Windows Function Lead – Sintaxe:

LEAD ( scalar_expression [ ,offset ] , [ default ] )
OVER ( [ partition_by_clause ] )

Argumentos:

scalar_expression:O valor a ser retornado com base no deslocamento especificado.É uma expressão de qualquer tipo que retorna um único valor (escalar).scalar_expression não pode ser uma função analítica.

Offset:O número de linhas atrás da linha atual da qual obter um valor.Se não for especificado, o padrão será 1.offset pode ser uma coluna, subconsulta ou outra expressão que avalia para um inteiro positivo ou pode ser convertida implicitamente em bigint. O offset não pode ser um valor negativo ou uma função analítica.

Default:O valor a ser retornado quando scalar_expression a offset é NULL.Se um valor padrão não for especificado, NULL será retornado.default pode ser uma coluna, subconsulta ou outra expressão, mas não pode ser uma função analítica.default deve ser de tipo compatível com scalar_expression.

OVER ( [ partition_by_clause ] order_by_clause):partition_by_clause divide o conjunto de resultados produzido pela cláusula FROM em partições nas quais a função é aplicada.Se não for especificado, a função tratará todas as linhas do conjunto de resultados da consulta como um único grupo. O order_by_clause determina a ordem dos dados antes de a função ser aplicada.Se partition_by_clause é especificado, determina a ordem dos dados na partição. A order_by_clause é obrigatória.

Tendo como base a Tabela 1, utilizada para função Lag, foi desenvolvido o código de exemplo para a função Lead, conforme apresentado abaixo:

— Declarando a variável do tipo Table —

DECLARE @Valores TABLE  (Data DATE,  Valor DECIMAL(4,2))

— Inserindo valores na variável —

INSERT INTO @Valores

VALUES (‘2012-04-01’,0.55),

(‘2012-05-01’,4.07),

(‘2012-06-01’,10.22),

(‘2012-07-01’,2.59),

(‘2012-08-01’,5.29)

— Executando o comando Select fazendo uso da Função Lag —

 

SELECT Data, Valor,
LEAD(Valor) OVER (ORDER BY Data) As ‘Posição Posterior’,
LEAD(Valor,3) OVER (ORDER BY Data) As ‘Posição Final’
FROM @Valores

Com os códigos de exemplos apresentados acima, podemos observar que o uso destas funções é muito fácil e simples, no último código de exemplo, vou fazer uso das duas funções no mesmo select, conforme apresento a seguir:

— Utilizando as Windows Function Lag e Lead —

SELECT Data, Valor,

LAG(Valor) OVER (ORDER BY Data) As ‘Posição Inicial’,

LEAD(Valor) OVER (ORDER BY Data) As ‘Posição Posterior’,

LAG(Valor,2) OVER (ORDER BY Data) As ‘Posição Intermediária’,

LEAD(Valor,3) OVER (ORDER BY Data) As ‘Posição Final’

FROM @Valores

Com este último código de exemplo vou encerrar este post, espero que esta dica possa ser útil e demonstre o quando o Microsoft SQL Server 2012 pode ser revolucionário.

Um grande abraço.

Até a próxima.

Anúncios

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.

3 comentários em “Novidades – Microsoft SQL Server 2012 – Novas Windows Functions”

  1. BOm dia Amigo, li seus posts, porem ainda estou com uma dúvida.. Ql a diferença entre Windows Server 2012 e Micrshoft SQL Server 2012? É a mesma coisa?? se puder me responder desde já agradeço..

    Curtir

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