Rankings con RANK, DENSE_RANK y ROW_NUMBER — Proyecto: Analítica de Ventas — Iterixs

Proyecto: Analítica de Ventas

Inscríbete para acceder a todas las lecciones de este curso.

Contenido

Lección 10 de 18· Hito 3 — Top-N por categoría con funciones de ventana

Rankings con RANK, DENSE_RANK y ROW_NUMBER

Lectura

El problema: el top-N por grupo

Una pregunta de negocio que aparece una y otra vez en analítica de ventas es:

¿Cuáles son los 3 productos más vendidos dentro de cada categoría?

Fíjate en el matiz: no quieres los 3 mejores productos del catálogo completo (eso sería un simple ORDER BY ingresos DESC LIMIT 3). Quieres los 3 mejores de cada categoría por separado. Si una categoría como Electrónica concentra todas las ventas, un LIMIT 3 global te devolvería tres productos de Electrónica y dejaría Hogar y Deportes sin representación. Eso no responde la pregunta.

Este patrón —ranquear filas dentro de particiones y quedarte con las primeras de cada una— es exactamente para lo que existen las funciones de ventana de ranking: ROW_NUMBER, RANK y DENSE_RANK. En esta lección vas a producir un top-N por categoría de principio a fin, vas a ver por qué las tres funciones se comportan distinto ante empates, y vas a aprender el patrón con CTE que necesitas para filtrar por el ranking (algo que, como verás, no se puede hacer directamente en WHERE).

Recordatorio rápido: `OVER (PARTITION BY ... ORDER BY ...)`

Ya viste funciones de ventana en SQL avanzado, así que solo refrescamos lo justo. Una función de ventana calcula un valor mirando un conjunto de filas relacionadas con la fila actual, sin colapsarlas como hace GROUP BY. La cláusula OVER define esa ventana:

PARTITION BY categoria divide las filas en grupos independientes (uno por categoría). El ranking se reinicia en cada partición.
ORDER BY ingresos DESC define el orden dentro de cada partición: quién va primero, segundo, tercero…

Así que ROW_NUMBER() OVER (PARTITION BY categoria ORDER BY ingresos DESC) significa literalmente: «numera las filas de cada categoría, de mayores a menores ingresos». Vamos a verlo con datos reales.

Paso 1 — Calcular los ingresos por producto

Antes de ranquear necesitamos la métrica sobre la que ranquear. Partimos del detalle de ventas y agregamos los ingresos (cantidad × precio) de cada producto, junto con su categoría. Ejecuta esto primero:

sql

SELECT
    c.nombre              AS categoria,
    p.nombre              AS producto,
    SUM(d.cantidad * p.precio) AS ingresos
FROM detalle_ventas d
JOIN productos   p ON p.id = d.producto_id
JOIN categorias  c ON c.id = p.categoria_id
GROUP BY c.nombre, p.nombre
ORDER BY c.nombre, ingresos DESC;

Obtienes los ingresos totales de cada producto, ordenados dentro de su categoría. Mira con atención: en Electrónica, Auriculares X y Monitor 4K tienen exactamente los mismos ingresos (3.200), y en Hogar, Cafetera y Aspiradora empatan en 2.800. Esos empates van a ser clave para entender la diferencia entre las tres funciones de ranking.

Paso 2 — Las tres funciones, lado a lado

Envolvemos la agregación anterior en una CTE (WITH ingresos_producto AS (...)) para no repetirla, y aplicamos las tres funciones de ranking sobre la misma ventana. Así puedes comparar columna a columna qué número asigna cada una:

sql

WITH ingresos_producto AS (
    SELECT
        c.nombre              AS categoria,
        p.nombre              AS producto,
        SUM(d.cantidad * p.precio) AS ingresos
    FROM detalle_ventas d
    JOIN productos   p ON p.id = d.producto_id
    JOIN categorias  c ON c.id = p.categoria_id
    GROUP BY c.nombre, p.nombre
)
SELECT
    categoria,
    producto,
    ingresos,
    ROW_NUMBER() OVER (PARTITION BY categoria ORDER BY ingresos DESC) AS row_number,
    RANK()       OVER (PARTITION BY categoria ORDER BY ingresos DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY categoria ORDER BY ingresos DESC) AS dense_rank
FROM ingresos_producto
ORDER BY categoria, ingresos DESC;

Concéntrate en la categoría Electrónica, donde Auriculares X y Monitor 4K empatan en 3.200. Las tres columnas dicen cosas distintas:

producto	ingresos	row_number	rank	dense_rank
Laptop Pro	5.000	1	1	1
Auriculares X	3.200	2	2	2
Monitor 4K	3.200	3	2	2
Teclado Mecánico	1.500	4	4	3
Webcam HD	900	5	5	4

Ahí está toda la diferencia, en cómo trata cada función el empate de 3.200 y qué pasa con la fila siguiente.

Las tres funciones: en qué se diferencian

Función	Ante un empate	¿Deja huecos después del empate?	Garantiza valores únicos
`ROW_NUMBER`	Asigna números distintos y consecutivos (2, 3); el desempate es arbitrario salvo que lo fijes en el `ORDER BY`	No deja huecos (…2, 3, 4…)	Sí — exactamente una fila por número
`RANK`	Asigna el mismo número a los empatados (2, 2)	Sí: salta posiciones (después de dos «2» viene «4»)	No
`DENSE_RANK`	Asigna el mismo número a los empatados (2, 2)	No: el siguiente es «3», sin huecos	No

La intuición:

ROW_NUMBER = «dame una numeración limpia 1,2,3,… cueste lo que cueste». Perfecta cuando necesitas exactamente N filas por grupo y no te importa cuál de dos empatados sale primero (o lo decides con un criterio de desempate).
RANK = el ranking «de competición deportiva»: dos medallas de plata y no hay bronce (después de 2,2 viene 4). Útil cuando quieres reflejar que «ambos quedaron segundos».
DENSE_RANK = ranking «de niveles de precio» o categorías distintas: dos en el nivel 2, y el siguiente es el nivel 3, sin saltos. Útil para «¿cuántos niveles de ingreso distintos hay y en cuál cae cada producto?».

Consejo: si en tu ORDER BY los valores nunca empatan, las tres devuelven lo mismo. La elección solo importa cuando hay empates — y en datos reales casi siempre los hay, así que conviene decidir conscientemente.

El error clásico: filtrar por la función de ventana en `WHERE`

Lo natural sería escribir «dame las filas donde row_number <= 3». Pero esto falla. Ejecútalo y lee el error:

sql

-- ❌ Esto NO funciona
SELECT
    c.nombre AS categoria,
    p.nombre AS producto,
    SUM(d.cantidad * p.precio) AS ingresos,
    ROW_NUMBER() OVER (PARTITION BY c.nombre ORDER BY SUM(d.cantidad * p.precio) DESC) AS rn
FROM detalle_ventas d
JOIN productos   p ON p.id = d.producto_id
JOIN categorias  c ON c.id = p.categoria_id
GROUP BY c.nombre, p.nombre
WHERE rn <= 3;

PostgreSQL responde con un error de columna rn inexistente (o de sintaxis cerca de WHERE). La razón es el orden lógico de evaluación de una consulta: WHERE se procesa antes que SELECT, y las funciones de ventana se calculan en la fase de SELECT, después de FROM/WHERE/GROUP BY/HAVING. Cuando WHERE se ejecuta, la columna rn todavía no existe — ni siquiera puedes referenciar su alias.

No es una limitación caprichosa: una función de ventana necesita ver todas las filas del grupo para numerarlas, así que no puede usarse para decidir qué filas entran o salen en la misma pasada. La solución es separar el cálculo del filtrado en dos etapas, y para eso usamos una CTE.

El patrón de dos etapas (CTE) para el top-N

La receta es siempre la misma: en una etapa calculas el ranking, y en una etapa externa filtras por él. La función de ventana ya está «materializada» como una columna normal de la CTE, así que el WHERE exterior puede usarla sin problema.

Diagrama

Paso 3 — Top-3 productos por categoría con `ROW_NUMBER`

Aquí encadenamos dos CTEs: ingresos_producto (la agregación) y ranking (la numeración). La consulta final solo filtra rn <= 3:

sql

WITH ingresos_producto AS (
    SELECT
        c.nombre              AS categoria,
        p.nombre              AS producto,
        SUM(d.cantidad * p.precio) AS ingresos
    FROM detalle_ventas d
    JOIN productos   p ON p.id = d.producto_id
    JOIN categorias  c ON c.id = p.categoria_id
    GROUP BY c.nombre, p.nombre
),
ranking AS (
    SELECT
        categoria,
        producto,
        ingresos,
        ROW_NUMBER() OVER (PARTITION BY categoria ORDER BY ingresos DESC) AS rn
    FROM ingresos_producto
)
SELECT categoria, producto, ingresos, rn
FROM ranking
WHERE rn <= 3
ORDER BY categoria, rn;

