Bloque 7 · Funciones de ventana PostgreSQL 17 northwind

Ranking: ROW_NUMBER, RANK, DENSE_RANK y NTILE

1. Qué es

Cuatro funciones que numeran filas en vez de agregarlas. No calculan una suma ni una media: dan una posición.

Función	Qué da	Con empates
`row_number()`	1, 2, 3, 4…	los rompe: números consecutivos siempre
`rank()`	posición de clasificación	mismo número, y deja hueco después
`dense_rank()`	posición sin huecos	mismo número, sin hueco
`ntile(n)`	en qué grupo de n cae	reparte por tamaño, no por valor

Ninguna lleva argumento salvo ntile. Todas necesitan ORDER BY dentro de OVER — sin un orden no hay posición.

2. Las tres primeras, lado a lado

La única forma de entenderlas es verlas sobre datos con empates. Beverages los tiene: cuatro productos a 18,00 y dos a 14,00.

SELECT p.product_name, p.unit_price,
       row_number() OVER (ORDER BY p.unit_price DESC) AS rn,
       rank()       OVER (ORDER BY p.unit_price DESC) AS rnk,
       dense_rank() OVER (ORDER BY p.unit_price DESC) AS drnk
FROM products p WHERE p.category_id = 1 ORDER BY p.unit_price DESC;

       product_name        | unit_price | rn | rnk | drnk
---------------------------+------------+----+-----+------
 Côte de Blaye             |     263.50 |  1 |   1 |    1
 Ipoh Coffee               |      46.00 |  2 |   2 |    2
 Chang                     |      19.00 |  3 |   3 |    3
 Lakkalikööri              |      18.00 |  4 |   4 |    4
 Steeleye Stout            |      18.00 |  5 |   4 |    4
 Chartreuse verte          |      18.00 |  6 |   4 |    4
 Chai                      |      18.00 |  7 |   4 |    4
 Outback Lager             |      15.00 |  8 |   8 |    5
 Laughing Lumberjack Lager |      14.00 |  9 |   9 |    6
 Sasquatch Ale             |      14.00 | 10 |   9 |    6
 Rhönbräu Klosterbier      |       7.75 | 11 |  11 |    7
 Guaraná Fantástica        |       4.50 | 12 |  12 |    8

Lee la fila de Outback Lager, que es donde se separan las tres:

rn = 8 — es la octava fila, sin más.
rnk = 8 — es el octavo mejor precio, porque los cuatro empatados a 18,00 consumieron las posiciones 4, 5, 6 y 7. rank() salta de 4 a 8.
drnk = 5 — es el quinto precio distinto de la lista: 263,50 / 46,00 / 19,00 / 18,00 / 15,00.

La forma corta de recordarlo:

row_number() cuenta filas.
rank() cuenta cuántos van por delante, más uno.
dense_rank() cuenta valores distintos.

De ahí sale una identidad útil para comprobar consultas: el dense_rank() máximo es siempre el número de valores distintos. Aquí 8, y count(DISTINCT unit_price) sobre Beverages da 8. Si no cuadra, el ORDER BY de la ventana no es el que crees.

3. Cuál usar

row_number() — cuando necesitas un identificador único por fila. Paginación, "el más reciente de cada cliente", numerar un informe. Si hay empates, alguien tiene que ser el primero.

rank() — cuando publicas una clasificación. Es lo que hace un ranking deportivo: dos segundos y ningún tercero. Si dos comerciales venden lo mismo, no hay motivo para decidir a cara o cruz cuál es mejor.

dense_rank() — cuando cuentas niveles, no puestos. "Los 3 precios más altos" es dense_rank() <= 3. Con rank() te llevarías menos productos de los que crees si hay empates arriba.

ntile(n) — cuando quieres n grupos del mismo tamaño, no del mismo valor. Cuartiles, deciles, segmentación ABC.

3.1 El top N por grupo, otra vez

En lateral resolvimos los 3 productos más vendidos de cada categoría con CROSS JOIN LATERAL. Con ventanas es el otro camino:

WITH t AS (
    SELECT c.category_name, p.product_name,
           sum(od.unit_price*od.quantity*(1-od.discount)) AS f,
           row_number() OVER (PARTITION BY c.category_id
                              ORDER BY sum(od.unit_price*od.quantity*(1-od.discount)) DESC) AS pos
    FROM order_details od
    JOIN products p   ON p.product_id = od.product_id
    JOIN categories c ON c.category_id = p.category_id
    GROUP BY c.category_id, c.category_name, p.product_id, p.product_name
)
SELECT category_name, pos, product_name, round(f, 2) AS facturacion
FROM t WHERE pos <= 3 ORDER BY category_name, pos;

 category_name  | pos |           product_name           | facturacion
