Funções LAG e LEAD em SQL

LAG() e LEAD() são funções posicionais. Uma função posicional é um tipo de função de janela. Se você não está familiarizado com quando e como usá-las, qual é a sintaxe de cada função, por que você pode precisar usá-las e quais são as diferenças, continue lendo!

LAG() e LEAD() são funções posicionais. Estas são funções de janela e são muito úteis na criação de relatórios, pois podem se referir a dados de linhas acima ou abaixo da linha atual. Neste artigo, analisaremos em detalhe as duas funções.

Sintaxe da Função LAG

A função LAG() permite acessar um valor armazenado em uma linha acima da linha atual. A linha acima pode ser adjacente ou algum número de linhas acima, conforme ordenado por uma coluna ou conjunto de colunas especificado.

Vamos ver sua sintaxe:

LAG(expressão [,offset[,valor_padrão]]) OVER(ORDER BY colunas)

LAG() leva três argumentos: o nome da coluna ou uma expressão da qual se obtém o valor, o número de linhas a pular (offset) acima e o valor padrão a ser exibido se o valor armazenado obtido da linha acima estiver vazio. Apenas o primeiro argumento é necessário. O terceiro argumento (valor padrão) só é permitido se você especificar o segundo argumento, offset.

Como em outras funções de janela, LAG() requer a cláusula OVER. Ela pode incluir parâmetros opcionais, que explicaremos mais adiante. Com LAG(), você deve especificar um ORDER BY na cláusula OVER, com uma coluna ou uma lista de colunas pelas quais as linhas devem ser ordenadas.

Vamos considerar a seguinte tabela, venda:

E a seguinte consulta com uma função LAG():

SELECT nome_vendedor, valor_venda,
  LAG(valor_venda) OVER(ORDER BY valor_venda) as valor_venda_anterior
FROM venda;

Aqui está o resultado:

Este uso mais simples de LAG() mostra o valor da linha adjacente acima. Por exemplo, o segundo registro exibe o valor da venda de Alice ($12.000) com o de Estefano ($7.000) da linha acima, nas colunas valor_venda e valor_venda_anterior, respectivamente. Note que a primeira linha não tem uma linha adjacente acima, e consequentemente o campo valor_venda_anterior está vazio (NULL), já que a linha da qual o valor de valor_venda deve ser obtido não existe.

Se você especificar apenas o argumento requerido (o nome da coluna ou outra expressão), como nesse exemplo, o argumento offset tem como padrão 1 e o terceiro argumento tem como padrão NULL. Em nosso exemplo, a primeira linha do conjunto de resultados tem NULL em valor_venda_anterior e nas outras linhas estão os valores das respectivas linhas imediatamente acima, porque o offset é igual a 1.

Usando LAG(), você pode ver o valor para a linha atual, bem como o valor da linha adjacente acima. Você pode usar isto, por exemplo, para verificar o valor da venda de uma determinada linha em relação à linha anterior com o valor de venda classificado da linha mais baixa para a mais alta.

A ilustração abaixo mostra como o valor da linha adjacente acima é anexado à linha atual.

A função LAG() está incluída em nosso "Resumo sobre Funções de Janela (Window Functions) em SQL", com conteúdos práticos.

Sintaxe da Função LEAD

LEAD() é semelhante a LAG(). Enquanto LAG() acessa um valor armazenado em uma linha acima, LEAD() acessa um valor armazenado em uma linha abaixo.

A sintaxe de LEAD() é semelhante à de LAG():

LEAD(expressão [,offset[,valor_padrão]]) OVER(ORDER BY colunas)

Assim como LAG(), a função LEAD() leva três argumentos: o nome de uma coluna ou expressão, o offset a ser pulado abaixo e o valor padrão a ser retornado se o valor armazenado obtido na linha abaixo estiver vazio. Apenas o primeiro argumento é necessário. O terceiro argumento, o valor padrão, só pode ser especificado se você especificar o segundo argumento, offset.

Assim como LAG(), LEAD() é uma função de janela e requer uma cláusula OVER. E, assim como LAG(), LEAD() deve ser acompanhada de um ORDER BY na cláusula OVER.

Vamos voltar para a tabela venda:

Aqui está uma consulta com uma função LEAD():

SELECT nome_vendedor, valor_venda,
  LEAD(valor_venda) OVER(ORDER BY valor_venda) as proximo_valor_venda
FROM venda;

Aqui está o resultado:

As linhas são ordenadas pela coluna especificada em ORDER BY (valor_venda). A função LEAD() obtém o valor da venda a partir da linha abaixo. Por exemplo, o valor da venda de Estefano é de $7.000 na coluna valor_venda e a coluna proximo_valor_venda no mesmo registro contém $12.000. Este último vem da coluna valor_venda para Alice, a vendedora na linha seguinte. Note que a última linha não tem uma linha após ela, portanto o campo proximo_valor_venda está vazio (NULL) para a última linha.

Se você especificar apenas o argumento requerido, ou seja, apenas o nome da coluna ou outra expressão, o offset fica definido como 1 e o terceiro argumento fica definido como NULL. Em nosso exemplo, o valor para o proximo_valor_venda de Alice vem da coluna valor_venda da linha adjacente abaixo, uma vez que o offset padrão é 1.

Usando LEAD(), você pode comparar valores entre linhas. A ilustração a seguir mostra como o valor retornado por LEAD() é anexado à linha atual.

Usando LAG() e LEAD() Para Comparar Valores

Um uso importante para LAG() e LEAD() em relatórios é comparar os valores na linha atual com os valores na mesma coluna, mas em uma linha acima ou abaixo.

Considere a seguinte tabela, venda_anual, mostrada abaixo:

Como você pode ver, esta tabela contém o valor total de vendas por ano. Usando LAG() e LEAD(), podemos comparar os valores das vendas anuais ao longo dos anos.

Vamos conferir esta consulta:

SELECT  ano, venda_total_atual,
   LAG(venda_total) OVER(ORDER BY ano) AS venda_total_anterior,
   venda_total - LAG(venda_total) OVER(ORDER BY ano) AS diferença
FROM venda_anual;

Aqui está o conjunto de resultados:

Esta consulta pega o valor da venda do ano anterior e o coloca na coluna venda_total_anterior usando a função LAG(). O ORDER BY na cláusula OVER ordena os registros por ano, garantindo que a linha adjacente acima represente o ano anterior. Em seguida, pega o valor da coluna venda_total_atual na linha anterior e o traz para a linha atual.

Esta consulta também calcula a diferença no valor da venda entre o ano atual e o ano anterior. Isto pode nos ajudar a entender se houve um aumento (diferença positiva) ou uma diminuição (diferença negativa) nas vendas de um ano para o outro.

Para 2015, não temos informações sobre o ano anterior. Portanto, o valor retornado pela função LAG() é NULL e o mesmo acontece com a diferença. Agora, a venda total em 2018 foi de $32.000, mas foi de $34.000 em 2017 (o ano anterior), como mostrado na coluna venda_total_anterior. A diferença é de -$2.000, indicando que em 2018 houve uma diminuição de $2.000 nas vendas em relação a 2017.

Usando LAG() e LEAD() com um Offset Específico

Você pode usar as funções LAG() e LEAD() com dois argumentos: o nome da coluna e o offset.

Considere a tabela a seguir, funcionário:

A consulta abaixo seleciona o bônus para o funcionário com ID=1 para cada trimestre de cada ano. Em seguida, identifica os bônus para o trimestre correspondente no ano anterior e no ano seguinte.

SELECT ano, trimestre,
  LAG(bônus,4) OVER(ORDER BY ano,trimestre) AS bônus_anterior,
 bônus AS bônus_atual,
  LEAD(bônus,4) OVER(ORDER BY ano,trimestre) AS próximo_bônus 
FROM funcionário
WHERE identificação_do_funcionário=1;

Esta consulta retorna o seguinte conjunto de resultados:

As linhas destacadas em verde são os registros para o primeiro trimestre de cada ano, as linhas em branco para o segundo trimestre de cada ano, etc. Em qualquer linha, os valores dos bônus anterior e seguinte são retirados do trimestre correspondente do ano anterior e do ano seguinte, e são atribuídos às colunas bônus_anterior e próximo_bônus, respectivamente.

Por exemplo, o funcionário de ID=1 recebeu um bônus de $80 no primeiro trimestre de 2018. Para o mesmo funcionário, o bônus do primeiro trimestre de 2017 foi de $100, e o bônus do primeiro trimestre de 2019 foi de $0. O ORDER BY especifica que as linhas devem ser ordenadas por ano e trimestre. Um offset de 4 indica que as funções LEAD() e LAG() devem pular 4 linhas antes e depois da linha atual, respectivamente. Com este offset, você pode comparar valores do mesmo trimestre de anos diferentes, pois há 4 trimestres em um ano. A figura abaixo ilustra esta ideia.

Você pode aprender mais sobre as funções LAG() e LEAD() em nossos artigos "Funções de Janela (Window Functions) em SQL: Funções Posicionais " de Aldo Zelen e "Quando Usar Funções de Janela (Window Functions) em SQL? " de Tihomir Babic.

