How to usar window functions para resolver em uma query

Window functions resolvem em uma unica query problemas que antes exigiam subconsultas ou joins complicados. Elas calculam valores baseados em um conjunto de linhas relacionadas sem agrupar o resultado.

A sintaxe basica com ROW_NUMBER:

SELECT 
    nome,
    departamento,
    salario,
    ROW_NUMBER() OVER (PARTITION BY departamento ORDER BY salario DESC) as posicao
FROM funcionarios;

Isso numero cada funcionario dentro do seu departamento, ordenado por salario. O PARTITION BY divide as linhas em grupos. O ORDER BY define a ordem dentro de cada grupo.

Para pegar os 3 maiores salarios de cada departamento:

WITH ranked AS (
    SELECT 
        nome,
        departamento,
        salario,
        ROW_NUMBER() OVER (PARTITION BY departamento ORDER BY salario DESC) as pos
    FROM funcionarios
)
SELECT * FROM ranked WHERE pos <= 3;

RANK e DENSE_RANK sao variantes que lidam com empates:

SELECT 
    nome,
    departamento,
    salario,
    RANK() OVER (ORDER BY salario DESC) as rank,
    DENSE_RANK() OVER (ORDER BY salario DESC) as dense_rank
FROM funcionarios;

RANK pula numeros em caso de empate (1, 1, 3). DENSE_RANK nao pula (1, 1, 2).

Para calcular totais acumulados:

SELECT 
    data,
    valor,
    SUM(valor) OVER (ORDER BY data) as acumulado
FROM vendas
ORDER BY data;

A SUM com OVER sem PARTITION BY calcula o total acumulado desde o inicio.

Para comparar um valor com a linha anterior:

SELECT 
    data,
    valor,
    LAG(valor, 1) OVER (ORDER BY data) as valor_anterior,
    valor - LAG(valor, 1) OVER (ORDER BY data) as diferenca
FROM vendas
ORDER BY data;

LAG olha para tras. LEAD olha para frente. O segundo parametro e quantas linhas pular.

Para media movel de 7 dias:

SELECT 
    data,
    valor,
    AVG(valor) OVER (ORDER BY data ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as media_movel_7d
FROM vendas;

O ROWS BETWEEN define a janela. 6 PRECEDING pega as 6 linhas anteriores. CURRENT ROW e a linha atual.

Para percentual de cada venda dentro do total:

SELECT 
    produto,
    valor,
    valor / SUM(valor) OVER () * 100 as percentual
FROM vendas;

Sem PARTITION BY, a SUM considera todas as linhas.

Window functions sao mais performaticas que subconsultas equivalentes. O banco processa tudo em um unico scan dos dados.

That's all for now.
Thanks for reading!