Comment numéroter les lignes d'un jeu de résultats SQL

Avez-vous déjà eu besoin d'ajouter un numéro séquentiel aux enregistrements du résultat d'une requête SQL ? Ce n'est pas aussi simple qu'il y paraît ! Découvrez comment le faire correctement dans cet article.

Pour numéroter les lignes d'un ensemble de résultats, vous devez utiliser une fonction de la fenêtre SQL appelée ROW_NUMBER(). Cette fonction attribue un numéro séquentiel entier à chaque ligne de résultat. Cependant, elle peut également être utilisée pour numéroter les enregistrements de différentes manières, par exemple par sous-ensembles. Vous pouvez même l'utiliser pour numéroter des enregistrements à d'autres fins intéressantes, comme nous allons le voir.

Une demande fréquente : Pourriez-vous numéroter les enregistrements ?

Supposons que vous travaillez pour une société de vente de voitures et que vous souhaitiez produire le rapport suivant. Notez que la première colonne (row_num), n'est pas une colonne de table ; nous la générons en utilisant ROW_NUMBER() dans la requête.

Tableau de résultats

La requête permettant d'obtenir le rapport est la suivante :

SELECT ROW_NUMBER() OVER () AS row_num,
       article_code,
       article_name,
       branch,
       units_sold
FROM  Sales
WHERE article_code IN ( 101, 102, 103 )

Dans la requête ci-dessus, la syntaxe de la fonction ROW_NUMBER() est très simple : nous utilisons une clause OVER vide. Cela signifie que nous voulons numéroter tous les enregistrements du jeu de résultats en utilisant une seule séquence de chiffres, en attribuant des numéros aux enregistrements sans ordre. Il s'agit de la façon la plus simple d'utiliser la fonction ROW_NUMBER():

ROW_NUMBER() OVER () AS row_num

Cependant, il existe d'autres façons d'utiliser ROW_NUMBER(). Nous pouvons ajouter une clause PARTITION BY et/ou ORDER BY à la fonction OVER, comme nous le verrons dans la section suivante. La clause PARTITION BY nous permet de numéroter plusieurs groupes d'enregistrements indépendamment, tandis que la clause ORDER BY nous permet de numéroter les enregistrements dans un ordre spécifique. Dans la prochaine section, nous verrons quelques exemples.

Avant de poursuivre avec ROW_NUMBER(), nous devons dire quelques mots sur les fonctions de fenêtre SQL. Comme nous l'avons mentionné précédemment, ROW_NUMBER() est une fonction fenêtre. Il existe de nombreuses autres fonctions de fenêtre que vous pouvez utiliser dans vos requêtes, comme AVG(), MAX(), LEAD(), LAG() et FIRST_VALUE(). Si vous souhaitez entrer dans les détails, je vous suggère le cours Fonctions de fenêtrage de LearnSQL. Il s'agit d'un tutoriel qui vous guide pas à pas à travers les fonctions de fenêtre SQL à l'aide d'exemples et d'exercices.

Approfondissement : Les clauses Partition By et Order By

Dans la section précédente, nous avons abordé la manière la plus simple d'utiliser la fonction fenêtre ROW_NUMBER(), c'est-à-dire la numérotation de tous les enregistrements du jeu de résultats, sans ordre particulier. Dans les paragraphes suivants, nous allons voir trois exemples avec des clauses supplémentaires, comme PARTITION BY et ORDER BY.

