Em análises do dia a dia, muitas perguntas não são sobre o valor absoluto de uma métrica, mas sobre a variação entre períodos: quanto cresceu em relação ao mês anterior, qual foi a queda desde a última compra, qual a diferença entre o preço atual e o anterior, ou ainda se um indicador está acelerando ou desacelerando. Para esse tipo de leitura “período a período”, as funções analíticas LAG e LEAD são ferramentas centrais: elas permitem acessar valores de linhas anteriores ou posteriores dentro de uma mesma partição (por exemplo, por cliente, por produto, por loja), respeitando uma ordenação temporal (por exemplo, por dia, semana, mês).
O que são LAG e LEAD (e por que são diferentes de JOINs e subqueries)
LAG retorna um valor de uma linha anterior (passado) em relação à linha atual. LEAD retorna um valor de uma linha posterior (futuro) em relação à linha atual. Em ambos os casos, você define:
- Qual coluna quer “puxar” do passado/futuro;
- Quantas linhas de distância (offset) quer olhar (1 período atrás, 2 períodos atrás etc.);
- Qual valor padrão usar quando não existir linha anterior/posterior (por exemplo, no primeiro mês do cliente);
- Como particionar (por cliente, por produto, por canal) e como ordenar (por data do evento, por mês, por sequência).
O ganho prático é que você calcula deltas e variações sem precisar “auto-join” (juntar a tabela com ela mesma) nem escrever subqueries complexas para achar o registro anterior. Isso deixa o SQL mais legível e, em muitos cenários, mais eficiente.
Sintaxe essencial
LAG(valor [, offset [, default]]) OVER (PARTITION BY ... ORDER BY ...)LEAD(valor [, offset [, default]]) OVER (PARTITION BY ... ORDER BY ...)Onde:
- valor: a coluna que você quer trazer da linha anterior/posterior;
- offset: quantas linhas de distância (padrão = 1);
- default: valor usado quando não há linha anterior/posterior (opcional);
- PARTITION BY: define “grupos independentes” (ex.: cada cliente);
- ORDER BY: define a sequência (ex.: mês crescente).
Quando usar: deltas, variações percentuais e acompanhamento
Os usos mais comuns em relatórios e análises operacionais incluem:
Continue em nosso aplicativo
Você poderá ouvir o audiobook com a tela desligada, ganhar gratuitamente o certificado deste curso e ainda ter acesso a outros 5.000 cursos online gratuitos.
ou continue lendo abaixo...
Baixar o aplicativo
- Delta absoluto: diferença entre o valor atual e o anterior (ex.: receita do mês - receita do mês anterior).
- Variação percentual: (valor atual - anterior) / anterior.
- Comparação com N períodos atrás: mês atual vs. 3 meses atrás.
- Identificação de mudanças: detectar quando um status mudou (ex.: plano do cliente, faixa de preço, categoria).
- Tempo entre eventos: dias desde a última compra, tempo até a próxima compra.
- Projeções simples e “próximo evento”: olhar a próxima data agendada, o próximo preço, o próximo status.
Passo a passo prático: variação mês a mês de receita por produto
Imagine uma tabela de fatos mensal já agregada (ou uma visão) com receita por produto e mês:
fato_receita_mensal(produto_id, mes, receita)Objetivo: para cada produto e mês, calcular a receita do mês anterior, o delta absoluto e a variação percentual.
Passo 1: garantir a ordenação correta
LAG/LEAD dependem totalmente do ORDER BY dentro do OVER. Para séries temporais, use um campo que ordene corretamente (por exemplo, um primeiro dia do mês, ou um inteiro AAAAMM). Exemplo com mes como data (primeiro dia do mês):
SELECT produto_id, mes, receita FROM fato_receita_mensal;Se mes fosse texto, você correria risco de ordenar errado (por exemplo, “2024-10” antes de “2024-2”). O ideal é que mes seja um tipo de data ou um número.
Passo 2: trazer o valor anterior com LAG
SELECT produto_id, mes, receita, LAG(receita) OVER (PARTITION BY produto_id ORDER BY mes) AS receita_mes_anterior FROM fato_receita_mensal;Agora cada linha tem a receita do mês anterior do mesmo produto. No primeiro mês de cada produto, receita_mes_anterior será nulo (a menos que você defina um default).
Passo 3: calcular delta absoluto
SELECT produto_id, mes, receita, LAG(receita) OVER (PARTITION BY produto_id ORDER BY mes) AS receita_mes_anterior, receita - LAG(receita) OVER (PARTITION BY produto_id ORDER BY mes) AS delta_receita FROM fato_receita_mensal;Esse delta responde “quanto variou em reais” em relação ao mês anterior.
Passo 4: calcular variação percentual com cuidado
Para variação percentual, você precisa tratar o caso em que o mês anterior é nulo ou zero. Uma forma comum é usar NULLIF para evitar divisão por zero e deixar o resultado nulo quando não for calculável.
SELECT produto_id, mes, receita, LAG(receita) OVER (PARTITION BY produto_id ORDER BY mes) AS receita_mes_anterior, receita - LAG(receita) OVER (PARTITION BY produto_id ORDER BY mes) AS delta_receita, (receita - LAG(receita) OVER (PARTITION BY produto_id ORDER BY mes)) / NULLIF(LAG(receita) OVER (PARTITION BY produto_id ORDER BY mes), 0) AS variacao_pct FROM fato_receita_mensal;Em alguns bancos, a divisão entre inteiros pode truncar casas decimais. Se necessário, force tipo decimal (por exemplo, multiplicando por 1.0 ou fazendo cast) para obter percentual com precisão.
Passo 5: evitar repetição com uma CTE (organização do cálculo)
Como a expressão de LAG(receita) aparece várias vezes, é comum calcular uma vez e reutilizar. Isso melhora legibilidade e reduz chance de inconsistência.
WITH base AS ( SELECT produto_id, mes, receita, LAG(receita) OVER (PARTITION BY produto_id ORDER BY mes) AS receita_mes_anterior FROM fato_receita_mensal) SELECT produto_id, mes, receita, receita_mes_anterior, receita - receita_mes_anterior AS delta_receita, (receita - receita_mes_anterior) / NULLIF(receita_mes_anterior, 0) AS variacao_pct FROM base;Esse padrão (calcular o “valor anterior” em uma etapa e depois derivar métricas) é muito usado em relatórios recorrentes.
Offset: comparando com 2, 3 ou N períodos atrás
Nem sempre o comparativo é “mês anterior”. Às vezes você quer “mesmo período de 3 meses atrás” (um trimestre) ou “semana anterior” em uma série semanal. O offset resolve isso.
SELECT produto_id, mes, receita, LAG(receita, 3) OVER (PARTITION BY produto_id ORDER BY mes) AS receita_3_meses_atras, receita - LAG(receita, 3) OVER (PARTITION BY produto_id ORDER BY mes) AS delta_3_meses FROM fato_receita_mensal;Interpretação: para cada mês, traz a receita de três linhas antes na ordem. Isso pressupõe que sua série tenha um registro por mês. Se houver meses faltantes, “3 linhas atrás” não é necessariamente “3 meses atrás”. Em cenários com lacunas, você pode precisar completar calendário ou trabalhar com uma tabela calendário para garantir continuidade.
Default: definindo valor quando não existe anterior/posterior
Você pode definir um valor padrão para quando não houver linha anterior/posterior. Isso pode ser útil para relatórios que não aceitam nulos, mas exige critério: colocar 0 pode distorcer variações percentuais.
SELECT produto_id, mes, receita, LAG(receita, 1, 0) OVER (PARTITION BY produto_id ORDER BY mes) AS receita_mes_anterior_com_default FROM fato_receita_mensal;Uma prática comum é manter nulo para cálculos percentuais (para não inventar base) e, se necessário, tratar a exibição no relatório.
LEAD: olhando o próximo período (previsão operacional e “próximo evento”)
Enquanto LAG responde “de onde eu vim”, LEAD responde “para onde eu vou”. Exemplos práticos:
- Identificar a próxima compra do cliente e calcular o tempo até ela.
- Ver o próximo status de um ticket e medir tempo em cada etapa.
- Comparar o preço atual com o próximo preço (mudanças programadas).
Exemplo com uma tabela de compras por cliente:
compras(cliente_id, data_compra, valor)Queremos, para cada compra, a data da próxima compra do mesmo cliente e o intervalo em dias (ou na unidade que fizer sentido no seu banco).
WITH base AS ( SELECT cliente_id, data_compra, valor, LEAD(data_compra) OVER (PARTITION BY cliente_id ORDER BY data_compra) AS proxima_compra FROM compras) SELECT cliente_id, data_compra, valor, proxima_compra, proxima_compra - data_compra AS dias_ate_proxima_compra FROM base;O cálculo exato de diferença de datas varia entre bancos (alguns retornam intervalo, outros exigem função específica). A ideia central é: LEAD fornece a referência do “próximo evento” sem auto-join.
Detectando mudanças de estado com LAG (ex.: status, plano, categoria)
Um uso muito valioso é detectar quando um atributo mudou. Suponha uma tabela de histórico de status por cliente:
status_cliente(cliente_id, data_evento, status)Você quer marcar apenas os momentos em que o status mudou (por exemplo, de “ativo” para “inativo”).
WITH base AS ( SELECT cliente_id, data_evento, status, LAG(status) OVER (PARTITION BY cliente_id ORDER BY data_evento) AS status_anterior FROM status_cliente) SELECT cliente_id, data_evento, status, status_anterior, CASE WHEN status_anterior IS NULL THEN 0 WHEN status <> status_anterior THEN 1 ELSE 0 END AS mudou_status FROM base;Esse padrão é útil para auditoria, churn, mudanças de plano e qualquer trilha de eventos. Se houver eventos duplicados na mesma data/hora, você precisa de um critério de desempate no ORDER BY (por exemplo, um id incremental do evento) para garantir determinismo.
Armadilhas comuns e como evitar
1) ORDER BY insuficiente (empates na ordenação)
Se duas linhas tiverem o mesmo valor no campo usado para ordenar (por exemplo, duas compras no mesmo segundo), o banco pode escolher qualquer uma como “anterior” e “posterior” dependendo do plano de execução. Para evitar, adicione um segundo critério de ordenação que torne a sequência única.
LAG(valor) OVER (PARTITION BY cliente_id ORDER BY data_evento, evento_id)2) Confundir “linha anterior” com “período anterior”
LAG/LEAD navegam por linhas, não por calendário. Se sua série tiver buracos (meses sem registro), o “anterior” será o último mês com dado, não necessariamente o mês imediatamente anterior no calendário. Se a pergunta de negócio exige “mês anterior do calendário”, você pode precisar gerar meses faltantes e preencher com zero ou nulo antes de aplicar LAG.
3) Misturar granularidades
Se você aplicar LAG em dados diários, mas quer variação mensal, o resultado não fará sentido. Garanta que a tabela/consulta esteja na granularidade correta antes de calcular deltas. Uma boa prática é: primeiro consolidar na granularidade desejada (dia/semana/mês), depois aplicar LAG/LEAD.
4) Variação percentual com base nula ou zero
Quando o valor anterior é nulo (primeiro período) ou zero, a variação percentual pode ser indefinida. Use NULLIF para evitar divisão por zero e decida como reportar esses casos (nulo, 0, ou uma flag “sem base”).
Padrões prontos para relatórios: delta, % e aceleração
Além do delta simples, é comum querer entender se a variação está acelerando: isto é, comparar o delta atual com o delta anterior (uma “segunda diferença”). Você faz isso aplicando LAG sobre o delta.
Exemplo: receita mensal por produto, com delta e “delta do delta”.
WITH base AS ( SELECT produto_id, mes, receita, LAG(receita) OVER (PARTITION BY produto_id ORDER BY mes) AS receita_mes_anterior FROM fato_receita_mensal), deltas AS ( SELECT produto_id, mes, receita, receita_mes_anterior, receita - receita_mes_anterior AS delta_receita FROM base) SELECT produto_id, mes, receita, receita_mes_anterior, delta_receita, LAG(delta_receita) OVER (PARTITION BY produto_id ORDER BY mes) AS delta_anterior, delta_receita - LAG(delta_receita) OVER (PARTITION BY produto_id ORDER BY mes) AS aceleracao_delta FROM deltas;Interpretação:
- delta_receita: variação mês a mês.
- aceleracao_delta: se positivo, o crescimento está acelerando (o delta aumentou); se negativo, está desacelerando.
Checklist mental para usar LAG/LEAD com segurança
- Defina claramente a entidade da análise (cliente, produto, loja) e use em PARTITION BY.
- Defina a sequência (data/hora, mês, número de parcela) e use em ORDER BY, com desempate se necessário.
- Garanta a granularidade correta antes de aplicar a função.
- Decida como tratar primeiro/último período (nulo vs default).
- Para percentuais, proteja divisão com NULLIF e pense na interpretação quando a base é zero.
- Se a pergunta é “período anterior do calendário”, verifique se há lacunas na série.
