Fonctions window de base : ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()

Dans le cours précédent, on a pigé pourquoi on a besoin des fonctions window. Maintenant, on va voir des fonctions concrètes et leurs résultats. Les détails de la syntaxe, on les verra dans le prochain cours.

Fonction ROW_NUMBER()

La fonction ROW_NUMBER() renvoie un numéro unique pour chaque ligne dans la window. C’est juste une numérotation des lignes selon l’ordre défini dans ORDER BY.

Syntaxe :

ROW_NUMBER() OVER ([PARTITION BY colonne] ORDER BY colonne)

Où :

• PARTITION BY colonne (optionnel) : divise les données en groupes. Si tu le zappes, la numérotation sera globale sur tout le set.
• ORDER BY colonne : définit l’ordre des lignes pour la numérotation.

Exemple. Numérotation des lignes dans une table

Regardons la table students, qui contient des infos sur les étudiants et leurs notes.

SELECT * FROM students;

Maintenant, on va numéroter les lignes par ordre décroissant de leurs notes (score) :

SELECT
    name, 
    score, 
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
FROM students;

Résultat :

Chaque ligne a reçu un numéro d’ordre unique — en tenant compte du tri décroissant des notes.

C’est une opération simple mais super puissante — ajouter un numéro de ligne dans le résultat d’une requête. En SELECT classique, tu peux pas faire ça sans les fonctions window.

Fonction RANK()

La fonction RANK() ressemble beaucoup à ROW_NUMBER(), mais elle prend en compte les valeurs identiques. Si des lignes ont la même valeur dans le tri, elles auront le même rang, et le suivant sera sauté.

Syntaxe :

RANK() OVER ([PARTITION BY colonne] ORDER BY colonne)

Exemple. Classement des étudiants par leurs notes

Utilisons RANK() sur les mêmes données :

SELECT
    name, 
    score, 
    RANK() OVER (ORDER BY score DESC) AS rank
FROM students;

Résultat :

Ici, les lignes avec les mêmes valeurs (95 et 87) ont le même rang, et les rangs suivants sont sautés.

Fonction DENSE_RANK()

DENSE_RANK() ressemble à RANK(), mais ne saute pas les valeurs de rang. Ça veut dire que s’il y a des lignes identiques, le rang suivant sera juste +1 par rapport au précédent.

Syntaxe :

DENSE_RANK() OVER ([PARTITION BY colonne] ORDER BY colonne)

Exemple. Classement dense

On utilise DENSE_RANK() sur les mêmes données :

SELECT
    name, 
    score, 
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM students;

Résultat :

Ici, contrairement à RANK(), les valeurs de rang augmentent sans trous.

Fonction NTILE()

La fonction NTILE() divise les lignes en groupes égaux (quantiles) et donne à chaque ligne un numéro de groupe.

Syntaxe :

NTILE(n) OVER ([PARTITION BY colonne] ORDER BY colonne)

• n : le nombre de groupes dans lesquels tu veux diviser les données.

Exemple. Découpage des étudiants en 3 groupes

On va diviser les étudiants en 3 groupes par ordre décroissant de notes :

SELECT
    name, 
    score, 
    NTILE(3) OVER (ORDER BY score DESC) AS group_num
FROM students;

Résultat :

À noter : si on ne peut pas diviser les lignes en groupes parfaitement égaux, les lignes en trop vont dans les premiers groupes. Ici, les deux premiers groupes ont deux lignes, le dernier en a une.

Quand utiliser quelle fonction ?

• ROW_NUMBER() : pour numéroter de façon unique les lignes selon un ordre de tri.
• RANK() : pour classer en tenant compte des valeurs identiques et en sautant le rang suivant.
• DENSE_RANK() : pour classer en tenant compte des valeurs identiques sans sauter de rangs.
• NTILE() : Pour découper les lignes en groupes de façon équilibrée.

Toutes ces fonctions te permettent d’analyser tes données à un tout autre niveau. Utilise-les dès que tu as besoin de flexibilité pour calculer des numéros d’ordre ou découper tes données.