SQL LAG e LEAD

Num SELECT comum, cada linha só enxerga a si mesma. Para saber "este pedido foi maior ou menor que o anterior?" ou "quantos dias o cliente demorou para voltar?", a linha precisa espiar a vizinha. É exatamente o que fazem duas funções de janela: LAG traz um valor da linha anterior, e LEAD, da linha seguinte. Nesta aula a Ana usa as duas para comparar pedidos, acompanhar as vendas mês a mês e medir, em dias, o intervalo entre as compras dos clientes.

Esta aula continua as de Funções de Janela (OVER) e ROW_NUMBER, RANK e DENSE_RANK. Todos os exemplos rodam de verdade no seu navegador, no banco da Loja Aurora.

A ideia: o extrato do banco

Pense no extrato da sua conta: uma linha por dia, com o saldo. Para saber se você gastou mais hoje do que ontem, você olha a linha de cima. Para saber quanto tempo falta para o próximo depósito, olha a de baixo. Você não junta as linhas, não soma nada: só espia a vizinha.

1. O pedido anterior e o seguinte

O José Ribamar Silva (cliente 1) fez 4 pedidos. Para cada um, a data do pedido anterior e a do seguinte dele:

SELECT id,
       data,
       LAG(data)  OVER (ORDER BY data) AS anterior,
       LEAD(data) OVER (ORDER BY data) AS seguinte
FROM pedidos
WHERE cliente_id = 1
ORDER BY data;

LAG(data)

"O valor de data na linha anterior". A coluna entre parênteses é o que buscar na vizinha.

LEAD(data)

O mesmo, na linha seguinte.

OVER (ORDER BY data)

Define quem é "anterior" e "seguinte": a ordem das datas. É a parte mais importante; sem ela, a vizinhança é a ordem que o SQLite quiser.

WHERE cliente_id = 1

O WHERE roda antes da janela (aula Funções de Janela): a janela só enxerga os 4 pedidos do José. Por isso o "anterior" do pedido 27 é o pedido 1 dele, e não o pedido 26 da loja.

Repare nas pontas: o primeiro pedido não tem anterior, e o último não tem seguinte. Nesses lugares a função devolve NULL. Você vai ver na seção 4 como trocar esse NULL por outra coisa.

SELECT id, data
FROM pedidos
WHERE cliente_id = 3
ORDER BY data;

SELECT id,
       data,
       LAG(data) OVER (ORDER BY data) AS data_anterior
FROM pedidos
WHERE cliente_id = 3
ORDER BY data;

2. Comparar com o anterior: a diferença

Com o valor anterior na mesma linha, é só fazer a conta. Os pedidos de julho em diante de 2026, cada um comparado com o pedido anterior da loja. O valor de cada pedido vem de uma CTE (aula CTE (WITH)):

WITH totais AS (
  SELECT p.id, p.data,
         ROUND(SUM(i.quantidade * i.preco_unitario), 2) AS valor
  FROM pedidos AS p
  JOIN itens_pedido AS i ON i.pedido_id = p.id
  WHERE p.data >= '2026-07-01'
  GROUP BY p.id
)
SELECT id,
       data,
       valor,
       LAG(valor) OVER (ORDER BY data) AS anterior,
       ROUND(valor - LAG(valor) OVER (ORDER BY data), 2) AS diferenca
FROM totais
ORDER BY data;

LAG(valor) OVER (ORDER BY data)

O valor do pedido anterior, em ordem de data.

valor - LAG(valor) OVER (…)

Positivo: este pedido foi maior que o anterior. Negativo: menor. Na primeira linha, qualquer conta com NULL dá NULL (aula O Valor NULL).

O pedido 88, de 5 de outubro, foi R$ 474,97 maior que o anterior: é um dos maiores pedidos da história da loja (R$ 619,47).

A variação mês a mês

O mesmo truque com as vendas de cada mês de 2025, agora em porcentagem: "quanto este mês subiu ou caiu em relação ao anterior". A mesma janela aparece três vezes, então ela ganha nome com WINDOW:

WITH mensal AS (
  SELECT strftime('%Y-%m', p.data) AS mes,
         ROUND(SUM(i.quantidade * i.preco_unitario), 2) AS vendas
  FROM pedidos AS p
  JOIN itens_pedido AS i ON i.pedido_id = p.id
  WHERE p.data LIKE '2025-%'
  GROUP BY mes
)
SELECT mes,
       vendas,
       LAG(vendas) OVER m AS mes_anterior,
       ROUND(100.0 * (vendas - LAG(vendas) OVER m) / LAG(vendas) OVER m, 1) AS variacao_pct
