🙏 Realmente necesitamos su apoyo. Necesitamos financiamiento para continuar nuestra misión: publicar nuevas lecciones y mejorar la plataforma. Si puede, por favor apoye el proyecto con una donación. Ayude al proyecto ahora →
Código SQL copiado al portapapeles

SQLtest.online

Práctica

Lecciones

Entorno de Pruebas

Prueba

Lesión 7.2 · Tiempo de lectura: ~9 minutos

Esta lección se centra en las funciones de clasificación ROW_NUMBER, RANK, DENSE_RANK y NTILE. Aprenderás cómo se comporta cada función cuando los valores están empatados y cuándo usarla en la práctica. Al final de la lección, podrás construir clasificaciones precisas para informes, listas top-N y segmentación de clientes.

Uso de ROW_NUMBER, RANK, DENSE_RANK y NTILE

En la lección anterior, introdujimos las funciones de ventana y exploramos ROW_NUMBER(). Ahora profundizaremos en la familia de funciones de clasificación que SQL ofrece: ROW_NUMBER, RANK, DENSE_RANK y NTILE. Cada una tiene un propósito distinto y entender cuándo usar cada una es crucial para un análisis de datos efectivo.

Entendiendo las Diferencias

Las cuatro funciones asignan un valor numérico a las filas basado en el orden, pero manejan los empates (valores iguales) de manera diferente. Exploremos cada una.

ROW_NUMBER(): Números Secuenciales Únicos

ROW_NUMBER() asigna un número secuencial único a cada fila, incluso si los valores son idénticos. Trata los empates como filas diferentes.

Sintaxis:

ROW_NUMBER() OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression
)

Ejemplo: Clasificación de Transacciones

SELECT
    customer_id,
    amount,
    payment_date,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id 
        ORDER BY amount DESC
    ) AS payment_rank
FROM
    payment
WHERE
    customer_id IN (1, 2, 3)
ORDER BY
    customer_id,
    payment_rank;

Ejemplo de Salida:

customer_id | amount | payment_date | payment_rank
1           | 11.99  | 2005-08-01   | 1
1           | 11.99  | 2005-07-08   | 2
1           | 10.99  | 2005-06-19   | 3
2           | 11.99  | 2005-08-02   | 1
2           | 10.99  | 2005-07-09   | 2
3           | 9.99   | 2005-08-03   | 1

Punto Clave: A pesar de que los dos primeros pagos del cliente 1 tienen montos idénticos (11.99), reciben diferentes números de fila (1 y 2).

RANK(): Clasificación con Huecos

RANK() asigna el mismo rango a las filas con valores de orden idénticos, pero deja huecos en la secuencia de numeración. Si dos filas empatan en el rango 1, el siguiente rango es 3 (saltando 2).

Sintaxis:

RANK() OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression
)

Ejemplo: Clasificación de Pagos por Monto

SELECT
    customer_id,
    amount,
    payment_date,
    RANK() OVER (
        PARTITION BY customer_id 
        ORDER BY amount DESC
    ) AS payment_rank
FROM
    payment
WHERE
    customer_id IN (1, 2, 3)
ORDER BY
    customer_id,
    payment_rank;

Ejemplo de Salida:

customer_id | amount | payment_date | payment_rank
1           | 11.99  | 2005-08-01   | 1
1           | 11.99  | 2005-07-08   | 1
1           | 10.99  | 2005-06-19   | 3
2           | 11.99  | 2005-08-02   | 1
2           | 10.99  | 2005-07-09   | 2
3           | 9.99   | 2005-08-03   | 1

Punto Clave: Ambos pagos del cliente 1 de 11.99 reciben rango 1, y el siguiente pago obtiene rango 3 (no 2). Esto es útil cuando deseas identificar empates pero preservar la posición de clasificación en el conjunto de datos completo.

DENSE_RANK(): Clasificación Sin Huecos

DENSE_RANK() es similar a RANK() pero no salta números. Si dos filas empatan en el rango 1, el siguiente rango es 2 (no 3).

Sintaxis:

DENSE_RANK() OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression
)

Ejemplo: Clasificación Densa de Montos de Pago

