Comment utiliser la fonction SQL RANK OVER (PARTITION BY)

Le classement des données en SQL est un jeu d'enfant si vous savez comment utiliser RANK() pour effectuer un classement sur une partition. Cet article vous montre comment procéder, ainsi que les différences entre RANK() et DENSE_RANK() et ROW_NUMBER().

Si vous travaillez avec SQL dans un cadre professionnel, vous avez dû classer des données au moins une fois. Pensez au classement des mois/années/trimestres en fonction des revenus ou des coûts, des produits les plus vendus, des articles les plus consultés ou des chansons les plus écoutées, des employés en fonction de leur salaire, des branches les plus rentables, et j'en passe.

Ou encore à classer les livres en fonction de leurs ventes.

Le fait est que vous devez souvent classer des données au sein d'une certaine catégorie, ou partition comme nous l'appelons. C'est là que SQL RANK OVER (PARTITION BY) entre en jeu !

Il s'agit d'un exemple typique des fonctions de fenêtre en SQL. Pour une explication plus détaillée des fonctions de fenêtre, notre cours sur les fonctions de fenêtre est le meilleur. Grâce à 218 exercices interactifs, vous en apprendrez plus sur PARTITION BY et sur d'autres clauses de fonction fenêtre, telles que ORDER BY, ROWS, et RANGE. Ces fonctions sont utilisées non seulement dans le classement, mais aussi dans les fonctions de fenêtre d'agrégation et d'analyse.

Revenons aux ventes de livres. Jetez un coup d'œil à ce tableau, dont le code de création se trouve ici. Comment classer les ventes de livres dans chaque langue ?

La solution est simple si l'on sait que la langue, dans cet exemple, agit comme une partition de données.

SELECT
  original_language,
  title,
  author,
  sales,
  RANK() OVER (PARTITION BY original_language ORDER BY sales DESC)
    AS sales_rank
FROM books;

Et c'est le cas ici !

Maintenant, décortiquons cet exemple.

Qu'est-ce que RANK() ?

En termes simples, RANK() est une fonction de fenêtre.

Les fonctions de fenêtre SQL sont similaires aux fonctions d'agrégation en ce sens qu'elles s'appliquent à un groupe de lignes. Une grande différence : les fonctions de fenêtre conservent les détails des lignes individuelles, contrairement aux fonctions d'agrégat avec GROUP BY.

Que fait RANK() ?

Comme son nom l'indique, elle classe les données. Il s'agit donc d'une fonction de fenêtre de classement, au même titre que DENSE_RANK() et ROW_NUMBER().

Lorsque vous utilisez l'une de ces fonctions de fenêtre, elle doit être accompagnée d'une clause OVER (ORDER BY). La clause OVER() est obligatoire pour toute fonction fenêtre. C'est elle qui transforme une fonction "normale" en fonction fenêtre.

Pour ces fonctions de classement, le ORDER BY entre parenthèses définit l'ordre dans lequel le classement est effectué. Cet ordre peut être croissant ou décroissant. Gardez à l'esprit que cela n'affecte pas l'ordre des lignes dans le résultat ; cela se fait à l'aide d'un ORDER BY à la fin de la requête.

Pour en savoir plus, consultez notre article sur la fonction de fenêtre RANK().

Comment RANK() fonctionne avec OVER (ORDER BY)

Reprenons l'ensemble de données ci-dessus. Prenons l'ensemble du tableau et classons les livres en fonction des ventes. Voyons ce qui se passe.

SELECT
  title,
  author,
  sales,
  RANK() OVER (ORDER BY sales DESC) AS sales_rank
FROM books;

Il s'agit de la même requête que la précédente, sauf que celle-ci ne sélectionne pas la langue et n'utilise pas PARTITION BY. La fonction RANK() est donc suivie de OVER(). La clause ORDER BY indique à la fonction de classer les données par ordre décroissant des ventes, c'est-à-dire des livres les plus vendus aux livres les moins vendus. La clause PARTITION BY étant omise, la fonction classe l'ensemble du tableau.

Voici les dix premières lignes du résultat.

Ce qu'il faut remarquer ici, c'est qu'il y a quatre livres vendus à 50 millions d'exemplaires, et qu'ils sont tous classés en cinquième position.

C'est en cela que RANK() diffère des deux autres fonctions de fenêtre de classement : elle attribue le même rang aux valeurs égales. Lorsque la fonction atteint la valeur de vente suivante (dans ce cas, 36 millions d'exemplaires vendus), elle n'attribue pas la valeur de rang consécutive suivante (6), mais saute pour tenir compte du nombre de valeurs de vente ex æquo. Comme indiqué, le rang "5" apparaît quatre fois ; le rang suivant est donc neuf.

DENSE_RANK() classe également les ex æquo avec le même classement. Cependant, contrairement à RANK(), il ne saute pas les valeurs de classement basées sur les égalités. Les mêmes données classées avec DENSE_RANK() se présentent comme suit.

