Lição 7.4 · Tempo de leitura: ~9 minutos

Esta lição apresenta as funções de janela LAG, LEAD, FIRST_VALUE e LAST_VALUE. Você vai aprender como obter valores anteriores e seguintes sem JOIN, como encontrar o primeiro e o último valor dentro de uma janela, e por que LAST_VALUE muitas vezes precisa de um frame explícito. Ao final da lição, você conseguirá usar essas funções com confiança para comparar linhas, analisar tendências e montar relatórios analíticos.

LAG, LEAD, FIRST_VALUE e LAST_VALUE

Na lição anterior, estudamos frames de janela e vimos como seus limites afetam os cálculos. Agora vamos para as funções que permitem olhar para trás, para frente e para os valores extremos dentro da janela.

Essas funções são especialmente úteis em análise: ajudam a comparar vendas por dia, identificar a ação anterior de um cliente, calcular variações em relação a um valor anterior e encontrar o primeiro ou o último registro de um grupo sem self join.

O que essas funções fazem

As quatro funções são funções de janela e usam OVER (...).

• LAG retorna um valor de uma linha anterior na janela.
• LEAD retorna um valor de uma linha seguinte na janela.
• FIRST_VALUE retorna o primeiro valor na janela atual.
• LAST_VALUE retorna o último valor na janela atual.

A ideia principal é simples: a linha atual permanece no lugar, mas ganha acesso a valores de outras linhas na mesma partição.

Sintaxe básica

LAG e LEAD

LAG(expression [, offset [, default_value]]) OVER (
    [PARTITION BY ...]
    ORDER BY ...
)

LEAD(expression [, offset [, default_value]]) OVER (
    [PARTITION BY ...]
    ORDER BY ...
)

• expression é o valor que você quer obter de outra linha.
• offset define quantas linhas retroceder ou avançar.
• default_value define o que retornar se essa linha não existir.

FIRST_VALUE e LAST_VALUE

FIRST_VALUE(expression) OVER (
    [PARTITION BY ...]
    ORDER BY ...
    [frame_clause]
)

LAST_VALUE(expression) OVER (
    [PARTITION BY ...]
    ORDER BY ...
    [frame_clause]
)

Para FIRST_VALUE e principalmente LAST_VALUE, o frame da janela importa. Sem um frame explícito, LAST_VALUE frequentemente produz um resultado diferente do que iniciantes esperam.

Usando LAG

LAG é útil quando você precisa comparar a linha atual com a anterior.

Pagamento anterior de um cliente

SELECT
    customer_id,
    payment_date,
    amount,
    LAG(amount) OVER (
        PARTITION BY customer_id
        ORDER BY payment_date
    ) AS previous_amount
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

Resultado: cada linha mostra o pagamento atual e o valor do pagamento anterior do mesmo cliente.

Diferença em relação ao pagamento anterior

SELECT
    customer_id,
    payment_date,
    amount,
    amount - LAG(amount, 1, 0) OVER (
        PARTITION BY customer_id
        ORDER BY payment_date
    ) AS amount_diff
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

Resultado: você pode ver quanto o pagamento atual difere do anterior. Para a primeira linha, 0 é usado como valor padrão.

Usando LEAD

LEAD funciona de forma simétrica, mas olha para frente.

Próximo pagamento de um cliente

SELECT
    customer_id,
    payment_date,
    amount,
    LEAD(amount) OVER (
        PARTITION BY customer_id
        ORDER BY payment_date
    ) AS next_amount
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

Resultado: cada linha mostra o valor do próximo pagamento desse cliente.

Próxima data de locação

SELECT
    customer_id,
    rental_date,
    LEAD(rental_date) OVER (
        PARTITION BY customer_id
        ORDER BY rental_date
    ) AS next_rental_date
FROM rental
WHERE customer_id = 1
ORDER BY rental_date;

Resultado: a consulta mostra quando o mesmo cliente fará a locação seguinte.

Usando FIRST_VALUE

FIRST_VALUE retorna o primeiro valor da janela. Isso é útil quando você quer comparar a linha atual com um ponto inicial.

Primeiro pagamento de um cliente