FROM mensal
WINDOW m AS (ORDER BY mes)
ORDER BY mes;

WINDOW m AS (ORDER BY mes)

A janela com nome, para não repetir OVER (ORDER BY mes) três vezes.

100.0 * (vendas - anterior) / anterior

A conta da porcentagem de variação: a diferença dividida pelo valor de antes.

Abril caiu 95,7% (de R$ 579,07 para R$ 25,13), e por isso maio aparece com +3119,6%: sair de quase nada para R$ 809,09 é muitas vezes mais. Variações em porcentagem enganam quando a base é pequena; mostre sempre o valor junto.

3. Os dias entre um pedido e outro: julianday

A pergunta da Ana agora é sobre tempo: "de quantos em quantos dias o José volta a comprar?". O primeiro impulso é subtrair as datas direto, mas isso não funciona (veja em Quando dá errado). As datas da Aurora são texto no formato AAAA-MM-DD; para fazer conta, transforme cada uma num número de dias com julianday (aula Funções de Data):

SELECT julianday('2024-01-05') AS dia_juliano,
       julianday('2024-10-29') - julianday('2024-01-05') AS diferenca;

julianday('2024-01-05')

O "dia juliano": quantos dias se passaram desde uma data muito antiga (no ano 4713 antes de Cristo), uma contagem usada na astronomia. O .5 no fim é porque o dia juliano começa ao meio-dia; a meia-noite de uma data fica no meio.

julianday(…) - julianday(…)

A diferença entre dois dias julianos é o número de dias entre as datas: 298 dias entre 5 de janeiro e 29 de outubro de 2024. Sai com .0 porque é um número real.

Agora junte com o LAG: em cada pedido, os dias desde o pedido anterior do mesmo cliente.

SELECT id,
       data,
       LAG(data) OVER (ORDER BY data) AS anterior,
       CAST(julianday(data) - julianday(LAG(data) OVER (ORDER BY data)) AS INTEGER) AS dias
FROM pedidos
WHERE cliente_id = 1
ORDER BY data;

julianday(LAG(data) OVER (…))

A data do pedido anterior, virada em número de dias. A janela vai dentro da função, sem problema.

CAST(… AS INTEGER)

Tira o .0: 298 em vez de 298.0 (aula Os Tipos no SQLite).

O José compra mais ou menos a cada 300 dias: uma vez por ano, quase certinho.

LEAD: quanto tempo até o próximo

Com LEAD, a conta olha para a frente. Os pedidos da loja desde setembro de 2026 e quantos dias se passaram até o próximo pedido chegar:

SELECT id,
       data,
       LEAD(data) OVER (ORDER BY data) AS proximo,
       CAST(julianday(LEAD(data) OVER (ORDER BY data)) - julianday(data) AS INTEGER) AS espera
FROM pedidos
WHERE data >= '2026-09-01'
ORDER BY data;

O último pedido (90, de 14 de outubro) ainda não tem próximo: NULL.

De quantos em quantos dias a loja recebe pedido?

Calcule o intervalo de cada pedido para o anterior (agora na loja inteira) numa CTE, e resuma fora dela. Não dá para pôr AVG(LAG(…)) direto: uma agregação não pode conter uma janela.

WITH intervalos AS (
  SELECT id,
         julianday(data) - julianday(LAG(data) OVER (ORDER BY data)) AS dias
  FROM pedidos
)
SELECT ROUND(AVG(dias), 1) AS media_de_dias,
       MIN(dias) AS menor,
       MAX(dias) AS maior
FROM intervalos;

Em média, um pedido a cada 11,4 dias; nunca menos de 4 dias de intervalo, nunca mais de 18. O AVG, o MIN e o MAX ignoram o NULL do primeiro pedido (aula Funções Agregadas).

4. Pular mais de uma linha e o valor padrão

LAG e LEAD aceitam mais dois valores entre os parênteses:

LAG(coluna, quantas_linhas, valor_se_nao_houver)

SELECT id,
       data,
       LAG(data, 1, 'primeiro pedido') OVER (ORDER BY data) AS anterior,
       LAG(data, 2) OVER (ORDER BY data) AS dois_antes
FROM pedidos
WHERE cliente_id = 1
ORDER BY data;

LAG(data, 1, 'primeiro pedido')

