Funções de janela: agregações sem colapsar linhas

O que é

O tutorial do PostgreSQL define uma função de janela como uma função que realiza um cálculo sobre um conjunto de linhas da tabela que, de alguma forma, estão relacionadas à linha atual. É comparável a uma agregação, mas sem reunir as linhas numa única linha de saída. A cláusula OVER define a janela: PARTITION BY divide as linhas em grupos, ORDER BY ordena as linhas dentro de cada partição e um frame opcional (ROWS, RANGE ou GROUPS BETWEEN ... AND ...) limita quais linhas da partição a função enxerga. A página de referência lista row_number, rank, dense_rank, percent_rank, ntile, lag, lead, first_value, last_value e nth_value; qualquer agregação, como sum ou avg, também pode ser usada com OVER.

Por que importa

Sem funções de janela, pedidos como "o salário de cada funcionário ao lado da média do departamento", "os três pedidos mais recentes por cliente" ou "um saldo acumulado" exigem self-joins ou subconsultas correlacionadas, que são mais difíceis de ler. Com uma função de janela, cada um deles é expresso diretamente na lista do SELECT.

Como aplicar

• Total acumulado: sum(amount) OVER (PARTITION BY account_id ORDER BY created_at, id). Inclua um critério de desempate no ORDER BY: o tutorial afirma que, com ORDER BY, o frame padrão vai do início da partição até a linha atual, mais as linhas seguintes que sejam iguais a ela; por isso, chaves de ordenação iguais são somadas juntas.
• Top-N por grupo: row_number() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn numa subconsulta ou CTE, e depois WHERE rn <= 3 na consulta externa; funções de janela não podem aparecer diretamente no WHERE.
• Comparação com a linha anterior: lag(value) OVER (ORDER BY ts) para calcular diferenças, e lead para o valor seguinte.
• Ranking: rank deixa lacunas após empates, dense_rank não, e row_number atribui os números de forma arbitrária entre empates, a menos que o ORDER BY seja total.
• Para compartilhar uma janela entre várias funções, defina WINDOW w AS (PARTITION BY ... ORDER BY ...) e escreva OVER w.

Armadilhas

As funções de janela são avaliadas depois de WHERE, GROUP BY e HAVING; por isso, filtrar pelo resultado delas exige uma consulta externa. Com o frame padrão, last_value retorna o último par (peer) da linha atual (a própria linha, quando o ORDER BY não tem empates), e não a última linha da partição; a página de referência diz que isso tende a produzir resultados pouco úteis, então defina ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Partições grandes são ordenadas em memória ou em disco; procure os nós WindowAgg e Sort no EXPLAIN.