Dans notre premier exemple, nous numéroterons les enregistrements en utilisant une séquence différente pour chaque branche de la société, qui sera classée en fonction des unités vendues dans cette branche. Dans la requête suivante, la clause de branche PARTITION BY regroupe les enregistrements qui ont la même valeur dans la branche, en attribuant une séquence ROW_NUMBER différente à chaque groupe/branche. (Chaque groupe a une couleur différente dans l'image ci-dessous.) La clause ORDER BY units_sold définit l'ordre dans lequel nous traitons les lignes à l'intérieur de la partition. Dans ce cas, les lignes appartenant à chaque partition seront ordonnées par unité_vendue en ordre décroissant.

SELECT 
   ROW_NUMBER() OVER (PARTITION BY branch ORDER BY units_sold DESC) AS row_num,
   article_code,
   article_name,
   branch,
   units_sold
FROM  Sales
WHERE article_code IN ( 101, 102, 103 )

Notez que dans cette requête, les séquences sont attribuées par branche - dans l'image ci-dessous, chaque groupe d'enregistrements a une couleur différente - et ordonnées par units_sold. La clause que nous avons utilisée est la suivante :

ROW_NUMBER() OVER (PARTITION BY branch ORDER BY units_sold DESC)

Cette clause nous permet de numéroter les groupes d'enregistrements qui ont la même valeur dans la colonne branche. Dans notre exemple, il y a deux groupes d'enregistrements : New York (rouge) et San Francisco (bleu). Maintenant, chaque groupe d'enregistrements sera numéroté (clause ORDER BY ) en fonction de la colonne units_sold. Les valeurs sont affichées dans l'ordre décroissant.

Tableau de résultats

Les clauses OVER, PARTITION BY, et ORDER BY sont très courantes dans les fonctions de fenêtre ; si vous voulez entrer dans les détails, je vous suggère de lire l'article Exemples de fonctions de fenêtre, où vous trouverez plusieurs exemples de fonctions de fenêtre expliqués en détail.

Utilisation de ROW_NUMBER pour supprimer les doublons

Un autre cas d'utilisation intéressant de la fonction ROW_NUMBER() est celui des enregistrements entièrement dupliqués dans une table. Cela se produit lorsque la table contient plus d'un enregistrement avec les mêmes valeurs dans toutes ses colonnes (généralement en raison d'une erreur antérieure). Nous allons montrer un code SQL pour corriger cette situation ; de plus, ce code peut être adapté à n'importe quel cas d'enregistrements en double.

Tout d'abord, insérons quelques enregistrements complets dupliqués dans la table Sales table. Supposons que nous n'ayons pas de clé primaire dans la table Sales et qu'un développeur SQL exécute par erreur l'instruction suivante INSERT:

INSERT INTO sales 
SELECT * FROM sales WHERE branch = 'San Francisco';

Après l'exécution de INSERT, la table Sales ressemble à ceci. Les deux dernières lignes sont des doublons complets :

Table : Sales

Pour supprimer les enregistrements dupliqués, nous allons ajouter une nouvelle colonne appelée row_num et nous la remplirons avec la suivante INSERT qui utilise la fonction ROW_NUMBER(). Notez que nous PARTITION BY toutes les colonnes de la table. Voici le code SQL :

ALTER TABLE sales ADD COLUMN row_num INTEGER;
INSERT INTO sales 
SELECT 
 article_code, article_name, branch, units_sold, period, 
 ROW_NUMBER() OVER (PARTITION BY article_code,article_name,branch, units_sold,period) 
FROM sales ;

Ensuite, après avoir ajouté la nouvelle colonne et l'avoir remplie avec ROW_NUMBER(), notre tableau ressemble à ceci :

Table : Sales

Il est facile de voir que nous devons supprimer tous les enregistrements avec un NULL ou un 2 dans la colonne row_num. Faisons-le avec la commande DELETE. Après cela, nous devons supprimer la colonne row_num. Voici le code :

DELETE FROM sales WHERE row_rank IS NULL OR row_rank = 2;
ALTER TABLE sales DROP COLUMN row_rank;

Après l'exécution des instructions DELETE et ALTER, la table Sales est corrigée et ne contient plus d'enregistrements en double.

Cette approche présente un inconvénient que nous devons préciser. À un moment donné dans le processus, le nombre d'enregistrements dans la table Sales sera doublé. Cela peut rendre cette méthode inefficace, surtout pour les grandes tables. Nous recommandons donc de ne l'utiliser que pour les petites et moyennes tables.

Créer un rapport de classement avec ROW_NUMBER

Dans cette section, nous allons utiliser la fonction ROW_NUMBER() pour créer un classement. Nous verrons qu'il existe de meilleures fonctions pour le classement, comme RANK et DENSE_RANK; cependant, nous pouvons créer un rapport de classement assez bon en utilisant ROW_NUMBER().

Supposons qu'une fois par an, notre société de vente de voitures accorde trois primes à ses vendeurs : une prime est destinée à la personne qui a vendu le plus d'unités, une autre prime est destinée à la personne qui a réalisé le plus de recettes et la troisième prime est destinée à la personne qui a réalisé le plus de bénéfices. Si une catégorie de bonus est remportée par deux vendeurs, les deux vendeurs reçoivent 50 % du bonus. Nous utiliserons le tableau Sellers_2019 pour obtenir les classements et définir le gagnant de chaque bonus.

Tableau : Sellers_2019

La requête suivante renvoie les classements dont nous avons besoin pour définir les gagnants des bonus. Ces classements porteront sur les colonnes units_ranking, revenue_ranking et profit_ranking.

SELECT  seller_name,
	  ROW_NUMBER() OVER (ORDER BY units_sold desc) units_ranking,
	  ROW_NUMBER() OVER (ORDER BY revenue desc) revenue_ranking,
	  ROW_NUMBER() OVER (ORDER BY profit desc) profit_ranking
FROM sellers;

L'image suivante montre les résultats de la requête précédente avec la fonction ROW_NUMBER(). Au départ, les valeurs de classement semblent correctes. Mais si nous les examinons en détail, nous pouvons trouver une erreur avec Susan Graue dans Units_ranking. Susan (et John Doyle) a remporté la prime pour les unités vendues avec 123 unités. Cependant, la fonction ROW_NUMBER attribue la position 1 à John et la position 2 à Susan, ce qui n'est pas correct.

Tableau des résultats

Dans ce cas, la fonction ROW_NUMBER() n'est donc pas le meilleur choix pour les calculs de classement. Heureusement, SQL fournit deux fonctions spécifiquement destinées au classement : RANK() et DENSE_RANK(). La requête suivante utilise la fonction RANK() au lieu de ROW_NUMBER():

SELECT  seller_name,
	  RANK() OVER (ORDER BY units_sold desc) units_ranking,
	  RANK() OVER (ORDER BY revenue desc) revenue_ranking,
	  RANK() OVER (ORDER BY profit desc) profit_ranking
FROM sellers;

Dans l'image suivante, nous pouvons voir les résultats de la requête RANK(). Nous pouvons vérifier que le problème du classement de Susan est résolu.

Tableau des résultats

Si vous êtes intéressé par les fonctions de fenêtre RANK() et DENSE_RANK(), je vous suggère de lire l'article Comment utiliser les fonctions RANK. Vous y trouverez plusieurs exemples et requêtes.