Qu'est-ce que la fonction RANK() en SQL, et comment l'utiliser ?

Qui sont vos 10 meilleurs clients ? Quels sont les employés les plus performants de chaque service ? Pour répondre à ce genre de questions, vous devez classer les résultats de votre requête SQL. Voyons comment la fonction RANK() aide à classer les résultats en SQL.

Il existe de nombreux cas d'utilisation dans lesquels vous pouvez avoir besoin de classer des lignes en SQL. Vous pouvez par exemple classer les clients en fonction du volume des ventes, les étudiants en fonction de leurs résultats aux examens, les actifs d'une entreprise en fonction de leur valeur actuelle, les départements en fonction du nombre d'employés ou les utilisateurs en fonction de leur date d'enregistrement.

Le classement est un outil important et courant dans l'analyse des données. Dans ce tutoriel, je vais fournir des explications détaillées sur la façon de classer des lignes en SQL.

Introduction à la fonction RANK()

La fonction RANK() est l'une des fonctions fenêtre de SQL. Les fonctions fenêtres examinent une partie des données et calculent les résultats pour cette partie.

La fonction RANK(), en particulier, attribue un rang à chaque ligne en fonction d'une colonne fournie. RANK() est incluse dans l'instruction SELECT avec la syntaxe suivante :

RANK() OVER (ORDER BY column ASC|DESC)

En gros, vous ajoutez une autre colonne à votre ensemble de résultats. Cette colonne comprend le rang de chaque enregistrement en fonction de l'ordre spécifié après le mot-clé ORDER BY. Cela implique de spécifier (1) la colonne à utiliser pour trier les rangs et (2) si l'ordre doit être croissant ou décroissant.

La première ligne obtient le rang 1, et les lignes suivantes obtiennent des rangs supérieurs. Si des lignes ont la même valeur dans la colonne utilisée pour le tri, elles sont classées de la même façon. La fonction RANK() laisse des vides dans de tels cas.

Par exemple, si les première et deuxième lignes ont la même valeur dans la colonne fournie, elles obtiennent toutes deux le rang 1. Ensuite, la troisième ligne obtient le rang 3 (c'est-à-dire que le rang 2 sera manquant).

Pour vous familiariser avec la fonction RANK(), consultez notre cours interactif Fonctions de fenêtrage interactif. Il couvre également d'autres fonctions de classement qui ont des approches différentes pour traiter les mêmes valeurs dans la colonne fournie.

Voyons maintenant la fonction RANK() en action.

Classement de base

Pour voir comment la fonction RANK() fonctionne en pratique, nous allons prendre un exemple. Imaginons que nous ayons une équipe de développeurs. Nous voulons les classer en fonction de leur expérience. Voici le tableau avec lequel nous commençons :

Maintenant, notre tâche est d'afficher le prénom, le nom, le niveau d'ancienneté, les années d'expérience et le rang des développeurs. Nous voulons que le développeur le plus expérimenté obtienne le rang 1.

Voici notre requête SQL ainsi que le jeu de résultats :

SELECT first_name, last_name, level, years_experience,
       RANK() OVER (ORDER BY years_experience DESC)
FROM developers;

Pour classer les développeurs en fonction de leur expérience, nous avons inclus le champ suivant dans l'instruction SELECT:

RANK() OVER (ORDER BY years_experience DESC)

Nous définissons l'ordre après la clause ORDER BY en spécifiant la colonne à utiliser pour le tri (years_experience) et l'ordre (décroissant). Ensuite, nous classons les développeurs en fonction de cet ordre.

Nous voyons que le développeur avec 8 ans d'expérience obtient le rang 1, le développeur avec 5 ans d'expérience obtient le rang 2, et les deux développeurs avec 4 ans d'expérience obtiennent tous deux le rang 3. Notez que la fonction RANK() laisse un vide ici, et les développeurs suivants obtiennent le rang 5 (c'est-à-dire que le rang 4 est manquant).

Vous avez maintenant une idée du fonctionnement de la fonction RANK(), notamment de la manière dont elle traite les mêmes valeurs dans la colonne utilisée pour le tri. Vous trouverez d'autres cas d'utilisation dans ce guide complet du classement des lignes en SQL.

Nous allons maintenant passer à des exemples plus avancés.

Classement au sein de différents groupes

