Uma função de janela calcula um valor para cada linha a partir de um conjunto de linhas relacionadas, a janela dela, sem juntar as linhas como o GROUP BY faz. Escreva function() OVER (PARTITION BY ... ORDER BY ...). PARTITION BY divide as linhas em grupos e ORDER BY define a ordem dentro de cada grupo.
Todas as linhas continuam no resultado e levam o total da região: 1000 para north, 500 para south. A fatia de cada vendedor vira então uma única expressão (ana tem 50 por cento de north). Qualquer agregação (sum, count, avg, min, max) vira função de janela quando você adiciona OVER. Um OVER () vazio transforma o resultado inteiro numa única janela.
Numerar linhas com ROW_NUMBER, RANK e DENSE_RANK
As três funções de ranking só diferem quando as linhas empatam no valor do ORDER BY:
ben e cy têm 85. row_number ainda dá 2 e 3 a eles; qual recebe o 2 não é definido, a menos que o ORDER BY desempate. rank dá 2 aos dois e pula para 4 no dee. dense_rank dá 2 aos dois e continua com 3.
| Função | Empates | Buracos depois de empates |
|---|---|---|
row_number() | números diferentes | não |
rank() | o mesmo número | sim |
dense_rank() | o mesmo número | não |
Pegar as N primeiras linhas de cada grupo
Funções de janela são avaliadas depois do WHERE, então você não pode filtrar por elas diretamente. Numere as linhas numa subconsulta ou CTE e filtre do lado de fora:
O resultado traz os dois melhores vendedores de cada região. Use rank() em vez de row_number() para manter todas as linhas que empatam no segundo lugar. Para só a primeira linha de cada grupo, DISTINCT ON é mais curto.
Calcular um total acumulado ou uma média móvel
Com ORDER BY dentro do OVER, uma agregação roda sobre um frame. Por padrão, o frame vai da primeira linha da partição até a linha atual, o que dá um total acumulado. Uma cláusula ROWS BETWEEN define outro frame, como a linha atual e as duas anteriores:
O total acumulado chega a 130 no dia 5, e a média de três dias no dia 5 é (30 + 20 + 50) / 3 = 33.3.
O frame padrão é RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Ele inclui todas as linhas que empatam com a atual no valor do ORDER BY, então duas linhas com o mesmo dia recebem o mesmo total acumulado. Escreva ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW para somá-las uma de cada vez.
Comparar com a linha anterior: LAG e LEAD
lag(col) lê um valor da linha antes da atual na ordem da janela, e lead(col) da linha depois. As duas retornam NULL além da borda, a menos que você passe um valor padrão como terceiro argumento.
O dia 1 não tem linha anterior, então previous e change são NULL. A cláusula WINDOW define uma janela uma vez, com nome, para várias funções compartilharem.
first_value funciona com o frame padrão, mas last_value não: o frame padrão termina na linha atual, então ele retorna o valor da linha atual. Adicione ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING para o frame ser a partição inteira. Outras funções exclusivas de janela são ntile(n), percent_rank(), cume_dist() e nth_value(); a referência de funções de janela do PostgreSQL lista todas.
Filtrar quais linhas uma agregação conta
FILTER (WHERE ...) também funciona em agregações de janela, para totais condicionais em cada linha:
SELECT day,
count(*) FILTER (WHERE status = 'error') OVER (ORDER BY day) AS errors_so_far
FROM requests;
Perguntas frequentes
Qual a diferença entre GROUP BY e PARTITION BY?
GROUP BY retorna uma linha por grupo. PARTITION BY dentro do OVER mantém todas as linhas e dá a cada uma um valor calculado sobre o grupo dela, então você pode mostrar o valor de uma linha ao lado do total do grupo.
Posso usar uma função de janela no WHERE?
Não. Funções de janela rodam depois de WHERE, GROUP BY e HAVING, então só são permitidas na lista do select e no ORDER BY. Calcule o valor numa subconsulta ou CTE e filtre na consulta de fora.
Por que o LAST_VALUE retorna o valor da linha atual?
Com ORDER BY no OVER, o frame padrão termina na linha atual (e nos empates dela), então a última linha do frame é a atual. Adicione ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING para o frame ser a partição inteira.
Qual a diferença entre ROWS e RANGE num frame?
ROWS conta linhas físicas: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW é a linha atual e as duas anteriores. RANGE trabalha com o valor do ORDER BY, então linhas com valores iguais (peers) sempre entram juntas no frame.