SELECT
    customer_id,
    amount,
    payment_date,
    DENSE_RANK() OVER (
        PARTITION BY customer_id 
        ORDER BY amount DESC
    ) AS payment_rank
FROM
    payment
WHERE
    customer_id IN (1, 2, 3)
ORDER BY
    customer_id,
    payment_rank;

Ejemplo de Salida:

customer_id | amount | payment_date | payment_rank
1           | 11.99  | 2005-08-01   | 1
1           | 11.99  | 2005-07-08   | 1
1           | 10.99  | 2005-06-19   | 2
2           | 11.99  | 2005-08-02   | 1
2           | 10.99  | 2005-07-09   | 2
3           | 9.99   | 2005-08-03   | 1

Punto Clave: Ambos pagos del cliente 1 de 11.99 reciben rango 1, y el siguiente monto distinto obtiene rango 2. No hay huecos en la secuencia de clasificación. Esto es ideal cuando deseas identificar grupos distintos sin huecos.

NTILE(): Distribuyendo Filas en Grupos

NTILE(n) divide la partición en n grupos (cubos) y asigna a cada fila un número de cubo. Esto es útil para análisis de percentiles y agrupar datos en cuartiles, terciles, etc.

Sintaxis:

NTILE(number_of_buckets) OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression
)

Ejemplo: Análisis de Cuartiles

SELECT
    customer_id,
    amount,
    payment_date,
    NTILE(4) OVER (
        PARTITION BY customer_id 
        ORDER BY amount DESC
    ) AS quartile
FROM
    payment
WHERE
    customer_id IN (1, 2, 3)
ORDER BY
    customer_id,
    quartile;

Ejemplo de Salida:

customer_id | amount | payment_date | quartile
1           | 11.99  | 2005-08-01   | 1
1           | 11.99  | 2005-07-08   | 2
1           | 10.99  | 2005-06-19   | 3
2           | 11.99  | 2005-08-02   | 1
2           | 10.99  | 2005-07-09   | 2
3           | 9.99   | 2005-08-03   | 1

Punto Clave: Las filas se distribuyen en 4 cuartiles. Esto es extremadamente útil para análisis de percentiles: identificar el 25% superior (cuartil 1), el siguiente 25% (cuartil 2), etc.

Comparación Lado a Lado

Veamos las cuatro funciones aplicadas a los mismos datos:

SELECT
    customer_id,
    amount,
    row_number() OVER (ORDER BY amount DESC) AS row_num,
    rank() OVER (ORDER BY amount DESC) AS rnk,
    dense_rank() OVER (ORDER BY amount DESC) AS dense_rnk,
    ntile(3) OVER (ORDER BY amount DESC) AS tertile
FROM
    payment
LIMIT 10;

Ejemplo de Salida:

customer_id | amount | row_num | rnk | dense_rnk | tertile
1           | 11.99  | 1       | 1   | 1         | 1
1           | 11.99  | 2       | 1   | 1         | 1
2           | 11.99  | 3       | 1   | 1         | 1
5           | 10.99  | 4       | 4   | 2         | 1
6           | 10.99  | 5       | 4   | 2         | 1
3           | 9.99   | 6       | 6   | 3         | 2
4           | 9.99   | 7       | 6   | 3         | 2
7           | 8.99   | 8       | 8   | 4         | 3
8           | 8.99   | 9       | 8   | 4         | 3
9           | 7.99   | 10      | 10  | 5         | 3

Observaciones:

  • row_number: Siempre único, sin huecos
  • rank: Agrupa empates pero crea huecos (1, 1, 1, 4, 4, 6, 6, 8, 8, 10)
  • dense_rank: Agrupa empates sin huecos (1, 1, 1, 2, 2, 3, 3, 4, 4, 5)
  • ntile(3): Distribuye en 3 grupos basados en el orden

Aplicaciones Prácticas

Encontrar Mejores Desempeños (ROW_NUMBER)

Obtén el cliente que más paga por mes de alquiler:

WITH ranked_payments AS (
    SELECT
        customer_id,
        amount,
        DATE_TRUNC('month', payment_date) AS month,
        ROW_NUMBER() OVER (
            PARTITION BY DATE_TRUNC('month', payment_date)
            ORDER BY amount DESC
        ) AS rank
    FROM
        payment
)
SELECT
    customer_id,
    amount,
    month
