Window functions: LAG, LEAD, FIRST_VALUE, LAST_VALUE

1. Introdução às Funções de Janela de Deslocamento e Valor

As funções de janela (window functions) são um dos recursos mais poderosos do SQL moderno. Diferentemente das funções de agregação tradicionais como SUM() ou AVG(), que colapsam múltiplas linhas em um único resultado, as funções de janela realizam cálculos através de um conjunto de linhas relacionadas à linha atual — sem perder a granularidade dos dados individuais.

A cláusula OVER é o coração dessas funções. Ela define a "janela" de linhas sobre a qual o cálculo será aplicado, podendo incluir partições (PARTITION BY) e ordenação (ORDER BY).

Neste artigo, focaremos em quatro funções específicas: LAG e LEAD (funções de deslocamento) e FIRST_VALUE e LAST_VALUE (funções de valor em posições). Enquanto as primeiras permitem acessar linhas anteriores ou posteriores à linha atual, as segundas recuperam valores específicos dentro da janela definida.

2. LAG: Acessando Linhas Anteriores

A função LAG permite acessar dados de linhas que vêm antes da linha atual dentro da janela. Sua sintaxe básica é:

LAG(coluna, offset, valor_padrao) OVER (PARTITION BY ... ORDER BY ...)

O parâmetro offset (padrão 1) indica quantas linhas anteriores acessar, e valor_padrao é retornado quando não existe linha no offset especificado.

Exemplo prático: Calcular a diferença de vendas entre o mês atual e o mês anterior.

SELECT 
    mes,
    valor_venda,
    LAG(valor_venda, 1, 0) OVER (ORDER BY mes) AS venda_mes_anterior,
    valor_venda - LAG(valor_venda, 1, 0) OVER (ORDER BY mes) AS diferenca
FROM vendas_mensais;

Com PARTITION BY: Comparar compras de cada cliente com sua compra anterior.

SELECT 
    cliente_id,
    data_compra,
    valor,
    LAG(valor, 1, 0) OVER (PARTITION BY cliente_id ORDER BY data_compra) AS ultima_compra
FROM compras;

3. LEAD: Acessando Linhas Posteriores

LEAD é o espelho de LAG: acessa linhas que vêm depois da linha atual. Sintaxe idêntica, mas o deslocamento é para frente.

LEAD(coluna, offset, valor_padrao) OVER (PARTITION BY ... ORDER BY ...)

Exemplo: Identificar o próximo evento em uma sequência temporal.

SELECT 
    evento,
    data_evento,
    LEAD(data_evento, 1, 'Sem próximo') OVER (ORDER BY data_evento) AS proximo_evento
FROM cronograma;

Combinação de LAG e LEAD para calcular uma média móvel simples de 3 pontos:

SELECT 
    data,
    valor,
    (LAG(valor, 1, valor) OVER (ORDER BY data) + 
     valor + 
     LEAD(valor, 1, valor) OVER (ORDER BY data)) / 3.0 AS media_movel_3p
FROM serie_temporal;

4. FIRST_VALUE: Primeiro Valor de uma Janela

FIRST_VALUE retorna o valor da primeira linha dentro da janela definida. Sua sintaxe é direta:

FIRST_VALUE(coluna) OVER (PARTITION BY ... ORDER BY ...)

Exemplo: Obter o salário inicial de cada funcionário ao longo de sua carreira.

SELECT 
    funcionario_id,
    data_reajuste,
    salario,
    FIRST_VALUE(salario) OVER (PARTITION BY funcionario_id ORDER BY data_reajuste) AS salario_inicial
FROM historico_salarios;

Diferença entre FIRST_VALUE e MIN(): Enquanto MIN() retorna o menor valor independente da ordenação, FIRST_VALUE respeita a ordenação definida no ORDER BY. Se você ordenar por data crescente, FIRST_VALUE retorna o salário mais antigo, não necessariamente o menor.

5. LAST_VALUE: Último Valor de uma Janela (e a Armadilha do Frame)

LAST_VALUE retorna o valor da última linha da janela. No entanto, existe uma armadilha importante:

LAST_VALUE(coluna) OVER (PARTITION BY ... ORDER BY ...)

