Window functions: ROW_NUMBER, RANK, DENSE_RANK

1. Introdução às Funções de Ranqueamento em Janela

1.1. O que são funções de janela (window functions) e seu papel analítico

Funções de janela (window functions) são um recurso avançado de SQL que permite realizar cálculos em 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, COUNT, AVG), as window functions preservam cada linha individual, adicionando uma coluna com o resultado do cálculo sobre uma "janela" definida de dados.

As funções de ranqueamento — ROW_NUMBER, RANK e DENSE_RANK — são um subconjunto essencial das window functions, projetadas para atribuir posições ou números sequenciais a linhas dentro de uma partição de dados.

1.2. Diferença entre funções de agregação e funções de ranqueamento

Enquanto uma função de agregação como MAX(salario) reduz múltiplas linhas a um único valor, uma função de ranqueamento como RANK() OVER (ORDER BY salario DESC) atribui uma posição a cada linha individualmente. Isso permite, por exemplo, listar todos os funcionários com seus respectivos rankings salariais, sem perder nenhum detalhe.

1.3. Visão geral: ROW_NUMBER, RANK, DENSE_RANK – quando usar cada uma

  • ROW_NUMBER: Atribui um número único e sequencial a cada linha, mesmo em caso de empates. Ideal para paginação ou quando cada linha precisa de um identificador exclusivo.
  • RANK: Atribui o mesmo número para valores iguais, mas pula posições após empates. Útil para competições onde o próximo lugar após um empate deve refletir a quantidade de participantes à frente.
  • DENSE_RANK: Similar ao RANK, mas sem pular posições. Os números são consecutivos, mesmo com empates. Perfeito para classificações compactas como "top 3" que incluem empates.

2. Sintaxe Fundamental e a Cláusula OVER

2.1. Estrutura básica: função() OVER (ORDER BY ...)

A sintaxe fundamental para usar funções de ranqueamento é:

função_ranking() OVER (ORDER BY coluna1 [ASC|DESC], coluna2 [ASC|DESC])

O ORDER BY dentro do OVER define a ordem em que os ranks serão atribuídos.

2.2. Particionamento com PARTITION BY para ranqueamento por grupo

O PARTITION BY divide os dados em grupos independentes, reiniciando a numeração em cada partição:

função_ranking() OVER (PARTITION BY departamento ORDER BY salario DESC)

Isso permite, por exemplo, ranquear funcionários dentro de cada departamento separadamente.

2.3. Ordenação (ASC/DESC) e impacto no resultado do ranking

A direção da ordenação é crucial: ORDER BY salario DESC coloca os maiores valores em primeiro lugar (rank 1), enquanto ORDER BY salario ASC faz o oposto. Ordenações compostas podem desempatar registros de forma determinística.

3. ROW_NUMBER: Numeração Sequencial e Exclusiva

3.1. Funcionamento: números únicos e consecutivos para cada linha

ROW_NUMBER() atribui um número inteiro único para cada linha, começando em 1 e incrementando em 1. Mesmo que duas linhas tenham valores idênticos na coluna de ordenação, cada uma receberá um número diferente.

3.2. Exemplo prático: paginação de resultados (pagination)

Considere uma tabela produtos com 100 registros. Para implementar paginação com 10 itens por página:

SELECT * FROM (
    SELECT 
        produto_id,
        nome,
        preco,
        ROW_NUMBER() OVER (ORDER BY produto_id) AS linha_num
    FROM produtos
) AS sub
WHERE linha_num BETWEEN 11 AND 20;

3.3. Comportamento com empates (ties) – ordem arbitrária vs. determinística

Quando há empates na coluna de ordenação, ROW_NUMBER pode atribuir números de forma não determinística (dependendo do SGBD). Para garantir previsibilidade, adicione colunas de desempate no ORDER BY:

ROW_NUMBER() OVER (ORDER BY salario DESC, funcionario_id ASC)

4. RANK: Ranqueamento com Lacunas em Empates

4.1. Lógica de ranqueamento: valores iguais recebem o mesmo rank, próximos pulam posições