SELECT
    customer_id,
    payment_date,
    amount,
    FIRST_VALUE(amount) OVER (
        PARTITION BY customer_id
        ORDER BY payment_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_payment_amount
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

Resultado: o valor do primeiro pagamento do cliente se repete em cada linha da janela.

Comparando o pagamento atual com o primeiro

SELECT
    customer_id,
    payment_date,
    amount,
    amount - FIRST_VALUE(amount) OVER (
        PARTITION BY customer_id
        ORDER BY payment_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS diff_from_first
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

Resultado: isso ajuda a medir o quanto os valores atuais se afastam do primeiro valor da sequência.

Usando LAST_VALUE

LAST_VALUE parece simples, mas é aqui que as expectativas costumam falhar.

Nuance importante: o frame padrão

Se você escrever isto:

SELECT
    customer_id,
    payment_date,
    amount,
    LAST_VALUE(amount) OVER (
        PARTITION BY customer_id
        ORDER BY payment_date
    ) AS last_amount_default
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

em muitos SGBDs o resultado não será o último valor da partição inteira, mas o valor no final do frame atual. Em muitos casos, isso significa a própria linha atual.

Versão correta para o último valor da partição

SELECT
    customer_id,
    payment_date,
    amount,
    LAST_VALUE(amount) OVER (
        PARTITION BY customer_id
        ORDER BY payment_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_payment_amount
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

Resultado: agora cada linha consegue ver o valor do último pagamento do cliente em toda a partição.

Por que isso e útil

Esse padrão é conveniente quando você precisa comparar um valor atual com o último valor conhecido de uma série, o status final de um pedido ou o último pagamento feito por um cliente.

Comparando LAG, LEAD, FIRST_VALUE e LAST_VALUE

Se a tarefa envolve comparar linhas vizinhas, LAG e LEAD costumam ser as melhores escolhas. Se você precisa de um ponto de referência no início ou no fim da janela, use FIRST_VALUE e LAST_VALUE.

Exemplo prático: receita por dia e comparação com dias vizinhos

Primeiro, agregamos os pagamentos por dia e depois aplicamos funções de janela ao resultado agregado:

SELECT
    pay_day,
    daily_total,
    LAG(daily_total) OVER (ORDER BY pay_day) AS previous_day_total,
    LEAD(daily_total) OVER (ORDER BY pay_day) AS next_day_total,
    FIRST_VALUE(daily_total) OVER (
        ORDER BY pay_day
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_day_total,
    LAST_VALUE(daily_total) OVER (
        ORDER BY pay_day
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_day_total
FROM (
    SELECT
        DATE(payment_date) AS pay_day,
        SUM(amount) AS daily_total
    FROM payment
    GROUP BY DATE(payment_date)
) AS daily_stats
ORDER BY pay_day;

Resultado: cada data passa a ter acesso à receita do dia anterior, do dia seguinte e também aos primeiros e últimos valores de toda a sequência.

Esse é um bom modelo para análise de séries temporais, preparação de dashboards e identificação de desvios de tendência.

Perguntas Frequentes

Qual e a diferença entre LAG e LEAD?

LAG olha para trás e retorna um valor de uma linha anterior, enquanto LEAD olha para frente e retorna um valor de uma linha seguinte. Ambas funcionam dentro da janela definida e da ordenação especificada.

Por que LAST_VALUE muitas vezes retorna a linha atual?

Porque o resultado depende do frame da janela. Se você mantiver o frame padrão, a última linha do frame pode ser a linha atual. Para obter o último valor de toda a partição, geralmente é necessário escrever ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Posso usar LAG e LEAD sem PARTITION BY?

Sim. Nesse caso, a função trabalha sobre todo o conjunto de resultados como se fosse uma única partição. Isso é útil quando você precisa analisar uma sequência geral sem separá-la em grupos.

Perguntas de Entrevista

Quando devo usar LAG e quando devo usar LEAD?

Use LAG quando precisar comparar a linha atual com a anterior, por exemplo para encontrar a variação em relação ao pagamento anterior. Use LEAD quando precisar olhar para frente, por exemplo para obter a próxima data de evento ou o próximo valor de uma métrica.

Como FIRST_VALUE difere de MIN?

MIN retorna o menor valor em um conjunto de linhas, enquanto FIRST_VALUE retorna o valor da primeira linha de acordo com a ordenação definida. Se a ordenação não coincidir com o menor valor, os resultados serão diferentes.

Por que LAST_VALUE frequentemente exige um frame explícito?

Porque LAST_VALUE não significa "a última linha da partição em qualquer caso". Significa a última linha do frame atual. Se o frame padrão termina na linha atual, a função retorna o valor atual. Um frame explícito amplia a janela para toda a partição.

Principais conclusões desta lição:

• LAG e LEAD permitem acessar linhas vizinhas sem self join.
• FIRST_VALUE e LAST_VALUE retornam valores extremos dentro da janela, e não apenas o mínimo ou o máximo.
• Para todas essas funções, a cláusula ORDER BY é crítica porque define a sequência das linhas.
• LAST_VALUE frequentemente exige o frame explícito UNBOUNDED PRECEDING ... UNBOUNDED FOLLOWING.
• Essas funções são especialmente úteis para análise de sequências, séries temporais e detecção de mudanças entre linhas.