Tag Archives: LAST_VALUE function

SQL Server 2012 – Funções Analíticas (LAG, LEAD, FIRST_VALUE, LAST_VALUE)

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.

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)

Resultado:

Result Post Funcao Analitica

Grande Abraço.

Links de referência:

LAG (Transact-SQL) – http://msdn.microsoft.com/pt-br/library/hh231256.aspx
LEAD (Transact-SQL) – http://msdn.microsoft.com/pt-br/library/hh213125.aspx
FIRST_VALUE (Transact-SQL) – http://msdn.microsoft.com/pt-br/library/hh213018.aspx
LAST_VALUE (Transact-SQL) – http://msdn.microsoft.com/pt-br/library/hh231517.aspx