----------------+-----+----------------------------------+-------------
 Beverages      |   1 | Côte de Blaye                    |   141396.74
 Beverages      |   2 | Ipoh Coffee                      |    23526.70
 Beverages      |   3 | Chang                            |    16355.96
 Condiments     |   1 | Vegie-spread                     |    16701.10
 ...
 Seafood        |   3 | Boston Crab Meat                 |    17910.63

Las mismas 24 filas que dio la LATERAL. Dos herramientas, un resultado. Cuándo usar cada una:

	`row_number()` + filtro	`CROSS JOIN LATERAL`
Pasadas sobre los datos	una	una por grupo
El filtro `pos <= 3`	necesita un CTE fuera	va dentro, como `LIMIT`
Grupos con lógica distinta	no	sí
Con muchos grupos	suele ganar	suele perder
Con pocos grupos y un índice	pierde	suele ganar

La regla honesta: para un top N por grupo con la misma lógica en todos, row_number(). Para cuando cada grupo necesita su propia consulta, LATERAL. Aquí las dos valen; con 8 categorías la diferencia es irrelevante.

Y el <= 3 va fuera del CTE porque una ventana no puede ir en el WHERE. Es la regla del tema 32, y es lo que obliga al CTE.

4. ntile: repartir en grupos iguales

WITH t AS (
    SELECT o.customer_id, sum(od.unit_price*od.quantity*(1-od.discount)) AS total
    FROM orders o JOIN order_details od ON od.order_id = o.order_id
    GROUP BY 1
),
q AS (SELECT customer_id, total, ntile(4) OVER (ORDER BY total DESC) AS cuartil FROM t)
SELECT cuartil, count(*) AS clientes, round(sum(total), 2) AS facturacion,
       round(100.0 * sum(total) / sum(sum(total)) OVER (), 1) AS pct,
       round(min(total), 2) AS min_cliente, round(max(total), 2) AS max_cliente
FROM q GROUP BY cuartil ORDER BY cuartil;

 cuartil | clientes | facturacion | pct  | min_cliente | max_cliente
---------+----------+-------------+------+-------------+-------------
       1 |       23 |   852661.76 | 67.4 |    16817.10 |   110277.31
       2 |       22 |   266726.91 | 21.1 |     7048.24 |    16476.57
       3 |       22 |   108152.81 |  8.5 |     3239.80 |     7023.98
       4 |       22 |    38251.56 |  3.0 |      100.80 |     3172.16

23 clientes de 89 —el primer cuartil— son el 67,4% de la facturación. El último cuartil, con prácticamente los mismos clientes, aporta el 3,0%. Un cliente del primer cuartil vale, de media, 21 veces uno del cuarto — 37 072,25 contra 1 738,71.

Los grupos no salen exactamente iguales porque 89 no es divisible entre 4. ntile reparte 23/22/22/22: el resto va a los primeros grupos, nunca a los últimos. Con ntile(6) sobre los mismos 89 salen cinco grupos de 15 y uno de 14.

4.1 El gotcha de `ntile`: parte los empates

WITH q AS (SELECT product_name, unit_price, ntile(5) OVER (ORDER BY unit_price) AS quintil FROM products)
SELECT product_name, unit_price, quintil FROM q WHERE unit_price = 18.00 ORDER BY quintil;

   product_name   | unit_price | quintil
------------------+------------+---------
 Chartreuse verte |      18.00 |       2
 Steeleye Stout   |      18.00 |       2
 Chai             |      18.00 |       3
 Lakkalikööri     |      18.00 |       3

Cuatro productos al mismo precio exacto, repartidos entre dos quintiles distintos. Y no es un caso aislado: en los 77 productos hay tres precios partidos entre dos grupos — 12,50 entre el 1 y el 2, 18,00 entre el 2 y el 3, y 38,00 entre el 4 y el 5.

ntile garantiza tamaños iguales, y para lograrlo tiene que cortar por donde le toque, aunque sea en medio de un empate. Cuál de los cuatro cae en el quintil 2 es arbitrario.

Esto importa cuando el grupo tiene consecuencias. Si el quintil decide un descuento, una tarifa o a qué comercial se asigna un cliente, tienes dos casos idénticos tratados distinto y no sabrás explicar por qué. Cuando el corte tiene que respetar el valor, ntile no es la herramienta: lo son los percentiles de distribucion-y-percentiles, o un CASE con umbrales fijos.

5. El problema de los empates en row_number()

