Window functions: ROW_NUMBER, RANK, LAG e LEAD

GROUP BY (lição 4) resolve "quantos pedidos por usuário", mas colapsa as linhas — você perde o detalhe de cada uma. Se você precisa saber a posição de cada produto dentro da categoria dele, sem perder a linha de cada produto individual, GROUP BY sozinho não serve. É pra isso que existem as window functions.

A diferença fundamental: GROUP BY colapsa, window function mantém

SELECT
  produtos.categoria,
  produtos.nome,
  SUM(itens_pedido.quantidade) AS total_vendido,
  ROW_NUMBER() OVER (
    PARTITION BY produtos.categoria
    ORDER BY SUM(itens_pedido.quantidade) DESC
  ) AS posicao
FROM produtos
JOIN itens_pedido ON itens_pedido.produto_id = produtos.id
GROUP BY produtos.categoria, produtos.nome
ORDER BY produtos.categoria, posicao;

GROUP BY produtos.categoria, produtos.nome ainda agrupa pra somar a quantidade vendida — isso não muda. A diferença é o OVER (...): em vez de colapsar tudo numa linha por categoria, ROW_NUMBER() roda sobre uma janela de linhas (PARTITION BY categoria define a janela) e adiciona uma coluna nova — posicao — sem remover nenhuma linha do resultado.

PARTITION BY — reinicia a contagem por grupo

Sem PARTITION BY, ROW_NUMBER() numeraria todas as linhas em sequência, ignorando categoria. Com PARTITION BY produtos.categoria, a numeração reinicia do 1 toda vez que a categoria muda — exatamente como um GROUP BY separaria os grupos, mas sem colapsar as linhas.

RANK e DENSE_RANK — como cada um trata empate

total_vendido | ROW_NUMBER() | RANK() | DENSE_RANK()
--------------|--------------|--------|-------------
320           | 1            | 1      | 1
210           | 2            | 2      | 2
210           | 3            | 2      | 2
95            | 4            | 4      | 3

ROW_NUMBER() nunca repete posição, mesmo em empate — sempre sequencial. RANK() repete a posição pros empatados e pula a posição seguinte. DENSE_RANK() também repete a posição pros empatados, mas não pula nada depois.

LAG e LEAD — comparar uma linha com a vizinha

SELECT
  DATE_TRUNC('month', pedidos.criado_em) AS mes,
  SUM(itens_pedido.quantidade * itens_pedido.preco_unitario) AS receita,
  LAG(SUM(itens_pedido.quantidade * itens_pedido.preco_unitario)) OVER (
    ORDER BY DATE_TRUNC('month', pedidos.criado_em)
  ) AS receita_mes_anterior
FROM pedidos
JOIN itens_pedido ON itens_pedido.pedido_id = pedidos.id
GROUP BY mes
ORDER BY mes;

LAG(...) busca o valor da linha anterior dentro da janela ordenada; LEAD(...) faz o mesmo pra linha seguinte. Sem window function, comparar "este mês vs. mês passado" exigiria um JOIN da tabela com ela mesma — LAG resolve numa linha só.