Olá pessoal, espero que estejam todos bem! Espero logo encontrar vocês por ai nos eventos da comunidade técnica, alias que saudade de aglomerar nos eventos de SQL e conversar com pessoas que valem a pena ouvir, ouvir e ouvir. Pessoal, hoje gostaria de compartilhar com vocês duas funções muito interessantes que foram introduzidas a partir do SQL 2012, essas funções permitiram que os desenvolvedores T-SQL escrevessem menos códigos e tivessem resultados de maneira mais performáticas, estamos falando da função lead e lag do SQL. Bom, irei explicar de forma mais detalhada e resumida cada função.
Resumidamente essas funções são para verificar a linha anterior e a linha posterior da linha analisada, KEEP CALM, irei explicar em detalhes, primeiro irei falar sobre a função lead.
Função lag:
Sintaxe lead e lag:
LEAD/LAG ( scalar_expression [ ,offset ] , [ default ] )
OVER ( [ partition_by_clause ] order_by_clause )
scalar_expression: Valor que será base para realizar o deslize para frente ou para trás.
offset: Número de linhas ou posições que deseja andar para frente ou para trás, esse valor pode ser passado diretamente no parâmetro ou utilizando resultados de uma coluna que retorna um número inteiro, o valor tem que ser positivo e o default é 1.
A função lag serve para voltar linhas atrás da linha de referência
Reparem nessa query:
;with cte_val as
(
select * from (values
(1,'linha 1')
,(2,'linha 2')
,(3,'linha 3')
,(4,'linha 4')
,(5,'linha 5')
,(6,'linha 6')
,(7,'linha 7')
,(8,'linha 8')
,(9,'linha 9')
,(10,'linha 10')
) as cols(id,linha_atual)
)
select
*,
linha_anterior = LAG(linha_atual,1,':(') OVER ( ORDER BY id ASC)
from cte_val
order by id asc
Opa, nessa query vocês devem estar se perguntando ''o que é essa CTE com esse "values?", isso se chama "rows construct values" um recurso muito TOP do SQL que quase nunca vejo sendo utilizado, mas com ele podemos montar "tabelas" auxiliares em tempo de execução, assunto para outro artigo. Nesse caso só criei assim para não criar tabelas no banco e para mostrar para vocês uma funcionalidade, dica bônus :)
Reparem no resultado dessa query:
Nessa query eu tenho uma coluna chamada linha atual e uma coluna criada no select chamada linha anterior, essa "linha_anterior" é resultado da função lag, onde eu consigo pegar os dados de uma linha anterior levando como referencial a linha atual, consigo andar das linhas para traz? Consigo, basta eu alterar o parâmetro offset para 2 ou quantas casas eu quiser.
Agora vendo a função lead:
A função lead é muito parecida com a função lag, mas a diferença é que ela anda para frente, vejamos um exemplo:
Vimos que o exemplo acima segue a mesma lógica da função lag, pensando assim, um dia me perguntei, porque só não temos a função lag e quando quero andar para trás coloco um sinal negativo?
Será que isso funciona? Vamos testar
Logo de cara recebemos um erro, lembra que falei acima que o parâmetro offset só aceita positivo? Mas por quê? Padrão ANSI, o SQL precisa seguir um padrão, esse é o motivo de não aceitar negativo.
Montei um exemplo simples, onde podemos utilizar o lead e lag com partition by.
No exemplo acima, particionei a tabela por nome, onde o "Leandro" e o "Marcio" correspondem a uma janela cada um, eu explico como isso funciona post 1 da série de window Functions, caso tenha dúvidas, volte a confira.
Abaixo a query do lab:
---sql2016 servick pack 1
DROP TABLE IF EXISTS historico_salario,funcionario
create table funcionario
(
Matricula int identity
,nome varchar(150)
,constraint pk_funcionario_matricula primary key (Matricula)
)
create table historico_salario
(
matricula int
,salario numeric(9,2)
,historico_sal_id int identity
,constraint FK_HISTORICO_SALARIO foreign key (matricula) REFERENCES FUNCIONARIO (matricula)
)
INSERT INTO funcionario VALUES ('lEANDRO')
,('MARCIO')
,('REGINALDO')
INSERT INTO historico_salario VALUES (1,'1000')
,(1,'5000')
,(1,'2000')
,(1,'2500')
INSERT INTO historico_salario VALUES (2,'100')
,(2,'5000')
,(2,'1000')
,(2,'2500')
SELECT
B.nome
,salario AS SALARIO_ATUAL
,SALARIO_ANTERIOR = LAG(salario,1,'0') OVER (partition by b.nome ORDER BY historico_sal_id ASC)
FROM historico_salario A
INNER JOIN funcionario B
ON A.matricula = B.MATRICULA
Por enquanto é isso pessoal, espero que tenham gostado e até a próxima, irei lançar um desafio de T-SQL. Abraços :)
Comentários
Postar um comentário