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

Ajustando Usuários Órfãos – SQL Server

Vamos falar um pouco sobre um assunto que as vezes incomoda que são Usuários Órfãos ou como encontramos nas documentações da Microsoft, os Orphaned Users.

Abaixo segue um código para identificar os usuários da Database do contexto atual e para
qual Login do SQL Server este usuário está associado. Nos casos onde ServerLogin estiver como NULL temos um usuário Órfão.
No caso de Logins do Tipo SQL Server devemos utilizar os procedimentos descritos mais abaixo, no post, mas para caso de usuario do tipo Windows authentication basta criarmos o Login no Servidor.


SELECT usu.name as DBUser,lo.loginname as ServerLogin
FROM DB.dbo.sysusers usu
LEFT OUTER JOIN master.dbo.syslogins lo
ON usu.sid = lo.sid
WHERE usu.islogin = 1
AND usu.isaliased = 0
AND usu.hasdbaccess = 1

Geralmente encontramos este tipo de problema em migrações de Banco de dados como por exemplo, criação de uma base de desenvolvimento em outro servidor/instância.

O código abaixo mostra como identificar e associar um usuário órfão em uma database:



--identifica os usuarios órfãos
exec dbo.sp_change_users_login 'report'

--associa o usuário com seu respectivo login
exec sp_change_users_login 'Update_One','usr_Teste', 'usr_Teste'


Abaixo segue uma Stored Procedure que fiz para resolver este problema de maneira automática para facilitar a vida quando criamos, por exemplo, Jobs para atualização de ambientes de desenvolvimento.


createprocedure sp_ajusta_logins  @db varchar(30) = NULL
as
SET NOCOUNT ON
DECLARE @sql 	nvarchar(1000)
DECLARE @User	sysname
--Temp com os usuarios
CREATE TABLE #tbUsuarios (usuarios sysname,id BIGINT)
IF @db is null
begin
 print 'Por segurança, o parametro DataBase é obrigatório. Para executar para todos os DBs utilize sp_ajusta_logins ''TODOS'' ou sp_ajusta_logins ''nome_da_base'''
end else
IF not(upper(Rtrim(lTrim(@db))) = 'TODOS') -- Para um db específico
BEGIN
	PRINT 'Associando usuários órfãos para o DataBase '+@Db+' - '+convert(varchar,getdate())
	SET @sql = @db+'.dbo.sp_change_users_login ''report'''
	INSERT INTO #tbUsuarios exec sp_executesql @sql	
	
	IF exists(SELECT usuarios FROM #tbUsuarios)
	BEGIN
		SELECT @User = min(usuarios) from #tbUsuarios
		WHILE @User is not null
		BEGIN
			SELECT @sql = @db+'.dbo.sp_change_users_login ''Update_One'','''+ @User + ''','''+ @User +''''
			begin try
				EXEC sp_executesql @sql
				SET @sql = 'O usuário '''+ @User +''' do database '''+@db +''' foi associado ao seu login '''+@User+''''
				Print @sql
			end try
			begin catch
				SET @sql = N'Ocorreu um erro ao atualizar o usuário '''+ @User +''' do database '''+@db+''' - '+ERROR_MESSAGE()
				RAISERROR (@sql , 10, 1);
			end catch
			SELECT @User = min(usuarios) from #tbUsuarios where usuarios > @User			
		END
	END
END
ELSE
BEGIN
	print 'Executando SP para todos os DBS
	'
	-- Pesquisa em todos os dbs
	SELECT @db = min(name) from master.dbo.sysdatabases where name not in ('tempdb', 'pubs', 'msdb', 'NorthWind', 'master','model')
	PRINT 'Associando usuários órfãos para o DataBase '+@Db+' - '+convert(varchar,getdate())
	WHILE @db is not null
	BEGIN
		SET @sql = @db+'.dbo.sp_change_users_login ''report'''

		INSERT INTO	#tbUsuarios exec sp_executesql @sql
		IF exists(SELECT usuarios FROM #tbUsuarios)
		BEGIN
			SELECT @User = min(usuarios) from #tbUsuarios
			WHILE @User is not null
			BEGIN
				SET @sql = @db+'..sp_change_users_login ''Update_One'','''+ @User + ''','''+ @User +''''
				begin try
					EXEC sp_executesql @sql
					SET @sql = 'O usuário '''+ @User +''' do database '''+@db +''' foi associado ao seu login '''+@User+''''
					Print @sql
				end try
				begin catch
					SET @sql = N'Ocorreu um erro ao atualizar o usuário '''+ @User +''' do database '''+@db+''' - '+ERROR_MESSAGE()
					RAISERROR (@sql , 10, 1);
				end catch				
				SELECT @User = min(usuarios) from #tbUsuarios where usuarios > @User				
			END
		END
		DELETE #tbUsuarios
		SELECT @db = min(name) FROM master.dbo.sysdatabases WHERE name not in ('tempdb', 'pubs', 'msdb', 'NorthWind', 'master','model')
		AND Name > @db
	END
END

DROP TABLE #tbUsuarios
print 'Processo Concluído!'

Abraço!