row_number() tiene que decidir un orden entre filas empatadas. Si no le dices cómo, decide él, y no promete ser consistente.

Los cuatro productos a 18,00, rankeados dentro de Beverages:

 Lakkalikööri     |  4
 Steeleye Stout   |  5
 Chartreuse verte |  6
 Chai             |  7

Los mismos cuatro, con la misma cláusula OVER (ORDER BY unit_price DESC), pero rankeados sobre los 77 productos:

 Chai             | 44
 Steeleye Stout   | 45
 Chartreuse verte | 46
 Lakkalikööri     | 47

El orden se ha invertido. Lakkalikööri era el mejor de los cuatro y ahora es el peor; Chai era el peor y ahora es el mejor. Misma tabla, mismos datos, misma especificación de ventana — solo cambió el alcance de la consulta.

No es un fallo: el estándar no define qué pasa con los empates, así que el motor usa el orden que le resulte más barato, y ese orden depende del plan. Un cambio de índice, un VACUUM o una lectura en paralelo pueden alterarlo mañana.

La solución es una línea:

row_number() OVER (ORDER BY unit_price DESC, product_id)

Regla sin excepción: todo row_number() lleva un desempate único al final del ORDER BY. Normalmente la clave primaria. Cuesta cuatro palabras y convierte un resultado arbitrario en uno reproducible.

Es la misma lección de ordenar-y-limitar —un ORDER BY ambiguo con LIMIT da resultados distintos— y de el-marco-rows-vs-range. Los empates son el enemigo recurrente de este bloque.

6. Errores comunes

row_number() sin desempate. La sección 5. El error más caro del tema porque parece funcionar.
Usar rank() para paginar. Con empates, rank() BETWEEN 11 AND 20 puede devolver 7 filas o 15. Para paginar, row_number().
Esperar que rank() llegue hasta el número de filas. Sobre Beverages el rank() máximo es 12 y el dense_rank() máximo es 8. Si el informe dice "hay 12 niveles de precio", está mal: hay 8.
Poner la función de ranking en el WHERE. ERROR: window functions are not allowed in WHERE Siempre CTE o subconsulta, y el filtro fuera.
Creer que ntile(4) corta por percentiles. No: corta por número de filas. El corte del primer cuartil cae donde caiga el cliente número 23, valga lo que valga.
Olvidar PARTITION BY en un top N por grupo. Sin él, row_number() numera del 1 al 77 de corrido y pos <= 3 devuelve 3 filas en total, no 3 por categoría. Es el mismo error que el LIMIT fuera de la LATERAL.

7. Diferencias con SQL Server

	PostgreSQL	SQL Server
Las cuatro funciones	idénticas	idénticas
`ORDER BY` obligatorio dentro de `OVER`	sí	sí
`TOP n WITH TIES`	no existe	sí
Equivalente de `WITH TIES`	`rank() <= n` en un CTE	`TOP n WITH TIES`

Portan al pie de la letra. La única diferencia práctica es WITH TIES: en SQL Server SELECT TOP 3 WITH TIES ... ORDER BY precio DESC devuelve más de 3 filas si hay empate en la tercera posición. En PostgreSQL se escribe con rank() <= 3 filtrado fuera de un CTE, que es más largo pero también más explícito.

8. Ejercicios

Ranking de empleados por facturación y por número de pedidos, en la misma tabla. ¿Coinciden?
Los 2 pedidos más caros de cada cliente, con row_number(). Compruébalo con QUICK, ERNSH y CENTC.
¿Cuántos precios distintos hay entre los 77 productos, y cuántos productos están involucrados en algún empate?
Clasifica los 77 productos en 5 quintiles de precio y muestra el rango de cada uno.
Demuestra que el dense_rank() máximo es igual a count(DISTINCT ...). ¿Por qué tiene que serlo?

9. Pregunta de negocio

Comercial tiene tiempo para atender bien a un cuarto de la cartera. Quiere saber a cuáles, y cuánto negocio hay en juego en cada tramo.

"Un cuarto de la cartera" es una instrucción sobre el número de clientes, no sobre el dinero. Eso decide la función.

? RespuestaRespóndela antes de abrir

WITH por_cliente AS (
    SELECT o.customer_id, sum(od.unit_price*od.quantity*(1-od.discount)) AS total
    FROM orders o JOIN order_details od ON od.order_id = o.order_id
    GROUP BY 1
),
q AS (SELECT customer_id, total, ntile(4) OVER (ORDER BY total DESC) AS cuartil FROM por_cliente)
SELECT cuartil, count(*) AS clientes,
       round(sum(total), 2) AS facturacion,
       round(100.0 * sum(total) / sum(sum(total)) OVER (), 1) AS pct,
       round(min(total), 2) AS desde, round(max(total), 2) AS hasta,
       round(avg(total), 2) AS cliente_medio