Uma linha para trás (o padrão é 1) e, quando não houver linha anterior, o texto 'primeiro pedido' no lugar do NULL.

LAG(data, 2)

Duas linhas para trás. Os dois primeiros pedidos não têm "dois antes", e ficam NULL.

5. Formas diferentes de fazer a mesma coisa

Antes das funções de janela, havia dois jeitos de achar "o pedido anterior". Um funciona; o outro engana.

a) Subconsulta correlacionada

SELECT p.id,
       p.data,
       (SELECT MAX(p2.data)
        FROM pedidos AS p2
        WHERE p2.cliente_id = 1
          AND p2.data < p.data) AS anterior
FROM pedidos AS p
WHERE p.cliente_id = 1
ORDER BY p.data;

"A maior data do José que seja menor que a deste pedido." Dá o mesmo que o LAG, mas a condição do cliente aparece duas vezes, a subconsulta roda uma vez por linha, e buscar outra coluna do pedido anterior (o valor, o id) exige outra subconsulta.

b) Self-join pelo id: a armadilha

A ideia parece boa: "o pedido anterior é o de id um a menos" (aula Self Join):

SELECT p.id,
       p.data,
       a.data AS anterior
FROM pedidos AS p
LEFT JOIN pedidos AS a ON a.id = p.id - 1
WHERE p.cliente_id = 1
ORDER BY p.data;

Compare com o resultado da seção 1: as datas "anteriores" estão erradas. O pedido 26 é o anterior da loja, de outro cliente, e não o anterior do José. E mesmo para a loja inteira, isso só funciona enquanto os id estiverem em ordem de data e sem buracos (um pedido apagado já quebra a conta). O LAG não depende de nada disso: ele segue o ORDER BY que você mandar.

Quando dá errado

SELECT id,
       data,
       data - LAG(data) OVER (ORDER BY data) AS dias
FROM pedidos
WHERE cliente_id = 1;

SELECT julianday('05/01/2024') AS brasileiro,
       julianday('2024-1-5') AS sem_zeros,
       julianday('2024-01-05') AS iso;

SELECT id, LAG(data) AS anterior
FROM pedidos
WHERE cliente_id = 1;

SELECT id, LAG() OVER (ORDER BY data) AS anterior
FROM pedidos
WHERE cliente_id = 1;

SELECT id, data
FROM pedidos
WHERE julianday(data) - julianday(LAG(data) OVER (ORDER BY data)) < 7;

-- MySQL
SELECT id, DATEDIFF(data, LAG(data) OVER (ORDER BY data)) AS dias FROM pedidos;
-- SQL Server
SELECT id, DATEDIFF(day, LAG(data) OVER (ORDER BY data), data) AS dias FROM pedidos;
-- PostgreSQL (com a coluna data do tipo date)
SELECT id, data - LAG(data) OVER (ORDER BY data) AS dias FROM pedidos;

Referência rápida

Exercícios

Missão final: o intervalo da Ana Beatriz

SELECT data
FROM pedidos
WHERE cliente_id = 3
ORDER BY data;

SELECT data,
       CAST(julianday(data) - julianday(LAG(data) OVER (ORDER BY data)) AS INTEGER) AS dias
FROM pedidos
WHERE cliente_id = 3
ORDER BY data;

299, 309 e 299 dias: como o José, a Ana Beatriz compra uma vez por ano. A primeira linha fica com NULL, porque não há pedido anterior a ela.

Resumo

• LAG(x) traz o valor de x da linha anterior; LEAD(x), da seguinte. As duas são funções de janela: OVER (ORDER BY …) diz quem é vizinho de quem.
• Nas pontas, sem vizinha, o resultado é NULL. LAG(x, 1, 'padrão') troca esse NULL; LAG(x, 2) pula duas linhas.
• Com o valor vizinho na mesma linha, você calcula diferenças e variações: x - LAG(x) OVER (…).
• Datas são texto: para contar dias, julianday(data) - julianday(LAG(data) OVER (…)), com CAST(… AS INTEGER) para tirar o .0. Subtrair o texto direto dá a diferença dos anos.
• Para filtrar ou resumir pelo resultado do LAG, calcule numa CTE primeiro.
• O self-join pelo id - 1 só acerta por sorte; o LAG segue a ordem que você pedir.
• Próxima aula: a janela que recomeça em cada grupo, para fazer tudo isso por cliente e por categoria de uma vez: PARTITION BY.