Cours MariaDB / Comparer ROW_NUMBER, RANK et DENSE_RANK
Les fonctions de fenêtre de MariaDB ajoutent un calcul à chaque ligne du résultat, sans regrouper ces lignes en une seule. ROW_NUMBER() attribue un numéro distinct à chaque ligne ; RANK() et DENSE_RANK() donnent le même rang aux ex æquo, mais seul RANK laisse des trous après eux.
Choisir un numéro de ligne ou un rang partagé
Deux clients ayant acheté pour le même montant peuvent recevoir des numéros de ligne différents dans un écran tout en partageant le premier rang commercial. Définissez donc la règle d’égalité avant de choisir la fonction. Ici, nous calculons d’abord un montant par client, puis classons ces lignes clientes.
| Fonction | Traitement des montants égaux | Exemple avec deux paires d’ex æquo |
|---|---|---|
| ROW_NUMBER | Un numéro différent par ligne | 1, 2, 3, 4 |
| RANK | Même rang ; les positions suivantes sautent | 1, 1, 3, 3 |
| DENSE_RANK | Même rang ; les rangs restent consécutifs | 1, 1, 2, 2 |
Le découpage avec WITH et les expressions de table communes rend visibles les deux étapes : construire les montants, puis numéroter ou classer. Un classement effectué directement sur les articles de commande aurait une autre unité : il classerait des articles au lieu de clients. Changer seulement le nom de la fonction ne corrige pas cette différence.
Préparer des clients avec des montants égaux
Connectez-vous au serveur d’apprentissage avec course_user et utilisez la base shopcourse. Le compte doit pouvoir créer, remplir, lire et nettoyer les tables d’exercice. Le guide du client MariaDB et des fichiers SQL UTF-8 présente la connexion et l’exécution d’un fichier.
- Vérifiez le serveur et la base avec
SELECT VERSION(), DATABASE();, puis les noms existants avecSHOW TABLES;. Consultez la documentation correspondant à la version du serveur si les fonctions de fenêtre ne sont pas reconnues. - Les quatre noms
c32_customers,c32_products,c32_ordersetc32_itemsappartiennent à cet exercice. S’ils existent, examinez leur définition et leurs données. Ne les nettoyez que s’il s’agit bien d’objets d’exercice sans rien à conserver. - Enregistrez le bloc complet suivant en UTF-8, par exemple dans
C:/mariadb-lab/c32-ranking.sql, après avoir créé le dossier. Dans le client MariaDB, exécutezSOURCE C:/mariadb-lab/c32-ranking.sql;avec le chemin réel. Le SQL se lance dans la connexion au serveur, pas directement dans l’invite du système.
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);
Vous obtenez quatre clients, quatre produits, quatre commandes et six articles de commande. Les textes coréens restent identiques entre les traductions pour reproduire les résultats. Les produits 1 à 4 sont un carnet, un stylo, une tasse et une lampe. Les clients 1 et 3 vivent à 서울 (Séoul), le client 2 à 부산 (Busan), et le client 4 à 대전 (Daejeon). Conservez ces chaînes en UTF-8 et suivez les identifiants.
Les clients 1 et 2 ont chacun un montant de produits payés de 19500.00. Le client 3 n’a qu’une commande annulée, le client 4 aucune commande : nous définissons leur montant payé à zéro. Inclure les non-acheteurs et exclure les commandes annulées constituent la règle de ce rapport, pas une obligation imposée par les fonctions de classement. Les frais de livraison ne sont pas inclus dans le montant des produits.
Comparer les trois fonctions sur les mêmes clients
Exécutez cette instruction complète dans shopcourse :
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;
| customer_id | goods_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 |
La CTE customer_totals conserve chaque client avec LEFT JOIN, puis regroupe ses lignes. Le CASE additionne seulement les articles des commandes paid ; COALESCE prévoit une valeur zéro lorsqu’une somme est nulle. Un filtre WHERE o.status='paid' placé avant ce regroupement éliminerait ici les clients sans commande payée, au lieu de leur attribuer zéro.
ROW_NUMBER utilise le montant décroissant puis l’identifiant croissant comme critère de départage unique. Les clients 1 et 2 gardent donc des numéros différents malgré leur montant égal. Dans RANK et DENSE_RANK, le tri porte seulement sur le montant : l’égalité voulue reste visible.
Si vous ajoutez aussi customer_id au tri de RANK, les combinaisons deviennent différentes et les rangs ne sont plus partagés. Pour une comparaison contrôlée, changez seulement customer_id ASC en customer_id DESC dans le OVER de ROW_NUMBER, en gardant le tri final inchangé : les numéros des clients 1 et 2 s’inversent, tout comme ceux de 3 et 4 ; les deux rangs par montant restent identiques. Rétablissez ensuite ASC.
Distinguer l’ordre du calcul et les partitions
Le ORDER BY placé dans OVER(...) définit le calcul de la fonction. Le ORDER BY final définit l’ordre des lignes affichées. Le premier ne remplace pas le second : indiquez les deux lorsque les deux ordres comptent.
PARTITION BY sépare les groupes dans lesquels le calcul recommence. Il ne réduit pas les quatre lignes clientes comme le ferait un nouveau GROUP BY. La requête complète suivante ajoute la ville à la CTE et recommence la numérotation dans chaque ville :
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 | city | goods_total | regional_row_no |
|---|---|---|---|
| 1 | 서울 | 19500.00 | 1 |
| 2 | 부산 | 19500.00 | 1 |
| 3 | 서울 | 0.00 | 2 |
| 4 | 대전 | 0.00 | 1 |
Les clients 1 et 3 partagent la partition de Séoul et reçoivent 1 et 2. Les clients 2 et 4 sont chacun la première ligne d’une autre ville. Retirez seulement PARTITION BY city : la numérotation devient globale et donne 1, 2, 3 et 4 dans cet affichage. Remettez la partition pour retrouver 1, 1, 2 et 1.
Filtrer les numéros ou les rangs dans une requête extérieure
Le WHERE d’un SELECT ne peut pas filtrer directement son alias de fenêtre nouvellement calculé. Calculez d’abord les numéros dans une CTE ou une table dérivée, puis appliquez le filtre à l’extérieur. La présentation des fonctions de fenêtre explique cet ordre d’évaluation.
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;
Vous obtenez les clients 1 et 2, avec les montants 19500.00, les numéros 1 et 2 et le rang 1 pour chacun. Remplacez uniquement la condition extérieure row_no <= 2 par row_no <= 1 : seul le client 1 reste. Remplacez-la par amount_rank = 1 : les deux clients ex æquo restent. Rétablissez row_no <= 2 après la comparaison.
« Une première ligne » et « tous les premiers ex æquo » sont deux demandes différentes. De même, avec RANK, demander les rangs inférieurs ou égaux à 2 ne garantit ni deux lignes ni deux montants distincts : le rang suivant nos deux premiers est 3. Définissez la limite voulue avant de choisir le champ filtré.
Vérifier les égalités, la population et le nettoyage
- Rangs différents pour deux montants égaux : inspectez toute la liste ORDER BY de la fenêtre ; un identifiant unique supprime l’égalité des combinaisons.
- Clients manquants : vérifiez le maintien des lignes par LEFT JOIN et l’endroit où le statut est filtré avant l’agrégation.
- Trop de lignes : confirmez que la fenêtre reçoit un total par client, plutôt que les articles de commande.
- Erreur sur row_no : placez le filtre sur le résultat déjà calculé dans la requête extérieure, pas dans le WHERE qui précède son calcul.
Un numéro ou un rang peut changer quand les données ou les critères changent. Il décrit ce rapport et ne remplace pas l’identifiant stable du client. Ces SELECT n’altèrent pas les données : vous pouvez refaire les comparaisons et restaurer les critères initiaux sans modifier les tables.
Pour recréer le jeu de départ, vérifiez la base, les définitions et les lignes puis sauvegardez ce qui doit être conservé. Le bloc suivant supprime uniquement les quatre tables d’exercice, des enfants vers leurs parents. La documentation de DROP TABLE décrit la suppression des structures et des données ; un ROLLBACK ordinaire ne les restaure pas.
DROP TABLE shopcourse.c32_items;
DROP TABLE shopcourse.c32_orders;
DROP TABLE shopcourse.c32_products;
DROP TABLE shopcourse.c32_customers;
Réexécutez tout le bloc de préparation puis les trois requêtes complètes pour retrouver les résultats attendus. Les tables d’autres exercices, notamment les noms préfixés c31_, et une éventuelle table générale products ne font pas partie de ce nettoyage.




