Curso de MariaDB / Comparar ROW_NUMBER, RANK y DENSE_RANK
Las funciones de ventana de MariaDB añaden cálculos a un conjunto de filas sin agruparlas en una sola fila de salida. ROW_NUMBER asigna un número distinto a cada fila; RANK y DENSE_RANK permiten compartir posición cuando los valores de ordenación empatan, pero difieren en los saltos que dejan después.
Separar el número de fila de la posición compartida
Dos clientes con el mismo importe de compras pueden necesitar números de fila diferentes para mostrarlos, pero la misma posición por importe. Primero calcule un total por cliente y después clasifique esas filas. Cambiar únicamente la función no corrige una entrada formada por líneas de pedido ni una definición de empate incorrecta. Puede separar las etapas mediante WITH y expresiones de tabla comunes (CTE).
| Función | Empates | Secuencia del ejemplo |
|---|---|---|
| ROW_NUMBER | Un número distinto por fila | 1, 2, 3, 4 |
| RANK | Comparte posición y deja saltos | 1, 1, 3, 3 |
| DENSE_RANK | Comparte posición sin dejar saltos | 1, 1, 2, 2 |
Preparar clientes con importes iguales
Conéctese al servidor de prácticas como course_user y seleccione shopcourse. La guía del cliente y los archivos SQL UTF-8 explica cómo guardar el código y ejecutarlo dentro del cliente con SOURCE C:/mariadb-lab/lesson.sql;, sustituyendo la ruta por la real. No introduzca el SQL directamente en la consola del sistema operativo.
Compruebe SELECT VERSION(), DATABASE(); y SHOW TABLES; antes de crear c32_customers, c32_products, c32_orders y c32_items. Son nombres exclusivos de este ejercicio. Si ya existen, inspeccione sus definiciones y datos antes de decidir si puede borrarlos; no sustituya tablas de una aplicación. Compruebe que su versión admite la sintaxis de funciones de ventana utilizada.
USE shopcourse;
CREATE TABLE c32_customers (
customer_id INT NOT NULL PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL,
city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c32_products (
product_id INT NOT NULL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c32_orders (
order_id INT NOT NULL PRIMARY KEY,
customer_id INT NOT NULL,
ordered_at DATETIME NOT NULL,
status VARCHAR(20) NOT NULL,
shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
CONSTRAINT fk_c32_order_customer FOREIGN KEY (customer_id)
REFERENCES c32_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c32_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (order_id, product_id),
CONSTRAINT fk_c32_item_order FOREIGN KEY (order_id)
REFERENCES c32_orders(order_id) ON DELETE RESTRICT,
CONSTRAINT fk_c32_item_product FOREIGN KEY (product_id)
REFERENCES c32_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c32_customers VALUES
(1,'김민수','서울'),(2,'이서연','부산'),
(3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c32_products VALUES
(1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
(3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c32_orders
(order_id,customer_id,ordered_at,status,shipping_fee) VALUES
(101,1,'2026-01-10 10:00:00','paid',1000),
(102,1,'2026-01-20 11:00:00','paid',1000),
(103,2,'2026-02-05 12:00:00','paid',1000),
(104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c32_items VALUES
(101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
(103,1,1,3500),(103,3,2,8000),(104,2,1,1500);
Se conservan los nombres y las ciudades originales en coreano. Los productos 1–4 son un cuaderno, un bolígrafo, una taza y una lámpara. Los clientes 1 y 3 comparten Seúl (서울); los clientes 2 y 4 viven en Busan y Daejeon. Mantenga estos literales UTF-8 para reproducir los resultados.
El cliente 1 suma 11500.00 y 8000.00 en sus dos pedidos pagados; el cliente 2 suma 19500.00 en uno. Ambos tienen, por tanto, 19500.00. El cliente 3 solo tiene un pedido cancelado y el 4 no tiene pedidos. El informe incluye a ambos con un total pagado de cero: incluir a quienes no han comprado y excluir los importes cancelados son decisiones de esta consulta.
Agregar por cliente y comparar las tres funciones
WITH customer_totals AS (
SELECT c.customer_id,c.customer_name,
COALESCE(SUM(CASE WHEN o.status='paid'
THEN i.quantity*i.unit_price ELSE 0 END),0) AS goods_total
FROM c32_customers c LEFT JOIN c32_orders o
ON o.customer_id=c.customer_id
LEFT JOIN c32_items i ON i.order_id=o.order_id
GROUP BY c.customer_id,c.customer_name
)
SELECT customer_id,customer_name,goods_total,
ROW_NUMBER() OVER(ORDER BY goods_total DESC,customer_id ASC) AS row_no,
RANK() OVER(ORDER BY goods_total DESC) AS amount_rank,
DENSE_RANK() OVER(ORDER BY goods_total DESC) AS dense_amount_rank
FROM customer_totals ORDER BY goods_total DESC,customer_id ASC;
Los LEFT JOIN conservan a los clientes aunque falten pedidos o líneas. CASE suma importes únicamente cuando el pedido está pagado y COALESCE convierte en cero un total ausente. GROUP BY genera una fila por cliente antes de aplicar las funciones de ventana. El importe utiliza los precios de las líneas y no incluye gastos de envío.
| Cliente | Total | row_no | amount_rank | dense_amount_rank |
|---|---|---|---|---|
| 1 | 19500.00 | 1 | 1 | 1 |
| 2 | 19500.00 | 2 | 1 | 1 |
| 3 | 0.00 | 3 | 3 | 2 |
| 4 | 0.00 | 4 | 3 | 2 |
ROW_NUMBER ordena por importe descendente y utiliza customer_id como desempate único. En cambio, RANK y DENSE_RANK ordenan solo por importe para que los importes iguales compartan posición. Si añadiera el ID único a sus definiciones de orden, las combinaciones serían diferentes y desaparecerían los empates deseados. Tras los dos primeros empatados, RANK continúa en 3 y DENSE_RANK en 2.
Distinguir el orden de ventana, la presentación y las particiones
ORDER BY dentro de OVER establece el orden del cálculo. El ORDER BY del SELECT final establece el orden de presentación. Escriba ambos cuando necesite definir ambas cosas: la ordenación de una ventana no garantiza por sí sola cómo se mostrarán las filas.
PARTITION BY divide las filas en grupos donde el cálculo vuelve a empezar. Para numerar clientes por ciudad, la etapa agregada debe proporcionar city. A diferencia de GROUP BY, la partición de ventana no reduce las filas de clientes a una sola por ciudad.
La siguiente consulta completa usa las mismas tablas y conserva una fila por cliente. Numera dentro de cada ciudad y muestra el resultado por ID de cliente.
WITH customer_totals AS (
SELECT c.customer_id, c.customer_name, c.city,
COALESCE(SUM(CASE WHEN o.status='paid'
THEN i.quantity*i.unit_price ELSE 0 END),0) AS goods_total
FROM c32_customers c
LEFT JOIN c32_orders o ON o.customer_id=c.customer_id
LEFT JOIN c32_items i ON i.order_id=o.order_id
GROUP BY c.customer_id,c.customer_name,c.city
)
SELECT customer_id,city,goods_total,
ROW_NUMBER() OVER (PARTITION BY city
ORDER BY goods_total DESC,customer_id ASC) AS regional_row_no
FROM customer_totals ORDER BY customer_id;
| customer_id | goods_total | regional_row_no |
|---|---|---|
| 1 | 19500.00 | 1 |
| 2 | 19500.00 | 1 |
| 3 | 0.00 | 2 |
| 4 | 0.00 | 1 |
Los clientes 1 y 3 comparten la partición de Seúl y reciben 1 y 2. Los clientes 2 y 4 comienzan cada uno otra partición en 1. El SELECT también muestra la ciudad coreana almacenada, omitida aquí para destacar los números. Quite únicamente PARTITION BY city para comparar una numeración global y vuelva a añadirlo para recuperar la numeración por ciudad.
Filtrar la clasificación en una consulta exterior
WHERE del mismo SELECT no puede utilizar directamente el row_no que se está calculando. Obtenga la clasificación en otra CTE o en una tabla derivada y filtre después desde fuera. «Dos filas» y «todos los empatados en primer lugar» son peticiones distintas; elija la columna calculada que expresa la condición.
WITH customer_totals AS (
SELECT c.customer_id, c.customer_name, c.city,
COALESCE(SUM(CASE WHEN o.status='paid'
THEN i.quantity*i.unit_price ELSE 0 END),0) AS goods_total
FROM c32_customers c
LEFT JOIN c32_orders o ON o.customer_id=c.customer_id
LEFT JOIN c32_items i ON i.order_id=o.order_id
GROUP BY c.customer_id,c.customer_name,c.city
), ranked AS (
SELECT customer_id,customer_name,goods_total,
ROW_NUMBER() OVER (ORDER BY goods_total DESC,customer_id) AS row_no,
RANK() OVER (ORDER BY goods_total DESC) AS amount_rank
FROM customer_totals
)
SELECT customer_id,goods_total,row_no,amount_rank
FROM ranked WHERE row_no <= 2 ORDER BY row_no;
El resultado contiene a los clientes 1 y 2, con números de fila 1 y 2 y posición 1 para ambos. Cambie solo la condición exterior a row_no <= 1: queda el cliente 1. Con amount_rank = 1 permanecen los dos clientes empatados. Restaure row_no <= 2 al terminar la comparación.
Si clasificara las líneas de pedido originales, estaría numerando líneas en lugar de clientes. Mantenga visible la separación entre la agregación por cliente y la clasificación de esos totales.
Verificar los empates y la población del informe
Utilice empates deliberados y observe también la posición posterior. En el primer ejemplo, cambie únicamente el desempate de ROW_NUMBER de customer_id ASC a customer_id DESC: los clientes empatados intercambian sus números, mientras que las posiciones basadas solo en el importe siguen iguales. Restaure ASC y compruebe que recupera el resultado inicial. Un número generado para un informe no reemplaza el identificador estable del cliente.
Revise además que los pedidos cancelados no aporten importe y que los clientes sin compras sigan incluidos cuando así lo requiera el informe. La referencia oficial de ROW_NUMBER y la guía oficial de funciones de ventana explican las funciones y la separación entre agrupación, cálculo y filtrado.
Antes de repetir la preparación, compruebe la base seleccionada, las definiciones y los registros; respalde lo que deba conservar. Si las siguientes tablas son únicamente objetos prescindibles de este ejercicio, elimínelas en este orden, primero las hijas y después las referenciadas. DROP TABLE elimina estructuras y datos y no se revierte con un ROLLBACK ordinario; mantenga esta limpieza separada de las pruebas de transacciones.
DROP TABLE shopcourse.c32_items;
DROP TABLE shopcourse.c32_orders;
DROP TABLE shopcourse.c32_products;
DROP TABLE shopcourse.c32_customers;




