São funções analíticas dentro da linguagem SQL que funcionam como uma espécie de offset na exibição dos resultados de uma query executada.

Estas funções consideram a linha atual para então exibir um valor anterior (LAG) e o próximo valor (LEAD).

Se baseiam na cláusula ORDER BY e de acordo com a coluna que está sendo utilizada pelo PARTITION para assim, retornar o valor desejado

Aceita como parâmetro três argumentos quando utilizamos estas funções:

  1. A coluna que queremos retornar o valor para comparação;
  2. A quantidade de linhas que serão retornadas. Retorna uma linha por padrão.
  3. E o valor padrão de retorno que não houver um valor para retornar. Se nada for especificado, retornará NULL.

FUNÇÃO LAG

Repare no primeiro exemplo que a partir da segunda linha(coluna RNUMBER), temos o valor da linha anterior retornado na coluna PRE_VAL

Podemos substituir o NULL retornado na primeira linha por outro valor qualquer, neste caso, irei colocar o zero. Além, vou adicionar outro parâmetro para que retorne as 2 linhas anteriores a linha atual.

Percebam que somente a partir da linha 3 tanto do CUSTID 1(vermelho) e 2(preto) que os resultados começam a ser retornados.

Vejam também que mesmo sendo um resultado único, quando o query percebe que o custid mudou, a contagem começa do 0 novamente.  Isso ocorre por causa da cláusula PARTITION BY que reparte a query pelo id dos clientes.

E o segundo parâmetro lá na função lag(sublinhado de azul) é quem ‘ignora’ as duas primeiras linhas e seus valores anteriores, retornando 0 (já que foi passado como terceiro parâmetro), substituindo o NULL como foi dito na explicação do exemplo.

LEAD

O raciocínio para a função LEAD é o mesmo com os mesmos parâmetros, alterando apenas o valor retornado da query.

E percebam também que o valor que retorna NULL é o da última linha da consulta, antes de mudar para o cliente com ID 02.

Se for da vontade, podemos calcular na linha atual, a diferença entre o pedido anterior e o próximo pedido usando as funções de LAG e LEAD. Veja a query abaixo:

Veja que a coluna next_val faz uma subtração com a prev_val da linha três do cliente com ID 01. Ou seja, a linha anterior e a próxima realizam uma operação e retornam  a diferença na coluna diff_val. 

Se quisermos calcular apenas a diferença entre a linha atual e a próxima linha, podemos:

Como podemos ver, nas linhas três e quatro, a diferença foi aplicada apenas entre o valor corrente e o próximo, diferente da query anterior. Numa situação real, não multipliquem por -1, só fiz para todos retornarem positivos.

Com isso, termino essas funções de exibição para queries voltadas  para análise de dados utilizando a linguagem SQL.

E fiquem tranquilos, em breve irei abordar sobre a função OVER e suas funções.

Esse foi um post onde utilizamos alguns conceitos anteriores mas que, são extremamente úteis e que podem nos ajudar em diversas soluções.

Espero que tenham gostado! Saúde.