FROM q GROUP BY cuartil ORDER BY cuartil;

 cuartil | clientes | facturacion | pct  |  desde   |   hasta   | cliente_medio
---------+----------+-------------+------+----------+-----------+---------------
       1 |       23 |   852661.76 | 67.4 | 16817.10 | 110277.31 |      37072.25
       2 |       22 |   266726.91 | 21.1 |  7048.24 |  16476.57 |      12123.95
       3 |       22 |   108152.81 |  8.5 |  3239.80 |   7023.98 |       4916.04
       4 |       22 |    38251.56 |  3.0 |   100.80 |   3172.16 |       1738.71

La respuesta es concreta: los 23 clientes que facturan más de 16 817,10, y en ellos está el 67,4% del negocio.

Tres cosas que hay que decir al entregarlo:

ntile(4) es la función correcta aquí precisamente porque la restricción es de capacidad. Comercial no preguntó "qué clientes valen más de X": preguntó a cuántos puede atender. La pregunta es sobre número de personas, y ntile reparte por número. Si la pregunta hubiera sido "los clientes que suman el 80% del negocio", la herramienta sería un acumulado, no ntile.
El corte cae en 16 817,10 por dónde quedó el cliente número 23, no por ningún criterio de negocio. Mira quiénes están a cada lado de esa raya:

Entre SIMOB y LINOD hay 340,53 euros sobre 16 000: un 2%. Uno entra en la lista de atención preferente y el otro no, y la diferencia entre ambos es ruido. La raya del cuartil no es una frontera de negocio, es dónde cayó el corte. Si la lista tiene consecuencias reales, conviene revisar a mano las dos o tres posiciones de cada lado. 3. Y lo que la tabla no muestra: faltan dos clientes. Son 89 filas, pero customers tiene 91. FISSA y PARIS no han comprado nunca, así que el JOIN con orders los elimina y no aparecen en ningún cuartil.

No es que estén mal clasificados: son invisibles para la segmentación. Y en una decisión sobre a quién dedicar tiempo comercial, un cliente que nunca ha comprado no es lo mismo que uno que no existe. Si el objetivo incluyera captación, la consulta tendría que arrancar de customers con LEFT JOIN, como en lateral.

La lección general: toda segmentación construida sobre una tabla de hechos pierde a quien no tiene hechos. Es el problema del INNER JOIN de left-right-y-full-join, disfrazado de cuartil.

Y la conclusión que va antes que la tabla: la cartera está muy concentrada. Un cuartil se lleva dos tercios del negocio y el último cuartil aporta el 3%. Atender bien al primer cuartil no es una opción táctica, es lo que sostiene la empresa — y la contrapartida es que perder tres o cuatro de esos 23 clientes tiene un efecto que 22 clientes del último cuartil no compensan.

✓ SolucionesResuélvelos antes de abrir

SELECT e.last_name,
       round(sum(od.unit_price*od.quantity*(1-od.discount)), 2) AS facturacion,
       row_number() OVER (ORDER BY sum(od.unit_price*od.quantity*(1-od.discount)) DESC) AS rank_facturacion,
       count(DISTINCT o.order_id) AS pedidos,
       rank() OVER (ORDER BY count(DISTINCT o.order_id) DESC) AS rank_pedidos
FROM employees e
JOIN orders o         ON o.employee_id = e.employee_id
JOIN order_details od ON od.order_id = o.order_id
GROUP BY e.employee_id, e.last_name ORDER BY rank_facturacion;

 last_name | facturacion | rank_facturacion | pedidos | rank_pedidos
-----------+-------------+------------------+---------+--------------
 Peacock   |   232890.85 |                1 |     156 |            1
 Leverling |   202812.84 |                2 |     127 |            2
 Davolio   |   192107.60 |                3 |     123 |            3
 Fuller    |   166537.76 |                4 |      96 |            5
 Callahan  |   126862.28 |                5 |     104 |            4
 King      |   124568.24 |                6 |      72 |            6
 Dodsworth |    77308.07 |                7 |      43 |            8
 Suyama    |    73913.13 |                8 |      67 |            7
 Buchanan  |    68792.28 |                9 |      42 |            9

No coinciden, y las dos inversiones dicen algo. Fuller es 4º en dinero con solo 96 pedidos (5º en volumen): vende menos veces y más caro. Callahan es lo contrario — 104 pedidos, 4ª en volumen, pero 5ª en dinero.

