Aprende SQL · lección gratuita

Lección 34 · Distribución: NTILE y percentiles

Resumen

Las funciones de distribución ubican cada fila dentro de la repartición global de valores: en qué cuartil/decil cae, qué porcentaje de filas quedan por debajo, o su posición relativa entre 0 y 1. Sirven para segmentar (cuartiles de salario, deciles de revenue), detectar outliers y aproximar medianas y percentiles, que SQLite no ofrece como agregado nativo. Todas son funciones de ventana y requieren ORDER BY dentro de OVER.

• NTILE(n): reparte las filas ordenadas en n buckets de tamaño lo más igual posible (cuartiles con 4, deciles con 10). Devuelve el número de bucket (1…n).
• PERCENT_RANK(): posición relativa en [0, 1], calculada como (rank - 1) / (total_filas - 1). La primera fila vale 0, la última 1.
• CUME_DIST(): distribución acumulada en (0, 1]: fracción de filas con valor ≤ al actual. Útil para "qué % gana hasta este salario".
• Mediana / percentil aproximado: como no hay PERCENTILE() nativo, se obtiene con CUME_DIST() y un filtro tipo cd >= 0.5 (mediana) en una CTE.
• ORDER BY define el sentido: ordena ascendente para "menor = bucket 1", descendente para "mejor = bucket 1". Elige conscientemente.

Sintaxis / Conceptos

NTILE(n)        OVER ([PARTITION BY ...] ORDER BY ...)
PERCENT_RANK()  OVER ([PARTITION BY ...] ORDER BY ...)
CUME_DIST()     OVER ([PARTITION BY ...] ORDER BY ...)

NTILE y el reparto desigual. Si las filas no se dividen exactamente entre n, los primeros buckets reciben una fila más. Con 22 empleados y NTILE(4): 22 = 6+6+5+5, así que los cuartiles 1 y 2 tienen 6 filas y los 3 y 4 tienen 5. NTILE reparte por conteo de filas, no por rangos de valor: dos salarios iguales podrían caer en buckets distintos si están en la frontera.

PERCENT_RANK vs CUME_DIST. Ambas dan una posición en el intervalo unitario, pero:

• PERCENT_RANK = (rank − 1) / (N − 1) → la primera fila es exactamente 0.
• CUME_DIST = (nº de filas ≤ valor actual) / N → ninguna fila vale 0; la última vale 1.

Para "¿qué fracción de empleados gana menos que este?", PERCENT_RANK es lo natural. Para "¿qué fracción gana este salario o menos?", usa CUME_DIST.

Mediana sin función nativa. SQLite carece de PERCENTILE_CONT/MEDIAN. Una mediana aproximada (el menor valor cuya distribución acumulada llega al 50%) se calcula filtrando CUME_DIST() >= 0.5 en una CTE y tomando el MIN.

SELECT nombre, salario, ROUND(AVG(salario) OVER (ORDER BY salario ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING), 0) AS media_movil FROM empleados ORDER BY salario

Ejemplos

-- Cuartiles de salario: NTILE(4) reparte a los 22 empleados en 4 grupos.
-- Orden ascendente => cuartil 1 = los que menos ganan.
SELECT nombre,
       departamento_id,
       salario,
       NTILE(4) OVER (ORDER BY salario) AS cuartil
FROM empleados
ORDER BY salario;

-- Posición relativa de cada salario: PERCENT_RANK y CUME_DIST.
SELECT nombre,
       salario,
       ROUND(PERCENT_RANK() OVER (ORDER BY salario), 3) AS percent_rank,
       ROUND(CUME_DIST()    OVER (ORDER BY salario), 3) AS cume_dist
FROM empleados
ORDER BY salario DESC;

-- Resumen por cuartil (ordenado DESC => Q1 = top 25% mejor pagado)
-- y mediana aproximada vía CUME_DIST en una CTE aparte.
WITH q AS (
  SELECT salario,
         NTILE(4) OVER (ORDER BY salario DESC) AS cuartil
  FROM empleados
)
SELECT cuartil,
       COUNT(*)            AS empleados,
       MIN(salario)        AS min_salario,
       MAX(salario)        AS max_salario,
       ROUND(AVG(salario), 0) AS promedio
FROM q
GROUP BY cuartil
ORDER BY cuartil;

-- Mediana de salario aproximada: el menor salario cuya distribución
-- acumulada alcanza el 50%. CUME_DIST en CTE, filtro fuera.
WITH c AS (
  SELECT salario,
         CUME_DIST() OVER (ORDER BY salario) AS cd
  FROM empleados
)
SELECT MIN(salario) AS mediana_aprox
FROM c
WHERE cd >= 0.5;

💡 NTILE reparte por número de filas, no por valor: no garantiza que cada bucket cubra el mismo rango de salarios. Si necesitas cortes por valor (ej. "más de 10000"), usa CASE/percentiles, no NTILE. Y recuerda redondear (ROUND) los resultados decimales para comparaciones estables.

Cheatsheet

Función	Rango	Qué mide
NTILE(n)	1…n	Bucket (cuartil/decil) por conteo de filas
PERCENT_RANK()	0…1	(rank-1)/(N-1); primera fila = 0
CUME_DIST()	(0…1]	Fracción de filas ≤ valor actual; última = 1
NTILE(4)	cuartiles	Reparte en 4; primeros buckets +1 fila si no divide
MIN(x) WHERE CUME_DIST>=0.5	—	Mediana aproximada (en CTE)
ORDER BY x DESC dentro de OVER	—	Invierte el sentido (mejor = bucket 1)

---

← Totales acumulados y marcos de ventana (frames)Insertar datos: INSERT →

Ver todas las lecciones de Aprende SQL →