Aperçu des fonctions de classement en SQL

Les fonctions de classement SQL facilitent le travail avec les bases de données relationnelles, notamment pour les analystes de données, les spécialistes du marketing et les spécialistes financiers. Ces fonctions sont utilisées pour attribuer un numéro de classement à chaque enregistrement et vous permettent de créer efficacement des rapports utiles.

Les fonctions de classement SQL sont des fonctions de fenêtre. Les fonctions de fenêtre calculent le résultat sur la base d'un ensemble de lignes. Le mot "fenêtre" fait référence à cet ensemble de lignes. Examinons la syntaxe des fonctions de classement :

rank_function OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY order_expression [ASC | DESC] ...
)

La syntaxe commence par le nom de la fonction de classement, comme RANK(), DENSE_RANK(), ROW_NUMBER(), ou PERCENT_RANK(), et la clause OVER(). Dans la clause OVER(), vous spécifiez les clauses PARTITION BY et ORDER BY. Pour les fonctions de classement, la clause ORDER BY, comprenant le(s) nom(s) de la ou des colonnes ou une expression, est obligatoire.

Avant la clause ORDER BY se trouve la clause facultative PARTITION BY, qui inclut le(s) nom(s) de la ou des colonnes ou une expression. La clause PARTITION BY divise l'ensemble des lignes en groupes de lignes pour des classements séparés.

Ne vous inquiétez pas si cette syntaxe vous semble compliquée. Je vais l'expliquer étape par étape dans les sections suivantes.

Les exemples présentés dans les sections suivantes utilisent la table salequi stocke des données dans les colonnes salesman_id, sale_date et sale_amount. Voir la vente de la table ci-dessous :

ROW_NUMBER()

La première fonction de classement dont je vais parler est ROW_NUMBER(). Elle renvoie le numéro séquentiel de chaque enregistrement dans le jeu de résultats ou dans la partition du jeu de résultats, en commençant par 1. Avec ROW_NUMBER(), vous pouvez sélectionner tous les enregistrements et les numéroter. Voir l'exemple 1 ci-dessous.

Exemple 1

SELECT 
ROW_NUMBER() OVER(ORDER BY sale_amount) 
  AS row_number, 
sale_date, 
salesman_id,
sale_amount 
FROM sale;

Cette requête renvoie le résultat :

Dans ce cas, la clause OVER contient uniquement la clause ORDER BY avec la colonne sale_amount (cette clause trie les lignes en fonction du montant de la vente en remontant de 9 500 $ à 31 000 $). La requête renvoie le numéro séquentiel à partir de 1 dans la colonne row_number.

Notez que les lignes qui ont la même valeur dans la colonne sale_amount reçoivent des numéros différents. Ainsi, si vous souhaitez classer tous les enregistrements avec un numéro unique, utilisez ROW_NUMBER().

Que faire si vous souhaitez numéroter des groupes de lignes distincts ? Vous pouvez utiliser la clause facultative PARTITION BY avant la clause ORDER BY. Voir l'exemple 2 ci-dessous.

Exemple 2

SELECT
ROW_NUMBER() OVER(PARTITION BY sale_date ORDER BY sale_amount) 
  AS row_number, 
sale_date, salesman_id, sale_amount 
FROM sale;

Les enregistrements sont divisés en groupes (appelés "partitions") en fonction de la date de vente. Dans chaque partition, les enregistrements sont numérotés séparément.

Cette requête renvoie le résultat :

Pour la date de vente 2020-04-20, les lignes portent les numéros 1 et 2, même si le montant de la vente est le même. Dans la partition suivante, la ligne avec la valeur de vente la plus faible porte le numéro 1, et la ligne avec la valeur de vente la plus élevée porte le numéro 2. Les enregistrements dans chaque partition sont triés en fonction de la colonne dans ORDER BY.

RANK()

La deuxième fonction de classement est RANK(). Cette fonction ajoute un numéro de rang, qui est un numéro séquentiel, à chaque ligne de l'ensemble de résultats ou de la partition de l'ensemble de résultats.

La différence entre RANK() et ROW_NUMBER() est que RANK() ignore les valeurs en double. Lorsqu'il y a des valeurs en double, le même classement est attribué, et un écart apparaît dans la séquence pour chaque classement en double.

Voir l'exemple 1 ci-dessous.

Exemple 1

SELECT
RANK() OVER(ORDER BY sale_amount)
  AS row_number,
sale_date,
salesman_id,
sale_amount
FROM sale;

Cette requête renvoie le résultat :

Dans ce cas, RANK() attribue un numéro de classement pour chaque enregistrement comme ROW_NUMBER(), mais pour la même valeur dans sale_amount, le numéro de classement est le même. Les vendeurs 11 et 13 du 22 avril 2020 ont réalisé le même montant de vente, soit 11 000 $. Par conséquent, ils ont le même numéro de rang, 2. Dans ce cas, ROW_NUMBER() a attribué un numéro de rang différent.

Remarquez que l'enregistrement suivant n'a pas le numéro 3. RANK() ignore le(s) numéro(s) de rang des lignes supplémentaires ayant la même valeur. Ainsi, après deux lignes avec le numéro de rang 2, le numéro de rang suivant est 4, et non 3.

