Niveau 17 : Fonctions de fenêtre : classer

Leçon

GROUP BY résume : il fusionne les lignes d'un groupe en une seule. Parfois, on veut au contraire garder toutes les lignes et ajouter à chacune une information qui dépend des autres : son rang, son numéro, sa position dans le groupe. C'est le rôle des fonctions de fenêtre, qui se reconnaissent au mot OVER.

La « fenêtre », c'est l'ensemble des lignes que la fonction regarde pour calculer la valeur de la ligne en cours. OVER (ORDER BY rating DESC) signifie : regarde toutes les lignes, rangées par note décroissante.

Trois fonctions classent les lignes. ROW_NUMBER() numérote 1, 2, 3… sans jamais répéter un numéro. RANK() donne le même rang aux ex æquo puis saute des places : 1, 2, 2, 4. DENSE_RANK() donne aussi le même rang aux ex æquo, mais sans trou : 1, 2, 2, 3. Dans staff, Farid et Iris gagnent tous deux 47000 : RANK et DENSE_RANK les placent au même rang, ROW_NUMBER les sépare.

En cas d'égalité, ROW_NUMBER départage les ex æquo dans un ordre arbitraire, qui peut changer. Ajoute une colonne de départage (ORDER BY salary DESC, name) pour obtenir un résultat stable.

PARTITION BY découpe les lignes en groupes, et le calcul recommence dans chaque groupe : RANK() OVER (PARTITION BY department ORDER BY salary DESC) classe les employés à l'intérieur de leur service. Chaque service a son numéro 1.

Le ORDER BY écrit dans OVER sert au calcul, pas à l'affichage : pour trier le résultat, ajoute un ORDER BY final.

Les fonctions de fenêtre sont calculées après WHERE, GROUP BY et HAVING, juste avant le ORDER BY final. Un WHERE ne peut donc pas filtrer sur un rang : il n'existe pas encore. Pour garder « le premier de chaque service », on calcule le rang dans une CTE, puis on filtre dessus : c'est la méthode du « top N par groupe ».

Syntaxe

SELECT col, RANK() OVER (PARTITION BY groupe ORDER BY valeur DESC) AS rang
FROM ma_table;

Exemple commenté

SELECT title, rating, RANK() OVER (ORDER BY rating DESC) AS rk
FROM movies;

Chaque film reçoit son rang selon sa note, et les 10 films restent présents.

Résultat de l’exemple

À retenir

• OVER (…) signale une fonction de fenêtre.
• ROW_NUMBER : numéros uniques ; RANK : ex æquo puis trou ; DENSE_RANK : ex æquo sans trou.
• PARTITION BY = « dans chaque groupe ».

Pièges fréquents

• WHERE RANK() OVER (…) = 1 est interdit : passe par une CTE.
• Le ORDER BY dans OVER classe les lignes pour le calcul ; il ne trie pas le résultat final.
• ROW_NUMBER sans départage peut changer de gagnant d'une exécution à l'autre en cas d'égalité.

Pour aller plus loin

NTILE(4) OVER (ORDER BY salary) répartit les lignes en 4 groupes de même taille (quartiles) ; PERCENT_RANK donne la position relative de chaque ligne, entre 0 et 1.

Les 5 exercices du niveau

1. Guidé · Affiche le titre et la date de sortie des chansons, avec une colonne n qui les numérote de la plus ancienne (1) à la plus récente. (Table : songs)
2. Entraînement · Affiche le nom et le salaire des employés avec leur rang RANK et leur rang DENSE_RANK, du salaire le plus élevé au plus bas. (Table : staff)
3. Entraînement · Affiche le nom, le service et le salaire de chaque employé avec son rang de salaire à l'intérieur de son service (1 = le mieux payé du service). (Table : staff)
4. Entraînement · Affiche l'employé le mieux payé de chaque service (nom, service, salaire). (Table : staff)
5. Défi · Pour chaque course (race), affiche les deux coureurs les plus rapides : course, nom du coureur et temps, triés par course puis par classement. (Tables : results, runners)

Dans SpeedQL, chaque requête est corrigée tout de suite : le résultat est comparé à celui attendu, puis la requête est relancée sur une base de contrôle cachée. Chaque exercice a ses indices écrits, à afficher seulement si tu bloques.

Voir aussi : la fiche de l’aide-mémoire · les exercices SQL corrigés sur cette notion