Funções de janela

O problema que resolvem

GROUP BY colapsa muitas linhas em uma só por grupo: você perde o detalhe. As funções de janela calculam um valor agregado mantendo todas as linhas. Cada linha vê uma "janela" de linhas relacionadas e obtém seu resultado.

A peça-chave é a cláusula OVER (...):

SELECT
  nome,
  categoria,
  preco,
  AVG(preco) OVER (PARTITION BY categoria) AS media_categoria
FROM produtos;

Aqui cada linha mantém seu nome e preco, e além disso mostra a média de sua categoria. Com GROUP BY categoria você teria apenas uma linha por categoria.

PARTITION BY e ORDER BY dentro de OVER

Dentro do OVER você pode dividir e ordenar a janela:

• PARTITION BY col → divide as linhas em grupos independentes. A função reinicia em cada grupo. É como um "GROUP BY que não colapsa".
• ORDER BY col → ordena as linhas dentro da janela. É imprescindível para rankings e totais acumulados, porque definem "a ordem" ou "o anterior".

Numerar e rankear

SELECT nome, categoria, preco,
  ROW_NUMBER() OVER (PARTITION BY categoria ORDER BY preco DESC) AS pos,
  RANK()       OVER (PARTITION BY categoria ORDER BY preco DESC) AS rk
FROM produtos;

Totais acumulados (running totals)

Combinando um agregado com OVER (ORDER BY ...) você obtém um total acumulado: cada linha soma desde a primeira até ela mesma.

SELECT dia, valor,
  SUM(valor) OVER (ORDER BY id) AS acumulado
FROM vendas;

As funções de janela são calculadas depois de WHERE/GROUP BY, por isso você não pode usá-las em um WHERE. Se precisar filtrar pelo resultado delas, envolva a consulta em uma subconsulta ou em uma CTE.

Exemplos

Ranking de preços dentro de cada categoria

SELECT nome, categoria, preco,
  RANK() OVER (PARTITION BY categoria ORDER BY preco DESC) AS rk
FROM produtos
ORDER BY categoria, rk;

Total acumulado de vendas por ordem de registro

SELECT id, dia, valor,
  SUM(valor) OVER (ORDER BY id) AS acumulado
FROM vendas
ORDER BY id;