PERCENT_RANK e CUME_DIST: percentis de um ranking no SQL

PERCENT_RANK e CUME_DIST respondem a pergunta "quao alto esta esta linha em relacao a todas as outras" e retornam um numero entre 0 e 1. Sao a ferramenta certa para percentis de um ranking, quando a posicao absoluta (1.o, 2.o, 3.o) importa menos do que a fatia relativa.

A formula e uma consulta basica

PERCENT_RANK e definido como (rank - 1) / (rows - 1), onde rank e o valor da funcao de janela RANK() e rows e o numero de linhas da janela. A melhor linha recebe 0 e a pior recebe 1.

SELECT
  u.id,
  u.name,
  SUM(o.amount) AS total_spent,
  PERCENT_RANK() OVER (ORDER BY SUM(o.amount) DESC) AS pr,
  CUME_DIST()    OVER (ORDER BY SUM(o.amount) DESC) AS cd
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid'
GROUP BY u.id, u.name
ORDER BY total_spent DESC;

A diferenca entre as duas e fundamental:

• PERCENT_RANK = (rank - 1) / (rows - 1). A linha do topo sempre da exatamente 0.
• CUME_DIST = (linhas <= atual) / (linhas totais). A linha do topo quase nunca e 0; a de baixo e sempre 1.

CUME_DIST responde literalmente: "que fracao dos participantes nao teve resultado melhor que o meu". Um cliente com CUME_DIST = 0.1 numa ordenacao decrescente por gasto esta nos 10% que mais gastam.

Percentis de um ranking

A apresentacao comum e "voce e melhor que N% dos jogadores". Isso e 1 - PERCENT_RANK, multiplicado por 100.

WITH board AS (
  SELECT
    e.id,
    e.name,
    e.dept,
    e.salary,
    PERCENT_RANK() OVER (ORDER BY e.salary) AS pr
  FROM employees e
)
SELECT
  name,
  dept,
  salary,
  ROUND((pr * 100)::numeric, 1) AS percentile,
  ROUND(((1 - pr) * 100)::numeric, 1) AS better_than_pct
FROM board
ORDER BY salary DESC;

Voce pode calcular o percentil dentro de um grupo adicionando PARTITION BY. Entao o salario e comparado apenas com os colegas do mesmo departamento:

SELECT
  name,
  dept,
  salary,
  PERCENT_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_pr
FROM employees;

Tratamento de empates

PERCENT_RANK usa RANK() por baixo dos panos, entao valores iguais recebem o mesmo resultado. Muitas vezes e o desejado: dois jogadores com a mesma pontuacao compartilham o percentil. Mas isso tambem cria lacunas na distribuicao e falta de determinismo na ordenacao.

Gotcha: quando os valores do ORDER BY empatam, a ordem das linhas e indefinida. O PERCENT_RANK e estavel dentro do grupo empatado, mas qualquer funcao vizinha (ROW_NUMBER, LAG) vai retornar resultados diferentes a cada execucao. Sempre adicione uma coluna de desempate unica.

SELECT
  u.id,
  u.name,
  COUNT(o.id) AS orders_cnt,
  PERCENT_RANK() OVER (
    ORDER BY COUNT(o.id) DESC, u.id
  ) AS pr
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name
ORDER BY orders_cnt DESC, u.id;

Aqui u.id torna a ordem totalmente deterministica. Mas atencao: com um desempate nao ha mais empates, e cada linha recebe um rank unico, um comportamento diferente do empate "real". Escolha de forma consciente.

Casos de borda e diferencas entre motores

• Uma unica linha na janela: rows = 1, entao a formula divide por zero conceitualmente, mas o padrao define o resultado como 0, e o PostgreSQL retorna 0.
• Um NULL no ORDER BY participa da ordenacao; controle com NULLS FIRST / NULLS LAST.
• PostgreSQL: ambas as funcoes seguem o padrao SQL.
• MySQL: suportadas desde a 8.0; a sintaxe e identica.
• ClickHouse: historicamente nao existem PERCENT_RANK / CUME_DIST como funcoes de janela, entao emule com rank() e count() sobre uma janela: (rank() OVER w - 1) / (count() OVER w - 1).

Regra pratica: para "posicao em porcentagem" use PERCENT_RANK (o topo e sempre 0); para "fatia que nao me supera" use CUME_DIST. E nunca publique percentis sem desempate, ou as linhas vizinhas vao saltar entre execucoes.