RANK() atribui o mesmo número para linhas com valores iguais na ordenação. Quando um empate ocorre, o próximo valor distinto recebe um rank que considera quantos registros vieram antes (incluindo os empatados).

4.2. Exemplo: notas de alunos – como identificar campeões e vice-campeões

SELECT 
    aluno_id,
    nome,
    nota,
    RANK() OVER (ORDER BY nota DESC) AS posicao
FROM alunos;

Se dois alunos têm nota 100 (rank 1), o próximo com nota 95 receberá rank 3, indicando que há dois alunos à sua frente.

4.3. Diferença crucial: lacunas numéricas após empates

As lacunas são intencionais: o rank 3 após um empate duplo no rank 1 informa que aquele aluno é o terceiro melhor, não o segundo. Isso é matematicamente correto para competições.

5. DENSE_RANK: Ranqueamento Contínuo sem Lacunas

5.1. Funcionamento: ranks consecutivos mesmo com empates

DENSE_RANK() funciona como RANK(), mas sem pular números. Após um empate, o próximo valor distinto recebe o rank imediatamente seguinte.

5.2. Exemplo comparativo: DENSE_RANK vs. RANK no mesmo dataset

SELECT 
    produto,
    vendas,
    RANK() OVER (ORDER BY vendas DESC) AS rank_com_lacunas,
    DENSE_RANK() OVER (ORDER BY vendas DESC) AS rank_sem_lacunas
FROM vendas_produtos;

Resultado hipotético:
| Produto | Vendas | rank_com_lacunas | rank_sem_lacunas |
|---------|--------|------------------|------------------|
| A | 100 | 1 | 1 |
| B | 100 | 1 | 1 |
| C | 90 | 3 | 2 |
| D | 80 | 4 | 3 |

5.3. Caso de uso: quando você precisa de uma classificação compacta

Use DENSE_RANK para criar categorias como "Top 3" que incluem todos os empatados. Se cinco produtos vendem 100 unidades, todos recebem rank 1, e o próximo recebe rank 2.

6. Comparação Prática entre as Três Funções

6.1. Tabela comparativa: ROW_NUMBER vs. RANK vs. DENSE_RANK

Característica ROW_NUMBER RANK DENSE_RANK
Números únicos? Sim Não Não
Pula números em empates? N/A Sim Não
Ordem determinística? Requer desempate Sim (com desempate) Sim (com desempate)

6.2. Exemplo unificado: mesmo conjunto de dados, resultados lado a lado

SELECT 
    funcionario,
    salario,
    ROW_NUMBER() OVER (ORDER BY salario DESC) AS row_num,
    RANK() OVER (ORDER BY salario DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY salario DESC) AS dense_rank
FROM funcionarios;

6.3. Critérios de escolha: necessidade de unicidade, lacunas ou continuidade

  • Escolha ROW_NUMBER quando cada linha precisa de um identificador único (paginação, deduplicação).
  • Escolha RANK quando lacunas são significativas (competições, rankings oficiais).
  • Escolha DENSE_RANK quando a continuidade numérica é mais importante que a precisão posicional (categorização, top N com empates).

7. Boas Práticas e Armadilhas Comuns

7.1. Uso de ORDER BY inadequado – resultados não determinísticos

Sempre inclua colunas de desempate no ORDER BY quando usar ROW_NUMBER() para evitar resultados imprevisíveis. Para RANK e DENSE_RANK, a falta de desempate pode gerar ranks idênticos sem critério claro.

7.2. Performance: impacto de PARTITION BY em grandes tabelas

Particionar por colunas com alta cardinalidade (muitos valores únicos) pode degradar a performance. Em tabelas com milhões de linhas, considere índices apropriados nas colunas usadas em PARTITION BY e ORDER BY.

7.3. Combinação com outras window functions (LAG, LEAD) em consultas analíticas

As funções de ranqueamento podem ser combinadas com LAG() e LEAD() para análises mais profundas:

SELECT 
    data,
    receita,
    RANK() OVER (ORDER BY receita DESC) AS rank_receita,
    LAG(receita, 1) OVER (ORDER BY data) AS receita_dia_anterior
FROM financeiro;

Referências