LAG() e LEAD() no PostgreSQL: análise período a período sem self-joins

Comparar cada linha com a anterior ou a próxima — a receita do mês passado, a leitura anterior, o próximo evento — é uma das necessidades mais comuns de análise. Antes das window functions, isso exigia self-joins horríveis. O LAG() e o LEAD() transformam isso em uma única passada legível sobre os dados.

O problema que resolvem

"Quanto a receita variou em relação ao mês passado?" Responder isso com SQL puro tradicionalmente significava juntar a tabela com ela mesma em mes = mes - 1 — verboso, lento e propenso a erros nas bordas. O LAG() e o LEAD() deixam cada linha espiar uma linha irmã da mesma partição sem nenhum join.

• LAG(col, n, padrao) — valor de n linhas antes da atual (padrão n = 1).
• LEAD(col, n, padrao) — valor de n linhas depois.

Sintaxe

LAG(coluna [, deslocamento [, valor_padrao]]) OVER (
  [PARTITION BY coluna_ou_expr]
  ORDER BY coluna_ou_expr   -- define o que é "anterior"
)

O ORDER BY é o que dá sentido a "anterior" e "próximo" — na prática, ele não é opcional. O PARTITION BY mantém o espiar dentro de cada grupo, então a primeira linha de um cliente nunca lê a última de outro.

Crescimento mês a mês

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS delta,
  round(
    100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
    / NULLIF(LAG(revenue) OVER (ORDER BY month), 0)
  , 1) AS pct_change
FROM monthly_revenue
ORDER BY month;

O NULLIF(..., 0) protege contra divisão por zero quando o mês anterior foi 0.

Valores padrão para as bordas

A primeira linha não tem antecessora, então o LAG() retorna NULL. Passe um terceiro argumento como valor padrão:

LAG(revenue, 1, 0) OVER (ORDER BY month)  -- a primeira linha recebe 0 em vez de NULL

Detectando lacunas em uma sequência

Encontre números de nota fiscal faltando — linhas em que o próximo número pula mais de 1:

SELECT invoice_no, next_no
FROM (
  SELECT
    invoice_no,
    LEAD(invoice_no) OVER (ORDER BY invoice_no) AS next_no
  FROM invoices
) s
WHERE next_no - invoice_no > 1;

Tempo entre eventos

Meça os segundos entre logins consecutivos por usuário — base para sessionização e detecção de anomalias:

SELECT
  user_id,
  logged_at,
  logged_at - LAG(logged_at) OVER (
    PARTITION BY user_id ORDER BY logged_at
  ) AS gap
FROM logins;

Por que vence o self-join

Um self-join relê a tabela e casa linhas por um predicado de junção — risco quadrático se as colunas não estiverem perfeitamente indexadas. O LAG()/LEAD() faz uma única passada ordenada (WindowAgg), então é mais rápido e mais difícil de errar.

Da análise ao alerta

O padrão de "lacuna entre eventos" acima é exatamente como o PG Monitoring detecta paradas silenciosas — um replication slot que para de avançar, um job que silenciosamente deixa de disparar. O mesmo raciocínio de window function que move seus relatórios move a detecção de anomalias em produção.