Obtienes exactamente 3 filas por categoría, ordenadas de mayor a menor ingreso. Como usamos ROW_NUMBER, el corte es siempre limpio: aunque Auriculares X y Monitor 4K empaten en Electrónica, uno recibe el 2 y el otro el 3, y ambos entran en el top-3. Esa es la garantía de ROW_NUMBER: N filas, ni una más.

El problema aparece cuando el empate cae justo en la frontera del corte. Veámoslo.

Paso 4 — Cuando los empates importan en el corte: usa `RANK`

Imagina ahora que la pregunta es top-2 por categoría. En Electrónica, el puesto 2 está empatado (Auriculares y Monitor, ambos en 3.200). Con ROW_NUMBER solo entraría uno de los dos —el otro recibe el rn = 3 y queda fuera— y cuál entra es arbitrario. Eso puede ser injusto o directamente incorrecto para el negocio: ¿por qué premiar a un producto y no al otro si vendieron lo mismo?

Si quieres que todos los empatados en la frontera entren, cambia ROW_NUMBER por RANK y filtra por él:

sql

WITH ingresos_producto AS (
    SELECT
        c.nombre              AS categoria,
        p.nombre              AS producto,
        SUM(d.cantidad * p.precio) AS ingresos
    FROM detalle_ventas d
    JOIN productos   p ON p.id = d.producto_id
    JOIN categorias  c ON c.id = p.categoria_id
    GROUP BY c.nombre, p.nombre
),
ranking AS (
    SELECT
        categoria,
        producto,
        ingresos,
        RANK() OVER (PARTITION BY categoria ORDER BY ingresos DESC) AS posicion
    FROM ingresos_producto
)
SELECT categoria, producto, ingresos, posicion
FROM ranking
WHERE posicion <= 2
ORDER BY categoria, posicion, producto;

Ahora Electrónica devuelve 3 filas para un «top-2»: Laptop Pro (posición 1) y ambos productos empatados en la posición 2. Igual ocurre en Hogar, donde Cafetera y Aspiradora empatan en la posición 1. RANK te da un top-N que respeta los empates, a costa de que el número de filas por grupo ya no es fijo.

Esta es la decisión de diseño clave del top-N:

¿Necesitas exactamente N filas (por ejemplo, para una rejilla de UI de 3 tarjetas)? → ROW_NUMBER.
¿Necesitas «los N mejores incluyendo a quien empate en el corte»? → RANK (o DENSE_RANK si prefieres contar niveles distintos en lugar de posiciones).

Cómo elegir, en una frase

text

¿Quiero exactamente N filas por grupo?
   └─ Sí  → ROW_NUMBER  (desempata con un 2.º criterio en el ORDER BY si te importa cuál)
   └─ No, quiero incluir a los empatados en el corte:
        ├─ contar posiciones «de competición» (1,2,2,4) → RANK
        └─ contar niveles distintos sin huecos (1,2,2,3) → DENSE_RANK

Un detalle profesional sobre ROW_NUMBER: si te importa cuál de dos empatados sale primero, añade un criterio de desempate determinista al ORDER BY, por ejemplo ORDER BY ingresos DESC, producto ASC. Así el resultado es reproducible entre ejecuciones, en lugar de depender del orden interno que el motor decida ese día. La reproducibilidad importa mucho en un proyecto de portafolio: tu informe debe dar el mismo número cada vez que se corre.

Puntos clave

El top-N por grupo no se resuelve con LIMIT: se resuelve ranqueando con OVER (PARTITION BY grupo ORDER BY metrica DESC).
ROW_NUMBER, RANK y DENSE_RANK solo difieren ante empates: ROW_NUMBER numera 1,2,3,4 (único); RANK hace 1,2,2,4 (con hueco); DENSE_RANK hace 1,2,2,3 (sin hueco).
No puedes filtrar por una función de ventana en WHERE, porque se calcula después. Envuelve el ranking en una CTE y filtra en la consulta externa.
Elige la función según el resultado que necesitas: ROW_NUMBER para exactamente N filas, RANK/DENSE_RANK para incluir empates. Añade un desempate en el ORDER BY para resultados reproducibles.

Tu turno

Antes de pasar a la siguiente lección, prueba a modificar las consultas:

Cambia el top-3 a un top-1 por categoría con ROW_NUMBER: obtienes el producto estrella de cada categoría.
Sustituye RANK por DENSE_RANK en el paso 4 y observa qué productos cambian de posición cuando hay empates intermedios.
Añade , producto ASC al ORDER BY de la ventana en el paso 3 y comprueba que el desempate de Electrónica se vuelve estable (gana siempre el que va antes alfabéticamente).

←→para cambiar de lección