Las funciones de ranking son funciones de ventana que asignan un número a cada fila según un orden. Las tres principales se diferencian solo en cómo tratan los empates:
| Función | Con empates | Ejemplo con dos empatados en el 2.º puesto |
|---|---|---|
ROW_NUMBER() |
Números distintos, sin repetir | 1, 2, 3, 4 |
RANK() |
Mismo número, y salta los siguientes | 1, 2, 2, 4 |
DENSE_RANK() |
Mismo número, sin saltos | 1, 2, 2, 3 |
ROW_NUMBER() OVER ([PARTITION BY columnas] ORDER BY columnas)
En las tres el ORDER BY dentro de OVER es obligatorio: sin orden no hay ranking.
Las tres juntas
Ordenamos los pedidos por fecha, de más reciente a más antiguo. Los dos Echo DOT se pidieron el mismo día, el 1 de septiembre, así que empatan:
| producto | fechaPedido | numeroFila | ranking | rankingDenso |
|---|---|---|---|---|
| Horno Balay | 2022-09-10 | 1 | 1 | 1 |
| Nintendo Switch | 2022-09-05 | 2 | 2 | 2 |
| Echo DOT 3 | 2022-09-01 | 3 | 3 | 3 |
| Echo DOT 4 | 2022-09-01 | 4 | 3 | 3 |
| Play Station 5 | 2022-07-18 | 5 | 5 | 4 |
| Xiami Mi 11 | 2022-07-09 | 6 | 6 | 5 |
| MacBook Pro M1 | 2022-06-20 | 7 | 7 | 6 |
| Xbox series X | 2022-01-21 | 8 | 8 | 7 |
En la fila de la PlayStation se ve la diferencia: RANK salta del 3 al 5 porque hay dos pedidos en el puesto 3, y DENSE_RANK sigue con el 4.
ROW_NUMBER reparte los números de forma arbitraria entre las filas empatadas, y dos ejecuciones pueden no coincidir. Por eso en el ejemplo se añade idPedidos al ORDER BY: así el resultado es siempre el mismo.Ranking dentro de cada grupo
Con PARTITION BY la numeración vuelve a empezar en cada grupo. El pedido más caro de cada cliente queda con el número 1:
| nombre | producto | totalPrecio | posicion |
|---|---|---|---|
| Alejandro | Nintendo Switch | 899,97 | 1 |
| Ana | Xiami Mi 11 | 4304,25 | 1 |
| Fernando | MacBook Pro M1 | 2449,50 | 1 |
| Luis | Echo DOT 4 | 2999,50 | 1 |
| Luis | Echo DOT 3 | 1999,50 | 2 |
| María | Play Station 5 | 2199,80 | 1 |
| María | Xbox series X | 999,98 | 2 |
Top N por grupo
Es una de las preguntas de entrevista de SQL más típicas: “saca el pedido más caro de cada cliente” o “los tres productos más vendidos de cada categoría”. Como una función de ventana no se puede usar en el WHERE, se calcula en una CTE y se filtra fuera:
| nombre | producto | totalPrecio |
|---|---|---|
| Ana | Xiami Mi 11 | 4304,25 |
| Luis | Echo DOT 4 | 2999,50 |
| Fernando | MacBook Pro M1 | 2449,50 |
| María | Play Station 5 | 2199,80 |
| Alejandro | Nintendo Switch | 899,97 |
Cambia posicion = 1 por posicion <= 2 para obtener los dos pedidos más caros de cada cliente.
¿ROW_NUMBER o RANK aquí? Depende de qué quieras hacer con los empates. Con ROW_NUMBER sale exactamente un pedido por cliente aunque haya dos con el mismo importe; con RANK salen los dos.
Eliminar duplicados con ROW_NUMBER
Otro uso muy habitual: quedarse con una sola fila de cada grupo de duplicados. Aquí, un pedido por cada descripción de producto, el más reciente:
| descripcionProducto | producto | fechaPedido |
|---|---|---|
| Altavoz Alexa | Echo DOT 4 | 2022-09-01 |
| Consola Nintendo | Nintendo Switch | 2022-09-05 |
| Consola Sony | Play Station 5 | 2022-07-18 |
| Consola Xbox | Xbox series X | 2022-01-21 |
| Electrodoméstico | Horno Balay | 2022-09-10 |
| Portátil Apple | MacBook Pro M1 | 2022-06-20 |
| Smartphone | Xiami Mi 11 | 2022-07-09 |
Hay ocho pedidos pero siete descripciones: de los dos Altavoz Alexa solo queda uno. En SQL Server la misma CTE sirve para borrar los duplicados: DELETE FROM numerados WHERE n > 1.
NTILE: repartir en grupos iguales
NTILE(n) divide las filas ordenadas en n grupos del mismo tamaño (o casi). Sirve para hacer cuartiles, deciles o tramos:
| producto | totalPrecio | cuartil |
|---|---|---|
| Xiami Mi 11 | 4304,25 | 1 |
| Echo DOT 4 | 2999,50 | 1 |
| MacBook Pro M1 | 2449,50 | 2 |
| Play Station 5 | 2199,80 | 2 |
| Echo DOT 3 | 1999,50 | 3 |
| Horno Balay | 1399,10 | 3 |
| Xbox series X | 999,98 | 4 |
| Nintendo Switch | 899,97 | 4 |
Practica
- Numera a los clientes por orden alfabético de apellidos.
- Saca el pedido más antiguo de cada cliente.
- Cambia el
ROW_NUMBERdel ejemplo de top N porRANK. ¿Cambia algo con estos datos? ¿Por qué?