FROM
    ranked_payments
WHERE
    rank = 1
ORDER BY
    month DESC;

Identificando Niveles de Desempeño (DENSE_RANK)

Categoriza películas por frecuencia de alquiler:

WITH rental_counts AS (
    SELECT
        film_id,
        COUNT(*) AS rental_count,
        DENSE_RANK() OVER (
            ORDER BY COUNT(*) DESC
        ) AS popularity_tier
    FROM
        rental r
        JOIN inventory i ON r.inventory_id = i.inventory_id
    GROUP BY
        film_id
)
SELECT
    film_id,
    rental_count,
    CASE
        WHEN popularity_tier = 1 THEN 'Blockbuster'
        WHEN popularity_tier <= 3 THEN 'Popular'
        WHEN popularity_tier <= 10 THEN 'Standard'
        ELSE 'Niche'
    END AS popularity_category
FROM
    rental_counts
LIMIT 20;

Análisis de Percentiles (NTILE)

Segmenta clientes en cuartiles de gasto:

WITH customer_spending AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spent,
        NTILE(4) OVER (ORDER BY SUM(amount)) AS spending_quartile
    FROM
        payment
    GROUP BY
        customer_id
)
SELECT
    spending_quartile,
    COUNT(*) AS customer_count,
    MIN(total_spent) AS low_amount,
    MAX(total_spent) AS high_amount
FROM
    customer_spending
GROUP BY
    spending_quartile
ORDER BY
    spending_quartile;

Cuándo Usar Cada Función

FunciónCaso de UsoManeja Empates
ROW_NUMBERNecesita números secuenciales únicos; no le importa los empatesNo (todos únicos)
RANKNecesita identificar posición pero tener en cuenta empates; los huecos están bienSí (con huecos)
DENSE_RANKNecesita identificación de niveles sin huecos de posiciónSí (sin huecos)
NTILENecesita análisis de percentiles/cuartiles/gruposDistribuye en grupos

Preguntas Frecuentes

¿Cuándo debería elegir RANK en lugar de DENSE_RANK?

Usa RANK cuando los huecos son aceptables y deseas una clasificación estilo competencia. Usa DENSE_RANK cuando necesitas niveles compactos sin huecos.

¿Puedo usar ROW_NUMBER sin PARTITION BY?

Sí. En ese caso, la numeración se ejecuta a través de todo el conjunto de resultados como una única partición.

¿Por qué necesito NTILE si ya tengo funciones de clasificación?

NTILE resuelve un problema diferente: divide las filas en un número fijo de cubos, como cuartiles o deciles.

Preguntas de Entrevista

¿Cuál es la diferencia entre ROW_NUMBER y RANK?

ROW_NUMBER siempre asigna un número único a cada fila, mientras que RANK da el mismo rango a valores iguales y puede saltar números.

¿Cómo funciona NTILE(4)?

Ordena las filas dentro de la ventana y las distribuye en cuatro grupos aproximadamente iguales, asignando a cada fila un número de cuartil del 1 al 4.

¿Cómo obtienes las filas top-N dentro de cada grupo?

Usa ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) en una subconsulta y filtra con WHERE rn <= N.

Conclusiones Clave

  • ROW_NUMBER() da a cada fila un número único, útil para obtener los registros top N de cada grupo.
  • RANK() asigna el mismo rango a valores empatados pero salta rangos (1, 1, 3), útil para clasificaciones competitivas.
  • DENSE_RANK() asigna el mismo rango a valores empatados sin huecos (1, 1, 2), útil para identificación de niveles.
  • NTILE(n) divide las filas en cubos para análisis de percentiles y distribución.
  • Las cuatro funciones son parte de la familia de funciones de ventana y utilizan la cláusula OVER.
  • La diferencia clave es cómo manejan los valores idénticos en la columna de orden.
  • Elegir la función correcta depende de tu objetivo analítico: posicionamiento, agrupamiento o distribución.

En la próxima lección, exploraremos conceptos avanzados de funciones de ventana, incluyendo marcos de ventana, estrategias de particionamiento y otras funciones analíticas como LAG, LEAD, FIRST_VALUE y LAST_VALUE.

Lección anterior
Sigue practicando
Siguiente lección