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.

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

Os comentários estão fechados.