PARTITION BY e OVER nas window functions

1. Introdução às Window Functions e a Cláusula OVER

Window functions são funções especiais do SQL que realizam cálculos sobre um conjunto de linhas relacionadas à linha atual, sem agrupar os resultados em uma única saída. Diferentemente das funções de agregação tradicionais como SUM() ou AVG() com GROUP BY, que reduzem múltiplas linhas a uma única linha por grupo, as window functions preservam todas as linhas originais e adicionam uma coluna calculada.

A estrutura básica de uma window function segue este padrão:

função_janela() OVER (
    [PARTITION BY coluna1, coluna2, ...]
    [ORDER BY coluna1 ASC/DESC]
    [frame_specification]
)

A cláusula OVER é o coração da window function. Ela define o "conjunto da janela" — o subconjunto de linhas sobre o qual a função irá operar. Sem OVER, a função se comportaria como uma agregação comum.

2. Sintaxe e Componentes do OVER

A cláusula OVER possui três componentes principais:

  • PARTITION BY: Divide os dados em partições lógicas. Cada partição é processada independentemente.
  • ORDER BY: Define a ordem das linhas dentro de cada partição. Essencial para funções como RANK() e ROW_NUMBER().
  • Frame Specification: Define quais linhas dentro da partição são incluídas no cálculo, usando ROWS, RANGE ou GROUPS.

Exemplo de estrutura completa:

SELECT 
    nome,
    departamento,
    salario,
    AVG(salario) OVER (
        PARTITION BY departamento 
        ORDER BY salario DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as media_acumulada
FROM funcionarios;

3. PARTITION BY: Dividindo Dados em Grupos Lógicos

PARTITION BY cria subconjuntos independentes para o cálculo. Cada partição é tratada como um grupo separado, e a função é aplicada dentro de cada partição individualmente.

A diferença crucial entre PARTITION BY e GROUP BY é que PARTITION BY preserva todas as linhas originais, enquanto GROUP BY as agrega em uma única linha por grupo.

Exemplo prático: calcular a média salarial por departamento sem perder os detalhes de cada funcionário.

SELECT 
    nome,
    departamento,
    salario,
    AVG(salario) OVER (PARTITION BY departamento) as media_departamento,
    salario - AVG(salario) OVER (PARTITION BY departamento) as diferenca_media
FROM funcionarios;

Resultado: cada funcionário mantém sua linha, mas agora sabemos quanto ele ganha em relação à média do seu departamento.

4. Combinação de ORDER BY com PARTITION BY

Quando combinamos ORDER BY com PARTITION BY, a ordenação ocorre dentro de cada partição. Isso é essencial para funções como ROW_NUMBER(), RANK() e DENSE_RANK().

Exemplo: ranking de vendas por região, ordenado por valor decrescente.

SELECT 
    vendedor,
    regiao,
    valor_venda,
    ROW_NUMBER() OVER (PARTITION BY regiao ORDER BY valor_venda DESC) as posicao_regiao,
    RANK() OVER (PARTITION BY regiao ORDER BY valor_venda DESC) as ranking_com_empates
FROM vendas;

Para funções cumulativas como SUM() e AVG(), a ordenação define a direção do acúmulo:

SELECT 
    data_venda,
    valor,
    SUM(valor) OVER (ORDER BY data_venda) as total_acumulado
FROM vendas_diarias;

5. Frame Specification: Controlando o Escopo da Janela

A frame specification define precisamente quais linhas dentro da partição são incluídas no cálculo. As principais opções são:

  • ROWS: Baseado em linhas físicas (posição exata).
  • RANGE: Baseado em valores lógicos (diferença de valores).
  • GROUPS: Baseado em grupos de valores iguais.

Exemplo de total acumulado até a linha atual:

SELECT 
    mes,
    receita,
    SUM(receita) OVER (
        ORDER BY mes
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as receita_acumulada
FROM receitas_mensais;

Para janelas temporais com RANGE:

SELECT 
    data_venda,
    valor,
    SUM(valor) OVER (
        ORDER BY data_venda
        RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
    ) as total_ultimos_7_dias
FROM vendas;

A diferença entre ROWS e RANGE é crítica: ROWS conta linhas exatas, enquanto RANGE considera linhas com valores dentro do intervalo especificado.

6. Exemplos Práticos com Funções Comuns

ROW_NUMBER() com PARTITION BY para numerar linhas por grupo:

SELECT 
    cliente_id,
    data_pedido,
    valor,
    ROW_NUMBER() OVER (PARTITION BY cliente_id ORDER BY data_pedido DESC) as pedido_recente
FROM pedidos;

SUM() OVER com PARTITION BY e ORDER BY para subtotais cumulativos:

SELECT 
    categoria,
    produto,
    quantidade,
    SUM(quantidade) OVER (
        PARTITION BY categoria 
        ORDER BY produto
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as estoque_acumulado_categoria
FROM inventario;

LAG() e LEAD() para comparar valores entre linhas consecutivas:

SELECT 
    funcionario,
    mes,
    salario,
    LAG(salario, 1, 0) OVER (PARTITION BY funcionario ORDER BY mes) as salario_mes_anterior,
    LEAD(salario, 1, 0) OVER (PARTITION BY funcionario ORDER BY mes) as salario_proximo_mes
FROM historico_salarios;

7. Diferenças entre PARTITION BY e GROUP BY

A principal diferença: GROUP BY agrega e reduz o número de linhas; PARTITION BY calcula sem reduzir.

GROUP BY — relatório resumido:

SELECT 
    categoria,
    SUM(valor) as total_vendas
FROM vendas
GROUP BY categoria;

PARTITION BY — análise detalhada com coluna calculada:

SELECT 
    venda_id,
    categoria,
    valor,
    SUM(valor) OVER (PARTITION BY categoria) as total_categoria,
    ROUND(valor * 100.0 / SUM(valor) OVER (PARTITION BY categoria), 2) as percentual_categoria
FROM vendas;

Use GROUP BY quando precisar de um resumo. Use PARTITION BY quando precisar de detalhes com métricas calculadas ao lado.

8. Boas Práticas e Performance

Indexação: Para otimizar consultas com PARTITION BY, crie índices compostos nas colunas usadas na partição e ordenação:

CREATE INDEX idx_vendas_regiao_data ON vendas(regiao, data_venda);

Cuidados com frames grandes: Frames muito amplos (como ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) podem consumir muita memória. Prefira frames mais restritivos sempre que possível.

Alternativas: Subconsultas correlacionadas podem substituir window functions, mas geralmente são menos eficientes e mais difíceis de ler:

-- Com window function (recomendado)
SELECT *, AVG(salario) OVER (PARTITION BY departamento) as media
FROM funcionarios;

-- Com subconsulta (menos eficiente)
SELECT *, 
    (SELECT AVG(salario) FROM funcionarios f2 
     WHERE f2.departamento = f1.departamento) as media
FROM funcionarios f1;

Performance: Window functions são processadas após as cláusulas WHERE, GROUP BY e HAVING, mas antes de ORDER BY. Planeje suas consultas considerando essa ordem de execução.

As window functions com PARTITION BY e OVER são ferramentas poderosas que transformam análises SQL, permitindo cálculos sofisticados sem sacrificar a integridade dos dados originais. Dominá-las eleva significativamente a capacidade de gerar insights a partir de bancos de dados relacionais.

Referências