%% nav-debut : généré par gen_navigation.py, ne pas modifier à la main %% > [!nav] > ↩ [[Code et Développement/Développement d'Applications/SQL IV/SQL IV|Accueil · SQL IV]] > > | [[Code et Développement/Développement d'Applications/SQL IV/10_Calculer sur un groupe - PARTITION BY\|← Calculer sur un groupe : PARTITION BY]] | [[Code et Développement/Développement d'Applications/SQL IV/12_Cumuls et ligne précédente - LAG\|Cumuls et ligne précédente : LAG →]] | > |:---|---:| %% nav-fin %% [[Code et Développement/Développement d'Applications/SQL IV/10_Calculer sur un groupe - PARTITION BY|Calculer sur un groupe - PARTITION BY]] # Classer : `RANK`, `DENSE_RANK`, `ROW_NUMBER` ## Introduction « Le classement des clients », « les 3 meilleurs », « la commande la plus chère de chaque client »… Pour répondre, il faut **attribuer un rang** à chaque enregistrement. Trois fonctions de fenêtre le font, avec une différence importante : **la façon de traiter les ex æquo**. ### Objectifs du chapitre À la fin de ce chapitre, vous serez capable de : - Numéroter et classer des enregistrements avec `ROW_NUMBER`, `RANK` et `DENSE_RANK`. - Choisir la bonne fonction selon le traitement des ex æquo. - Obtenir « les n premiers » **en gardant les ex æquo**. - Classer **à l'intérieur de chaque groupe** avec `PARTITION BY`. Nous utilisons les tables *Clients* et *Commandes* présentées au chapitre [[Code et Développement/Développement d'Applications/SQL IV/01_Supprimer les doublons - DISTINCT|Supprimer les doublons : DISTINCT]], et la notion de fonction de fenêtre vue au chapitre [[Code et Développement/Développement d'Applications/SQL IV/10_Calculer sur un groupe - PARTITION BY|Calculer sur un groupe : PARTITION BY]]. --- ## 1. Classer les clients On nous demande : ***le classement des clients selon leur nombre de commandes (du plus grand au plus petit), avec leur rang.*** ### Ce que vous savez déjà faire (SQL I) On compte les commandes de chaque client (en gardant Robert grâce au `LEFT JOIN`), puis on trie : ```sql SELECT C.nom_client, COUNT(Co.id_commande) AS nb_commandes FROM Clients C LEFT JOIN Commandes Co ON C.id_client = Co.id_client GROUP BY C.id_client, C.nom_client ORDER BY nb_commandes DESC; ``` | nom_client | nb_commandes | | ---------- | ------------ | | Dupont | 3 | | Martin | 2 | | Bernard | 1 | | Petit | 1 | | Robert | 0 | Les clients sont dans le bon ordre, mais **le rang n'est pas affiché** : c'est au lecteur de compter les lignes, et de décider quoi faire pour Bernard et Petit, ex æquo. ### Une autre solution : les fonctions de classement ```sql SELECT C.nom_client, COUNT(Co.id_commande) AS nb_commandes, ROW_NUMBER() OVER (ORDER BY COUNT(Co.id_commande) DESC) AS num, RANK() OVER (ORDER BY COUNT(Co.id_commande) DESC) AS rang, DENSE_RANK() OVER (ORDER BY COUNT(Co.id_commande) DESC) AS rang_dense FROM Clients C LEFT JOIN Commandes Co ON C.id_client = Co.id_client GROUP BY C.id_client, C.nom_client ORDER BY nb_commandes DESC; ``` | nom_client | nb_commandes | num | rang | rang_dense | | ---------- | ------------ | --- | ---- | ---------- | | Dupont | 3 | 1 | 1 | 1 | | Martin | 2 | 2 | 2 | 2 | | Bernard | 1 | 3 | 3 | 3 | | Petit | 1 | 4 | 3 | 3 | | Robert | 0 | 5 | **5**| **4** | Deux nouveautés : 1. Les fonctions `ROW_NUMBER()`, `RANK()` et `DENSE_RANK()` n'ont **rien entre leurs parenthèses**. 2. Dans le `OVER`, **`ORDER BY` indique l'ordre du classement** : ici, du plus grand nombre de commandes au plus petit. Ce `ORDER BY` ne trie pas le résultat affiché : c'est le `ORDER BY` de la fin de la requête qui s'en charge. > [!note] Une fonction de fenêtre sur un `COUNT` ? > C'est possible car les fonctions de fenêtre sont calculées au `SELECT`, **après** le `GROUP BY` (voir le chapitre [[Code et Développement/Développement d'Applications/SQL IV/10_Calculer sur un groupe - PARTITION BY|Calculer sur un groupe : PARTITION BY]]). À ce moment, chaque enregistrement est un client, et son `COUNT` est connu : on peut classer les clients selon ce nombre. --- ## 2. Trois fonctions, trois façons de traiter les ex æquo | Fonction | Bernard et Petit (ex æquo) | Robert (le suivant) | À utiliser quand… | | --- | --- | --- | --- | | `ROW_NUMBER()` | 3 et 4 : **numéros différents**, attribués au hasard entre eux | 5 | on veut un numéro unique par ligne, sans se soucier des ex æquo | | `RANK()` | 3 et 3 : **même rang** | 5 : on **saute** le rang 4 | classement « sportif » : deux 3ᵉ, pas de 4ᵉ | | `DENSE_RANK()` | 3 et 3 : **même rang** | 4 : **pas de trou** | on veut des rangs qui se suivent | > [!warning] `ROW_NUMBER` et les ex æquo > Comme `LIMIT` (voir le chapitre [[Code et Développement/Développement d'Applications/SQL IV/02_Garder les premières lignes - LIMIT|Garder les premières lignes : LIMIT]]), `ROW_NUMBER` ne connaît pas les ex æquo : entre Bernard et Petit, on ne sait pas qui aura le 3 et qui aura le 4. Pour un classement, préférez `RANK` ou `DENSE_RANK`. --- ## 3. Les n premiers, ex æquo compris On nous demande : ***les 3 clients qui ont passé le plus de commandes.*** ### Ce que vous savez déjà faire (chapitre 02) ```sql SELECT C.nom_client, COUNT(*) AS nb_commandes FROM Clients C INNER JOIN Commandes Co ON C.id_client = Co.id_client GROUP BY C.id_client, C.nom_client ORDER BY nb_commandes DESC LIMIT 3; ``` On obtient Dupont, Martin… et **un seul** des deux ex æquo, Bernard ou Petit. C'est le piège vu au chapitre 02. ### Une autre solution : `RANK` dans une sous-requête On calcule le rang dans une sous-requête placée dans le `FROM`, puis on garde les rangs 1 à 3. On ne peut pas filtrer directement dans le `WHERE` : le rang est calculé au `SELECT` (voir le chapitre [[Code et Développement/Développement d'Applications/SQL IV/10_Calculer sur un groupe - PARTITION BY|Calculer sur un groupe : PARTITION BY]]). ```sql SELECT nom_client, nb_commandes, rang FROM ( SELECT C.nom_client, COUNT(*) AS nb_commandes, RANK() OVER (ORDER BY COUNT(*) DESC) AS rang FROM Clients C INNER JOIN Commandes Co ON C.id_client = Co.id_client GROUP BY C.id_client, C.nom_client ) AS t WHERE rang <= 3 ORDER BY rang, nom_client; ``` | nom_client | nb_commandes | rang | | ---------- | ------------ | ---- | | Dupont | 3 | 1 | | Martin | 2 | 2 | | Bernard | 1 | 3 | | Petit | 1 | 3 | Les deux ex æquo sont présents : le résultat contient 4 lignes, et c'est normal. > [!tip] « Les n premiers » : la méthode > - La demande porte sur un **nombre de lignes** (« affichez 10 commandes ») → `ORDER BY` + `LIMIT`. > - La demande porte sur un **classement** (« les 3 meilleurs clients ») → `RANK()` dans une sous-requête, puis `WHERE rang <= 3`. > > On peut aussi garder les ex æquo avec `MAX` ou `MIN` dans une sous-requête (voir le chapitre [[Code et Développement/Développement d'Applications/SQL IV/06_Sous-requête renvoyant une valeur|Sous-requête renvoyant une valeur]]), mais cela ne fonctionne que pour **le** premier, pas pour **les 3** premiers. --- ## 4. Classer à l'intérieur de chaque groupe En ajoutant `PARTITION BY` avant le `ORDER BY`, le classement **recommence à 1 pour chaque groupe**. On nous demande : ***pour chaque client, quelle est sa commande la plus chère ?*** ### Ce que vous savez déjà faire (SQL III et chapitre 08) On joint *Commandes* à une sous-requête qui calcule le montant maximal de chaque client (voir le chapitre [[Code et Développement/Développement d'Applications/SQL IV/08_Tester l'existence - EXISTS|Tester l'existence : EXISTS]], point 6) : ```sql SELECT Co.id_client, Co.id_commande, Co.montant_total FROM Commandes Co INNER JOIN ( SELECT id_client, MAX(montant_total) AS montant_max FROM Commandes GROUP BY id_client ) AS m ON Co.id_client = m.id_client AND Co.montant_total = m.montant_max ORDER BY Co.id_client; ``` ### Une autre solution : `RANK` avec `PARTITION BY` ```sql SELECT id_client, id_commande, montant_total, rang FROM ( SELECT id_client, id_commande, montant_total, RANK() OVER (PARTITION BY id_client ORDER BY montant_total DESC) AS rang FROM Commandes ) AS t WHERE rang = 1 ORDER BY id_client; ``` | id_client | id_commande | montant_total | rang | | --------- | ----------- | ------------- | ---- | | 1 | 101 | 250.00 | 1 | | 2 | 103 | 560.00 | 1 | | 3 | 106 | 150.00 | 1 | | 4 | 107 | 400.00 | 1 | Lisez `RANK() OVER (PARTITION BY id_client ORDER BY montant_total DESC)` ainsi : *« le rang de cette commande parmi **les commandes du même client**, de la plus chère à la moins chère »*. ### Là où les partitions deviennent indispensables On nous demande maintenant : ***pour chaque client, ses 2 commandes les plus chères.*** Avec `MAX`, ce n'est plus possible simplement : `MAX` ne donne que **la** plus chère. Avec `RANK`, il suffit de changer le filtre : ```sql SELECT id_client, id_commande, montant_total, rang FROM ( SELECT id_client, id_commande, montant_total, RANK() OVER (PARTITION BY id_client ORDER BY montant_total DESC) AS rang FROM Commandes ) AS t WHERE rang <= 2 ORDER BY id_client, rang; ``` | id_client | id_commande | montant_total | rang | | --------- | ----------- | ------------- | ---- | | 1 | 101 | 250.00 | 1 | | 1 | 102 | 120.00 | 2 | | 2 | 103 | 560.00 | 1 | | 2 | 105 | 310.00 | 2 | | 3 | 106 | 150.00 | 1 | | 4 | 107 | 400.00 | 1 | Les clients 3 et 4 n'ont qu'une commande : ils n'ont qu'une ligne. --- > [!important] Ce qu'il faut retenir > - `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()` attribuent un rang selon le `ORDER BY` écrit **dans** le `OVER`. > - Ex æquo : `ROW_NUMBER` les sépare au hasard ; `RANK` leur donne le même rang et saute les suivants ; `DENSE_RANK` leur donne le même rang sans trou. > - Pour filtrer sur un rang : la fonction dans une sous-requête du `FROM`, puis `WHERE rang <= n`. > - `PARTITION BY` + `ORDER BY` dans le `OVER` : un classement par groupe, qui recommence à 1 pour chaque groupe. Prochaine étape, les cumuls et la comparaison avec la ligne précédente : [[Code et Développement/Développement d'Applications/SQL IV/12_Cumuls et ligne précédente - LAG|Cumuls et ligne précédente : LAG]] %% nav-debut : généré par gen_navigation.py, ne pas modifier à la main %% > [!nav] > ↩ [[Code et Développement/Développement d'Applications/SQL IV/SQL IV|Accueil · SQL IV]] > > | [[Code et Développement/Développement d'Applications/SQL IV/10_Calculer sur un groupe - PARTITION BY\|← Calculer sur un groupe : PARTITION BY]] | [[Code et Développement/Développement d'Applications/SQL IV/12_Cumuls et ligne précédente - LAG\|Cumuls et ligne précédente : LAG →]] | > |:---|---:| %% nav-fin %% --- 🗓️ Historique Dernière mise à jour : `30 octobre 2026` Rédigé par : [[Vincent TELLIER]]