Bien entendu, RANK() attribue également des numéros au sein des partitions. Voir l'exemple 2 ci-dessous.

Exemple 2

SELECT
RANK() OVER(PARTITION BY sale_date ORDER BY sale_amount)
  AS row_number,
sale_date, salesman_id, sale_amount
FROM sale;

Cette requête renvoie le résultat :

La requête ci-dessus a divisé le jeu de résultats en ensembles de lignes ayant la même date de vente. Par exemple, une partition contient les ventes du 22 avril 2020. Les lignes sont numérotées séparément pour chaque date de vente.

Le 22 avril 2020, les vendeurs 11 et 13 ont le même montant de vente, soit 11 000 $. Par conséquent, ces enregistrements ont tous deux le rang 1, et l'enregistrement suivant a le rang 3 car la ligne supplémentaire est ignorée.

DENSE_RANK()

La troisième fonction de classement est DENSE_RANK(). Si vous souhaitez attribuer le même numéro aux lignes ayant la même valeur dans une colonne donnée, mais sans sauter les numéros suivants, utilisez DENSE_RANK().

DENSE_RANK() est similaire à RANK(), mais avec DENSE_RANK(), le numéro de classement n'est pas sauté pour les mêmes valeurs. Voir l'exemple 1 ci-dessous.

Exemple 1

SELECT
DENSE_RANK() OVER(ORDER BY sale_amount)
  AS row_number,
sale_date,
salesman_id,
sale_amount
FROM sale;

Cette requête renvoie le résultat suivant :

Pour le même montant de vente, les lignes ont le même numéro. Cependant, les lignes suivantes ne sont pas sautées, et elles ont le numéro séquentiel suivant.

Remarquez que pour le même montant de vente de 11 000 $ par les vendeurs 11 et 13 le 22 avril 2020, le numéro de rang attribué est 2, mais pour les deux enregistrements suivants avec le montant de vente de 12 500 $, le numéro de rang est 3. Cette fonction ne saute pas le numéro suivant.

RANK() fonctionne différemment. Dans ce cas, pour le montant de la vente de 12 500 $, RANK() attribue le numéro de rang 4, en sautant le numéro 3 parce que deux lignes ont le numéro 2.

DENSE_RANK() fonctionne également avec les partitions. Voir l'exemple 2 ci-dessous.

Exemple 2

SELECT
DENSE_RANK() OVER(PARTITION BY sale_date ORDER BY sale_amount)
  AS row_number,
sale_date, salesman_id, sale_amount
FROM sale;

Cette requête renvoie le résultat :

Dans ce cas, le 22 avril 2020, les vendeurs dont le montant de la vente est de 11 000 $ ont le numéro de rang 1, mais l'enregistrement suivant a le numéro de rang 2, et non 3 comme avec RANK().

PERCENT_RANK()

La dernière fonction de classement dont je vais parler est PERCENT_RANK(). Cette fonction renvoie les rangs en pourcentage. Voir l'exemple 1 ci-dessous.

Exemple 1

SELECT
PERCENT_RANK() OVER(ORDER BY sale_amount)
  AS row_number,
sale_date,
salesman_id,
sale_amount
FROM sale;

Cette requête renvoie le résultat :

Cette requête calcule le rang relatif de chaque ligne de l'ensemble de résultats. La valeur la plus élevée du montant de la vente se voit attribuer 1 comme rang en pourcentage, et la valeur la plus basse se voit attribuer 0. Les valeurs intermédiaires sont renvoyées comme rang dans une plage de valeurs, qui sont supérieures à 0 et inférieures à 1.

À mi-chemin entre la valeur la plus élevée et la plus basse, le rang en pourcentage est de 0,5. Dans le cas présent, les vendeurs 11 et 12 du 2020-04-20 se voient attribuer le rang en pourcentage 0,5. Les enregistrements des vendeurs 11 et 13 du 22 avril 2020 se situent entre 0 et 0,5, ils ont donc un rang en pourcentage de 0,1666666.

PERCENT_RANK() fonctionne de la même manière pour les partitions d'enregistrements. Voir l'exemple 2 ci-dessous.

Exemple 2

SELECT
PERCENT_RANK() OVER(PARTITION BY sale_date ORDER BY sale_amount)
  AS row_number,
sale_date, salesman_id, sale_amount
FROM sale;

Cette requête renvoie le résultat :

Dans chaque partition, la ligne retournée la plus haute a un rang en pourcentage de 1, et la plus basse a 0. Dans ces partitions, il n'y a pas d'enregistrements entre le plus haut et le plus bas. Par conséquent, il n'y a pas de pourcentage de rang entre 0 et 1 comme dans l'exemple précédent.

Résumé

Dans cet article, j'ai abordé les fonctions de classement SQL, en expliquant leur syntaxe et en les utilisant dans des exemples concrets. Si vous souhaitez en savoir plus sur les fonctions de classement, consultez le cours "Fonctions de fenêtrage" sur LearnSQL.fr ou lisez les articles "Exemple de fonction fenêtre SQL avec explications", "How to Use Rank Functions in SQL" et "Common SQL Fonctions de fenêtrage: Using Partitions With Ranking Functions".