Lo mismo abajo: Dodsworth hace 43 pedidos y factura más que Suyama con 67. Dos formas distintas de vender, y ningún ranking por sí solo las distingue. Con las dos columnas al lado, la diferencia salta.

WITH t AS (
    SELECT o.customer_id, o.order_id,
           sum(od.unit_price*od.quantity*(1-od.discount)) AS total,
           row_number() OVER (PARTITION BY o.customer_id
                              ORDER BY sum(od.unit_price*od.quantity*(1-od.discount)) DESC,
                                       o.order_id) AS pos
    FROM orders o JOIN order_details od ON od.order_id = o.order_id
    GROUP BY o.customer_id, o.order_id
)
SELECT customer_id, pos, order_id, round(total, 2)
FROM t WHERE pos <= 2 AND customer_id IN ('QUICK','ERNSH','CENTC')
ORDER BY customer_id, pos;

 customer_id | pos | order_id |  round
-------------+-----+----------+----------
 CENTC       |   1 |    10259 |   100.80
 ERNSH       |   1 |    10514 |  8623.45
 ERNSH       |   2 |    11017 |  6750.00
 QUICK       |   1 |    10865 | 16387.50
 QUICK       |   2 |    10540 | 10191.70

CENTC devuelve una fila y no dos, sin necesidad de ningún cuidado especial. WHERE pos <= 2 no exige que existan dos: si el cliente tiene un pedido, hay un pos = 1 y nada más. Es una ventaja sobre otras formas de hacerlo.

El , o.order_id del ORDER BY es el desempate obligatorio de la sección 5.

SELECT count(DISTINCT unit_price) AS precios_distintos, count(*) AS productos FROM products;
-- 61 precios distintos, 77 productos

SELECT count(*) AS precios_con_empate, sum(n) AS productos_empatados
FROM (SELECT unit_price, count(*) n FROM products GROUP BY unit_price HAVING count(*) > 1) t;
-- 11 precios con empate, 27 productos empatados

27 de 77 productos —el 35%— comparten precio con otro. No es un caso raro: en más de un tercio de la tabla, un row_number() sin desempate está decidiendo arbitrariamente. Por eso la regla de la sección 5 no es paranoia.

WITH q AS (SELECT product_name, unit_price, ntile(5) OVER (ORDER BY unit_price) AS quintil FROM products)
SELECT quintil, count(*) AS productos, min(unit_price) AS min, max(unit_price) AS max
FROM q GROUP BY quintil ORDER BY quintil;

 quintil | productos |  min  |  max
---------+-----------+-------+--------
       1 |        16 |  2.50 |  12.50
       2 |        16 | 12.50 |  18.00
       3 |        15 | 18.00 |  23.25
       4 |        15 | 24.00 |  38.00
       5 |        15 | 38.00 | 263.50

Los quintiles 1 y 2 tienen 16 productos y el resto 15: 77 entre 5 da 15 con resto 2, y los dos sobrantes van a los primeros grupos.

Y mira los bordes: 12,50 es el máximo del quintil 1 y el mínimo del 2. 18,00 y 38,00 hacen lo mismo. Es el gotcha de la sección 4 a la vista: tres precios partidos entre dos grupos.

El quintil 5 va de 38,00 a 263,50 — un rango 25 veces más ancho que el del quintil 1 (2,50 a 12,50). ntile iguala el número de productos, no el rango de precio. Si lo que quieres son tramos de precio comparables, esto no sirve.

WITH t AS (SELECT unit_price,
                  rank()       OVER (ORDER BY unit_price DESC) r,
                  dense_rank() OVER (ORDER BY unit_price DESC) d
           FROM products WHERE category_id = 1)
SELECT max(r) AS max_rank, max(d) AS max_dense,
       count(*) AS filas, count(DISTINCT unit_price) AS precios_distintos FROM t;

 max_rank | max_dense | filas | precios_distintos
----------+-----------+-------+-------------------
       12 |         8 |    12 |                 8

max(rank()) = count(*) y max(dense_rank()) = count(DISTINCT ...). Tiene que ser así por definición: dense_rank avanza exactamente una vez por cada valor nuevo que aparece en el ORDER BY, así que al llegar al final ha avanzado tantas veces como valores distintos haya. rank, en cambio, avanza tantas posiciones como filas haya consumido.

Es una comprobación gratis para cualquier consulta con ranking: si max(dense_rank()) no coincide con los valores distintos que esperas, el ORDER BY de la ventana no es el que creías.