O problema do frame padrão: Por padrão, o frame de uma janela com ORDER BY é RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Isso significa que LAST_VALUE considera apenas da primeira linha até a linha atual — resultando sempre no valor da linha atual, não no último valor de toda a partição.

Solução: Definir explicitamente o frame completo:

SELECT 
    funcionario_id,
    data_reajuste,
    salario,
    LAST_VALUE(salario) OVER (
        PARTITION BY funcionario_id 
        ORDER BY data_reajuste
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS salario_atual
FROM historico_salarios;

Comparação prática:

-- Comportamento incorreto (frame padrão)
SELECT 
    id, 
    valor,
    LAST_VALUE(valor) OVER (ORDER BY id) as ultimo_errado
FROM dados;

-- Comportamento correto
SELECT 
    id,
    valor,
    LAST_VALUE(valor) OVER (
        ORDER BY id
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as ultimo_correto
FROM dados;

6. Casos de Uso Combinados e Avançados

Diferença entre primeiro e último valor de um grupo:

SELECT 
    departamento,
    FIRST_VALUE(salario) OVER (PARTITION BY departamento ORDER BY data_admissao) AS salario_inicial,
    LAST_VALUE(salario) OVER (
        PARTITION BY departamento 
        ORDER BY data_admissao
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS salario_final,
    LAST_VALUE(salario) OVER (...) - FIRST_VALUE(salario) OVER (...) AS crescimento
FROM funcionarios;

Identificação de picos e vales em séries temporais:

SELECT 
    data,
    valor,
    CASE 
        WHEN valor > LAG(valor, 1, valor) OVER (ORDER BY data) 
         AND valor > LEAD(valor, 1, valor) OVER (ORDER BY data) THEN 'PICO'
        WHEN valor < LAG(valor, 1, valor) OVER (ORDER BY data) 
         AND valor < LEAD(valor, 1, valor) OVER (ORDER BY data) THEN 'VALE'
        ELSE 'NORMAL'
    END AS classificacao
FROM cotacoes;

Uso de FIRST_VALUE para "carregar" um valor fixo:

SELECT 
    cliente_id,
    data_pedido,
    FIRST_VALUE(data_primeiro_pedido) OVER (
        PARTITION BY cliente_id ORDER BY data_pedido
    ) AS primeiro_pedido,
    data_pedido - FIRST_VALUE(data_pedido) OVER (
        PARTITION BY cliente_id ORDER BY data_pedido
    ) AS dias_desde_primeiro
FROM pedidos;

7. Boas Práticas e Performance

Ordenação e índices: Sempre que possível, crie índices compostos que correspondam às colunas usadas em PARTITION BY e ORDER BY. Por exemplo, para LAG(valor) OVER (PARTITION BY cliente_id ORDER BY data), um índice em (cliente_id, data, valor) melhora significativamente a performance.

Desempenho vs. subconsultas: LAG e LEAD são geralmente mais eficientes que subconsultas correlacionadas para acessar linhas adjacentes. Uma subconsulta como:

SELECT valor - (SELECT valor FROM tabela t2 WHERE t2.id = t1.id - 1) FROM tabela t1;

Pode ser substituída por:

SELECT valor - LAG(valor) OVER (ORDER BY id) FROM tabela;

A versão com window function é mais legível e tende a ter melhor performance, especialmente em volumes grandes de dados.

Alternativas com JOIN: Em alguns casos, especialmente quando o offset é grande ou a lógica de deslocamento é complexa, um JOIN com deslocamento manual pode ser mais eficiente. Por exemplo, para comparar cada linha com a linha 5 posições atrás:

-- Com LAG (simples, mas pode ser lento para offsets grandes)
SELECT LAG(valor, 5) OVER (ORDER BY id) FROM tabela;

-- Com JOIN (pode ser mais rápido com índices adequados)
SELECT t1.valor, t2.valor
FROM tabela t1
LEFT JOIN tabela t2 ON t1.id = t2.id + 5;

Cuidados com frames grandes: Quando usar ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, o banco precisa manter todas as linhas da partição em memória. Para partições muito grandes, considere se realmente precisa do valor da última linha ou se pode usar outras abordagens.

Referências