PERCENT_RANK y CUME_DIST: percentiles de un ranking en SQL

PERCENT_RANK y CUME_DIST responden a la pregunta "que tan alto esta esta fila respecto a todas las demas" y devuelven un numero entre 0 y 1. Son la herramienta ideal para percentiles de un ranking, cuando la posicion absoluta (1.o, 2.o, 3.o) importa menos que la cuota relativa.

La formula y una consulta basica

PERCENT_RANK se define como (rank - 1) / (rows - 1), donde rank es el valor de la funcion de ventana RANK() y rows es el numero de filas de la ventana. La mejor fila obtiene 0 y la peor obtiene 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;

La diferencia entre ambas es fundamental:

• PERCENT_RANK = (rank - 1) / (rows - 1). La fila superior siempre da exactamente 0.
• CUME_DIST = (filas <= actual) / (filas totales). La fila superior casi nunca es 0; la inferior siempre es 1.

CUME_DIST responde literalmente: "que fraccion de participantes no obtuvo mejor resultado que yo". Un cliente con CUME_DIST = 0.1 bajo un orden descendente por gasto esta en el 10% que mas gasta.

Percentiles de un ranking

La presentacion habitual es "eres mejor que el N% de los jugadores". Eso es 1 - PERCENT_RANK, escalado a 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;

Puedes calcular el percentil dentro de un grupo anadiendo PARTITION BY. Entonces el salario se compara solo con los companeros del mismo departamento:

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

Manejo de empates

PERCENT_RANK usa RANK() por debajo, asi que los valores iguales obtienen el mismo resultado. A menudo es lo deseado: dos jugadores con la misma puntuacion comparten percentil. Pero tambien crea huecos en la distribucion y falta de determinismo en el orden.

Gotcha: cuando los valores de ORDER BY empatan, el orden de las filas es indefinido. PERCENT_RANK es estable dentro del grupo empatado, pero cualquier funcion vecina (ROW_NUMBER, LAG) devolvera resultados distintos en cada ejecucion. Anade siempre una columna 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 hace el orden totalmente determinista. Pero ojo: con un desempate ya no hay empates, y cada fila recibe un rango unico, un comportamiento distinto al de un empate "real". Eligelo de forma consciente.

Casos limite y diferencias entre motores

• Una sola fila en la ventana: rows = 1, asi que la formula divide por cero conceptualmente, pero el estandar define el resultado como 0, y PostgreSQL devuelve 0.
• Un NULL en ORDER BY participa en el orden; controlalo con NULLS FIRST / NULLS LAST.
• PostgreSQL: ambas funciones siguen el estandar SQL.
• MySQL: soportadas desde la 8.0; la sintaxis es identica.
• ClickHouse: historicamente no existen PERCENT_RANK / CUME_DIST como funciones de ventana, asi que emulalas con rank() y count() sobre una ventana: (rank() OVER w - 1) / (count() OVER w - 1).

Regla practica: para "posicion en porcentaje" usa PERCENT_RANK (el top siempre es 0); para "cuota que no me supera" usa CUME_DIST. Y nunca publiques percentiles sin desempate, o las filas vecinas saltaran entre ejecuciones.