Dans notre premier exemple, nous avons classé l'ensemble des résultats. Maintenant, disons que nous voulons avoir un classement distinct pour chacun des niveaux d'ancienneté (c'est-à-dire junior, moyen et senior).

Avec la fonction RANK() de SQL, nous pouvons le faire en ajoutant le mot-clé PARTITION BY et en spécifiant la colonne à utiliser pour regrouper les enregistrements. Voici notre requête SQL :

SELECT first_name, last_name, level, years_experience,
       RANK() OVER (PARTITION BY level ORDER BY years_experience    DESC)
FROM developers;

Dans la clause OVER(), nous définissons d'abord comment regrouper les enregistrements (en utilisant la colonne level ), puis comment ordonner les enregistrements au sein de ces groupes (en fonction des années d'expérience, en commençant par le développeur le plus expérimenté).

Voici le tableau qui en résulte :

Remarquez que nous avons des classements distincts pour les niveaux junior, moyen et senior. Pourtant, le développeur le plus expérimenté de chaque groupe est classé au rang 1, et les développeurs moins expérimentés sont mieux classés.

Notez également que si Kate Williams et Max Weber ont les mêmes 4 années d'expérience, Max obtient le rang 1 en tant que développeur de niveau intermédiaire le plus expérimenté, tandis que Kate obtient le rang 3 en tant que développeur senior le moins expérimenté.

Maintenant que vous connaissez le fonctionnement de PARTITION BY, il est temps de découvrir les autres fonctions de classement.

Autres fonctions de classement en SQL

Pour classer des lignes en SQL, vous pouvez également utiliser les fonctions DENSE_RANK() et ROW_NUMBER(). La différence réside dans la manière dont ces fonctions traitent les mêmes valeurs.

Comme nous l'avons appris précédemment, la fonction RANK() donne le même rang aux enregistrements ayant la même valeur et laisse un écart pour tenir compte de ces enregistrements. En revanche :

• la fonction DENSE_RANK() ne laisse pas d'écart ;
• la fonction ROW_NUMBER() classe différemment tous les enregistrements, même si les valeurs sont les mêmes.

Il est plus facile de comprendre cette différence à l'aide d'un exemple. Revenons donc à notre premier cas d'utilisation dans lequel nous avons classé l'ensemble des résultats. Mais, cette fois, nous allons également utiliser DENSE_RANK() et ROW_NUMBER():

SELECT first_name, last_name, level, years_experience,
       RANK() OVER (ORDER BY years_experience DESC),
       DENSE_RANK() OVER (ORDER BY years_experience DESC),
       ROW_NUMBER() OVER (ORDER BY years_experience DESC)
FROM developers;

Comme la fonction RANK(), la fonction DENSE_RANK() donne le même rang aux développeurs ayant les mêmes années d'expérience (par exemple, le rang 3 à Kate Williams et Max Weber). Cependant, le rang suivant dans la colonne dense_rank est 4, ce qui ne laisse aucun vide dans le classement.

D'autre part, la fonction ROW_NUMBER() attribue un numéro différent à tous les développeurs, y compris à ceux qui ont les mêmes années d'expérience. Alors, pourquoi Max obtient-il le rang 3 et Kate le rang 4 ? Il s'agit d'un choix arbitraire, et vous ne savez jamais lequel des enregistrements ayant la même valeur se verra attribuer un rang supérieur.

Maintenant que vous connaissez la différence entre les fonctions de classement, vous pouvez décider laquelle est la plus adaptée à vos cas d'utilisation. Si vous n'êtes pas sûr, voyez d'autres exemples des fonctions RANK(), DENSE_RANK() et ROW_NUMBER() dans cet article de présentation.

Il est temps de pratiquer les fonctions de classement !

Les fonctions de classement font partie des outils les plus couramment utilisés par les analystes de données. Vous devez donc vous sentir à l'aise avec le classement en SQL. Et la confiance vient avec la pratique.

Consultez ce Fonctions de fenêtrage qui comprend 218 exercices interactifs couvrant les fonctions de classement, les fonctions analytiques, les calculs statistiques avancés avec différents cadres de fenêtre, etc. Vous pouvez en savoir plus sur ce cours dans cet article.

Et voici un bonus : cette fiche de deux pages sur SQL Fonctions de fenêtrage vous aidera à vous entraîner au classement et à d'autres fonctions de fenêtre.

Merci de votre lecture et bon apprentissage !