PG Módulo 7: Window functions

Antes de começar

GROUP BY colapsa o grupo numa linha. Window function faz uma conta olhando um conjunto de linhas (a "janela") mas mantém cada linha no resultado. É como ter o total do grupo ao lado de cada item.

função() OVER (PARTITION BY x ORDER BY y): a janela. PARTITION BY divide em grupos; ORDER BY ordena dentro de cada grupo (importa pra ranking e acumulado). Os dois são opcionais.
Ranking: ROW_NUMBER() (1,2,3… sem empate), RANK() (empate ocupa a posição e pula: 1,1,3), DENSE_RANK() (1,1,2).
Acumulado: SUM(x) OVER (ORDER BY y): soma correndo linha a linha.
Vizinhos: LAG(x) / LEAD(x): valor da linha anterior / seguinte (dentro da partição).
Top-N por grupo: numere com ROW_NUMBER() OVER (PARTITION BY g ORDER BY v DESC) numa subconsulta e filtre WHERE rn <= 3 por fora (não dá pra filtrar window function no WHERE direto).

Dois exemplos

Teoria

COUNT(*) OVER (): total geral em toda linha. AVG(x) OVER (PARTITION BY g): média do grupo ao lado de cada linha.
ROW_NUMBER() OVER (ORDER BY x DESC): posição no ranking.
SUM(x) OVER (ORDER BY d ROWS UNBOUNDED PRECEDING): acumulado (o ROWS… é o default quando há ORDER BY).
LAG(x, 1) OVER (ORDER BY d): valor anterior; x - LAG(x) OVER (…) = variação.
Window function não pode ir no WHERE / HAVING: embrulhe numa subconsulta/CTE e filtre lá fora.

Exemplos de aplicação

Um exemplo montado pra cada item da teoria acima. Não precisa rodar, é pra ver a cara do recurso e entender cada pedaço. Os valores são ilustrativos.

`OVER ()` e `OVER (PARTITION BY ...)`

SELECT
  id,
  valor_total,
  ROUND(AVG(valor_total) OVER (), 2)                    AS media_geral,
  ROUND(AVG(valor_total) OVER (PARTITION BY status), 2) AS media_do_status
FROM pedidos;

Window function calcula um agregado sem colapsar as linhas: cada pedido continua aparecendo, com a média ao lado. OVER () = sobre a tabela toda; OVER (PARTITION BY status) = sobre os pedidos do mesmo status. Dá pra comparar cada linha com a média dela.

`ROW_NUMBER() OVER (ORDER BY ...)`

SELECT
  id,
  valor_total,
  ROW_NUMBER() OVER (ORDER BY valor_total DESC) AS posicao
FROM pedidos;

Numera as linhas na ordem que você pedir: aqui, 1 pro pedido mais caro, 2 pro segundo, e assim por diante. Com PARTITION BY cliente_id junto, o ranking reinicia a cada cliente ("o maior pedido de cada um").

Acumulado: `SUM(...) OVER (ORDER BY ...)`

SELECT
  data_pedido,
  valor_total,
  SUM(valor_total) OVER (ORDER BY data_pedido) AS acumulado
FROM pedidos
WHERE status <> 'cancelado';

Com ORDER BY dentro do OVER, a soma vira acumulada: cada linha traz o total dela mais o de todas as anteriores na ordem de data. É assim que se faz "faturamento acumulado no tempo".

`LAG(...)`: comparar com a linha anterior

SELECT
  mes,
  total,
  total - LAG(total) OVER (ORDER BY mes) AS variacao
FROM (
  SELECT DATE_TRUNC('month', data_pedido)::date AS mes,
         SUM(valor_total) AS total
  FROM pedidos
  GROUP BY 1
) m;

LAG(total) OVER (ORDER BY mes) traz o total do mês anterior pra mesma linha; a subtração dá a variação mês a mês. No primeiro mês não há anterior, então LAG vem NULL e a variação também.

Window function não vai no `WHERE`

SELECT *
FROM (
  SELECT id, valor_total,
         ROW_NUMBER() OVER (ORDER BY valor_total DESC) AS pos
  FROM pedidos
) ranqueado
WHERE pos <= 5;

O WHERE/HAVING roda antes das window functions, então WHERE pos <= 5 na mesma query daria erro. A saída é embrulhar numa subconsulta (ou CTE) e filtrar do lado de fora: aqui, "os 5 maiores pedidos".

Pegadinhas deste módulo

Os erros que quase todo mundo comete aqui. Ler agora poupa meia hora de confusão depois, e alguns deles são a causa de relatório errado em projeto real.

UPDATE ou DELETE sem WHERE atinge a tabela inteira. E não pede confirmação. Hábito que salva: escreva primeiro como SELECT com o mesmo WHERE, confira quais linhas voltam, e só então troque o começo por UPDATE ou DELETE.
Sem BEGIN, cada comando já está confirmado. Na maioria das ferramentas não existe "desfazer". Em dado que importa, abra a transação antes, confira o resultado, e só então COMMIT ou ROLLBACK.
Conte antes de escrever. SELECT COUNT(*) com o mesmo WHERE diz quantas linhas você está prestes a mudar. Se o número surpreender, pare.
INSERT sem nomear as colunas quebra quando a tabela muda. INSERT INTO t VALUES (...) depende da ordem das colunas. Nomeie sempre: INSERT INTO t (a, b) VALUES (...).
Massa de teste criada por você também precisa ser limpa. Dado de teste esquecido em ambiente compartilhado vira o "bug" que outra pessoa vai investigar na semana que vem.

Prática

Escreva a query e clique Rodar (ou Ctrl+Enter).

carregando banco…

Query livre

Olhar de QA

Window functions respondem perguntas que agregação simples não alcança: "qual a posição deste pedido no histórico do cliente?", "este defeito demorou mais que os anteriores do mesmo módulo?", "quanto tempo passou entre uma execução e a seguinte do mesmo caso de teste?". É a ferramenta de quem analisa tendência, e não só o retrato de hoje, e tendência é o que se leva para uma reunião de qualidade.

Módulo 7: Window functions