Après plusieurs livres classés cinquièmes, le rang suivant est le sixième, et non le neuvième comme pour RANK().

Qu'en est-il de ROW_NUMBER()? Il ne se préoccupe pas des ex aequo ou des sauts. Il se contente de classer les lignes de manière séquentielle. Les dix premiers rangs obtenus à l'aide de ROW_NUMBER() sont présentés ci-dessous.

Les différences entre ces fonctions sont expliquées dans l'aperçu des fonctions de classement.

Comment RANK() fonctionne avec OVER (PARTITION BY)

La clause PARTITION BY divise les données en partitions ou sous-ensembles. Lorsqu'elle est utilisée avec RANK(), cela signifie que les données sont classées à l'intérieur de la partition. Lorsqu'elles atteignent la deuxième partition, le classement est réinitialisé à partir de la première.

Reprenons la requête du début de cet article pour clarifier ce point.

SELECT
  original_language,
  title,
  author,
  sales,
  RANK() OVER (PARTITION BY original_language ORDER BY sales DESC)
    AS sales_rank
FROM books;

Dans cette requête, RANK() est utilisé avec PARTITION BY. La partition, dans ce cas, est original_language. Le classement est effectué par ordre décroissant des ventes, comme spécifié dans la clause ORDER BY.

La façon dont nous avons écrit PARTITION BY et ORDER BY signifie que les livres sont classés en fonction des ventes, mais dans chaque catégorie linguistique. Une fois que la fonction a classé tous les livres d'une langue, elle recommence lorsqu'elle atteint la deuxième langue, et ainsi de suite.

C'est ce que nous voyons dans le résultat de la requête.

Nous avons marqué chaque partition d'une couleur différente pour faciliter la visualisation des différentes partitions. Le livre le plus vendu en anglais est Harry Potter et l'école des sorciers de J.K. Rowling. Vient ensuite Le Hobbit. Lolita et Watership Down sont tous deux classés en troisième position, car RANK() attribue le même rang aux livres ayant la même valeur de vente. Ensuite, on saute une valeur de rang, et Nineteen Eighty-Four est classé cinquième.

La partition suivante est la langue française, et le classement recommence. Le Petit Prince est le livre le plus vendu en français.

La même logique s'applique aux livres en italien, en russe et en espagnol.

Exemple bonus

Nous avons étudié ces requêtes pour montrer comment fonctionne RANK() OVER (PARTITION BY). Maintenant, mettons-nous à l'œuvre !

L'exemple suivant n'est pas très différent de la première requête. Vous ne devriez pas avoir de difficultés à appliquer ce que vous avez appris.

La table books nommée classify_under. Elle indique la catégorie dans laquelle chaque livre doit être placé dans la librairie.

Classons les livres par ventes pour chaque catégorie.

SELECT
  classify_under,
  title,
  author,
  sales,
  RANK() OVER (PARTITION BY classify_under ORDER BY sales DESC)
    AS sales_rank
FROM books;

La requête sélectionne la colonne classify_under au lieu de original_language dans la première requête. Toutes les autres colonnes sélectionnées sont identiques.

Il y a également une différence dans RANK(). Puisque nous établissons un classement par classify_under, c'est cette colonne qui doit figurer dans la clause PARTITION BY.

Une fois de plus, nous voulons classer les livres par ventes dans l'ordre décroissant. Voici le classement :

Par pure coïncidence, il y a à nouveau cinq partitions. Dans la catégorie "Classiques", Don Quichotte est le livre le plus vendu. Ensuite, il y a trois livres classés en deuxième position. La séquence de classement est sautée jusqu'à ce que nous arrivions à Guerre et Paix, à la cinquième place. Les autres classiques sont classés dans l'ordre puisqu'il n'y a plus d'égalité.

Dans les autres catégories, il n'y a pas d'ex æquo : les catégories "poésie" et "science-fiction" ne comptent qu'un seul livre chacune. Il n'y a donc que le premier rang.

Pour plus d'exemples avec d'autres fonctions de fenêtre, consultez notre article expliquant comment utiliser PARTITION BY.

Ajouter RANK() à votre vocabulaire SQL

Nous avons vu les utilisations les plus typiques de la fonction de fenêtre RANK(). Bien qu'elle nécessite une clause OVER (ORDER BY), la clause PARTITION BY débloque ses possibilités. Elle fait de RANK() un outil sophistiqué pour classer les données dans une ou plusieurs partitions avec facilité dans votre travail quotidien.

Pour en savoir plus sur RANK() et d'autres fonctions de fenêtre (de classement) et les mettre en pratique, utilisez notre cours Fonctions de fenêtrage cours. Vous obtiendrez une explication encore plus détaillée du classement et aurez l'occasion d'écrire beaucoup de code dans nos exercices.

Bon classement !