Funções de janela para dados temporais: LEAD(), LAG()

Agora nossa missão é ir além e aprender a usar funções de janela pra analisar dados temporais. Bora? Espero que tu tenha pego um café, porque isso aqui vai ser massa.

Então, como sempre, primeiro respondendo a pergunta principal: pra que servem funções de janela (LEAD(), LAG())? Imagina que tu tá trabalhando com dados temporais, tipo logs de eventos, horas trabalhadas, séries temporais ou qualquer coisa onde a ordem dos eventos importa.

Por exemplo, tu quer:

• Saber quando rolou o próximo evento depois do atual.
• Calcular a diferença de tempo entre o evento atual e o anterior.
• Ordenar os dados e calcular a diferença entre os registros.

É aí que entram duas funções top: LEAD() e LAG(). Elas deixam tu pegar dados da linha anterior ou da próxima dentro de uma janela definida. É tipo se tu tivesse um livro mágico onde dá pra ver a próxima página sem virar a atual.

LEAD() e LAG(): sintaxe e princípios básicos

As duas funções usam uma sintaxe parecida:

LEAD(column_name, [offset], [default_value]) OVER (PARTITION BY column_name ORDER BY column_name)
LAG(column_name, [offset], [default_value]) OVER (PARTITION BY column_name ORDER BY column_name)

• column_name — a coluna de onde a gente quer pegar os dados.
• offset (opcional) — o deslocamento em relação à linha atual. Por padrão é 1.
• default_value (opcional) — valor que retorna se não existir linha com o deslocamento pedido (tipo quando tu tá na última linha).
• OVER() — aqui tu define a "janela" pra calcular. Normalmente é ORDER BY, às vezes rola PARTITION BY pra separar os dados em grupos.

Exemplo: Só LEAD() e LAG()

Bora criar uma tabela simples events pra brincar:

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name TEXT NOT NULL,
    event_date TIMESTAMP NOT NULL
);

INSERT INTO events (event_name, event_date)
VALUES
    ('Evento A', '2023-10-01 10:00:00'),
    ('Evento B', '2023-10-01 11:00:00'),
    ('Evento C', '2023-10-01 12:00:00'),
    ('Evento D', '2023-10-01 13:00:00');

Agora a gente quer ver quando rolaram os eventos anteriores e os próximos em relação a cada evento:

SELECT
    id,
    event_name,
    event_date,
    LAG(event_date) OVER (ORDER BY event_date) AS evento_anterior,
    LEAD(event_date) OVER (ORDER BY event_date) AS proximo_evento
FROM events;

O resultado vai ser assim:

Aqui o LAG() pega o dado da linha anterior e o LEAD() da próxima. O primeiro evento não tem pra onde olhar pra trás, e o último não tem ninguém pra frente, então eles recebem NULL.

Exemplo: diferença entre eventos

Às vezes a gente quer saber quanto tempo passou entre os eventos. Pra isso, é só subtrair um horário do outro:

SELECT
    id,
    event_name,
    event_date,
    event_date - LAG(event_date) OVER (ORDER BY event_date) AS tempo_desde_ultimo_evento
FROM events;

O resultado:

Exemplo: usando PARTITION BY

Imagina que temos vários usuários, cada um com seus eventos. A gente quer achar a diferença entre eventos pra cada usuário.

Vamos atualizar a tabela e adicionar a coluna user_id:

ALTER TABLE events ADD COLUMN user_id INT;

UPDATE events SET user_id = 1 WHERE id <= 2;
UPDATE events SET user_id = 2 WHERE id > 2;

Agora temos dois usuários. Bora usar PARTITION BY pra calcular dentro de cada grupo:

SELECT
    user_id,
    event_name,
    event_date,
    event_date - LAG(event_date) OVER (PARTITION BY user_id ORDER BY event_date) AS tempo_desde_ultimo_evento
FROM events;

O resultado:

Exemplos de uso em tarefas reais

1. Logs de eventos: analisar o tempo entre eventos, tipo login e logout do usuário.
2. Time tracking: calcular o tempo gasto em certas tarefas.
3. Análise de comportamento: analisar a sequência de ações dos clientes numa loja online.
4. Cálculo de métricas acumuladas: usar funções de janela pra trabalhar com séries temporais.

Erros comuns

Quando tu mexe com LEAD() e LAG(), os problemas mais comuns são:

• Esquecer o ORDER BY no OVER(). Sem ele, a função não sabe a ordem das linhas.
• Problemas com intervalos de tempo ou tipos de dados (TIMESTAMP vs DATE).
• Ignorar valores NULL que podem aparecer no começo e no fim da janela.

Pra evitar esses vacilos, sempre confere teus dados e garante que tu definiu a janela certa pras operações.