Neste post vou falar de algumas funções Analíticas que foram inseridas no SQL Server 2012 que são elas LAG, LEAD, FIRST_VALUE e LAST_VALUE. Estas funções vieram para resolver problemas que tínhamos anteriormente para exibir, por exemplo, o Valor de uma coluna na linha anterior na posição atual do conjunto de registros em uma unica consulta. Antes, para conseguir o valor da linha anterior, tínhamos que usar operações JOIN.
Abaixo segue um resumo sobre cada função abordada neste post e alguns exemplos de utilização.
A função LAG retorna o valor da linha anterior à atual de acordo com a quantidade de linhas anteriores escolhida.
Abaixo segue um resumo sobre cada função abordada neste post e alguns exemplos de utilização.
A função LAG retorna o valor da linha anterior à atual de acordo com a quantidade de linhas anteriores escolhida.
LAG (scalar_expression [,offset] [,default])
OVER ( [ partition_by_clause ] order_by_clause )
A função LEAD retorna o valor da linha posterior à linha atual de acordo com a quantidade de linhas anteriores escolhida.
LEAD (scalar_expression [,offset] [,default])
OVER ( [ partition_by_clause ] order_by_clause )
A função FIRST_VALUE retorna o valor de uma coluna da primeira linha do conjunto de registros.
FIRST_VALUE ( [scalar_expression ] )
OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )
A função LAST_VALUE retorna o valor de uma coluna da Última linha do conjunto de registros.
LAST_VALUE ( [scalar_expression )
OVER ( [ partition_by_clause ] order_by_clause rows_range_clause )
scalar_expression
Coluna que vai retornar os valores
offset
Número de Linhas antes da linha atual(LAG) ou após a linha atual(no caso do LEAD)
default
Valor a ser retornado se retorno for NULL.Não é obrigatória a especificação.
OVER ( [ partition_by_clause ] order_by_clause)
Cláusula OVER (Transact-SQL).
Atenção para com o OVER.
Exemplos de utilização das funções acima:
create table AtualizacaoProduto
(
idAtualizacao int identity(1,1) not null,
descProd varchar(50) not null,
descPeriodo varchar(7) not null,
vlrProd decimal(18, 2) not null
)
GO
insert into AtualizacaoProduto
values('Tomate','06/2012',2.5),
('Tomate','07/2012',2.6),
('Tomate','08/2012',2.9),
('Tomate','09/2012',2.9),
('Tomate','10/2012',3),
('Tomate','11/2012',3),
('Tomate','12/2012',3.5),
('Tomate','01/2013',4),
('Tomate','02/2013',5),
('Tomate','03/2013',7),
('Tomate','04/2013',8),
('Tomate','05/2013',9),
('laranja','01/2013',2),
('laranja','02/2013',2.5),
('laranja','03/2013',2.6),
('laranja','04/2013',2.75)
Select *
,lag(vlrProd,1) over(partition by descProd order by convert(date, '01/'+descPeriodo)) as "Valor Anterior" --Valor da última atualização
,LEAD(vlrProd,1) over(partition by descProd order by convert(date, '01/'+descPeriodo)) as "Valor Futuro" --Valor da Próxima atualização
,FIRST_VALUE(vlrProd) over(partition by descProd order by convert(date, '01/'+descPeriodo) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as "Primeiro Valor"
,LAST_VALUE(vlrProd) over(partition by descProd order by convert(date, '01/'+descPeriodo) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as "Último Valor"
from AtualizacaoProduto
order by descProd,convert(date, '01/'+descPeriodo)