Usando LAG() e LEAD() com um Valor Padrão

Na seção anterior, falamos como utilizar o argumento offset nas funções LAG() e LEAD(). Agora vamos considerar casos com um terceiro argumento: o valor padrão a ser atribuído quando o valor obtido for NULL. Para especificar este argumento, é preciso especificar também o segundo argumento, offset. O offset padrão é 1, portanto especifique 1 para manter o offset padrão ou algum outro valor apropriado para o seu caso.

Vejamos outro exemplo. A seguinte tabela, venda_produto, contém a identificação do produto, o mês (1 = janeiro, 2 = fevereiro, etc.) e a quantidade de vendas por mês.

Aqui estão os registros para os quais o ID do produto é 1.

A consulta:

SELECT identificacao_produto, mês,
  LAG(quantidade,1,0) OVER(ORDER BY mês) AS quantidade_anterior,
  quantidade AS quantidade_atual,
  quantidade - LAG(quantidade,1,0) OVER(ORDER BY mês) AS diferença
FROM venda_produto
WHERE identificacao_produto=1;

retorna o resultado:

Para o produto com ID=1, selecionamos o mês de venda, a quantidade de vendas deste mês (quantidade_atual) e a quantidade de vendas do mês anterior (o valor da linha anterior retornado por LAG()).

Gostaríamos de mostrar zero ao invés de NULL quando LAG() tenta obter valores de linhas além daqueles que existem em nosso conjunto de dados. Tanto para LAG() quanto para LEAD(), isto é feito especificando um terceiro argumento, o valor padrão. Lembre-se que o argumento do offset é necessário para especificar o argumento do valor padrão; aqui, especificamos um offset de 1 para ver a linha acima. Especificamos então 0 como o terceiro argumento. Isto define como zero qualquer tentativa de obter valores de linhas que não existem, como é o caso aqui para a primeira linha (não há linha acima da primeira linha).

Note que o valor padrão de zero é atribuído somente para linhas que não existem; as linhas cujas linhas adjacentes acima existem, mas com NULLs em quantidade_atual, são deixadas como NULLs em vez de serem transformadas em 0. Você pode ver isto na linha onde mês=4: embora a quantidade_atual para a linha acima (mes=3) seja NULL, ela não é substituída por um zero, uma vez que a linha anterior existe, contendo um NULL em quantidade_atual.

Usando LAG() e LEAD() com Partições

Vamos continuar com o mesmo exemplo, mas agora examinamos um caso no qual precisamos usar PARTITION BY na cláusula OVER. Abaixo está a próxima parte da tabela venda_produto com outro produto cujo ID=2.

A consulta:

SELECT identificacao_produto, mês,
  LAG(quantidade,1,0) OVER(PARTITION BY identificacao_produto ORDER BY mês) AS quantidade_anterior,
  quantidade AS quantidade_atual,
  quantidade - LAG(contar,1,0) OVER(PARTITION BY identificacao_produto ORDER BY mês) AS diferença
FROM venda_produto;

retorna o resultado:

Temos vários produtos nesta tabela. Para calcular as diferenças entre as vendas atuais e as vendas anteriores separadamente para cada produto, especificamos PARTITION BY antes de ORDER BY na cláusula OVER.

Você pode nomear uma coluna ou uma lista de colunas em PARTITION BY. Aqui, usamos a coluna identificacao_produto para dividir os registros em partições e depois ordenar por mês dentro de cada partição. Como resultado, cada partição começa com o Mês 1 e termina com o Mês 4.

PARTITION BY é semelhante a GROUP BY por agrupar valores iguais. Ao contrário de GROUP BY, no entanto, PARTITION BY não agrupa as linhas originais em uma única linha; as linhas originais permanecem acessíveis. Você pode ler mais sobre a diferença entre PARTITION BY e ORDER BY no artigo "Qual é a diferença entre GROUP BY e PARTITION BY em SQL?".

Funções LAG e LEAD são Muito Úteis!

Funções posicionais como LAG() e LEAD() são úteis em muitas situações. Elas são frequentemente usadas na criação de relatórios, pois podem se referir a linhas acima ou abaixo, como vimos nos exemplos. Espero que este artigo ajude a ampliar seus conhecimentos sobre funções de janela em SQL. Saiba mais sobre funções posicionais nos artigos "Funções de Janela (Window Functions) em SQL: Funções Posicionais " de Aldo Zelen e "Quando Usar Funções de Janela (Window Functions) em SQL? " de Tihomir Babic. Se você quiser aprender mais sobre funções de janela, experimente nosso curso interativo "Funções de Janela (Window Functions) em SQL" na plataforma LearnSQL.com.br.