Comment utiliser ROW_NUMBER OVER() en SQL pour classer les données

Vous avez parfois besoin de connaître la position des lignes dans un ensemble de résultats. Apprenezcomment utiliser ROW_NUMBER et OVER en SQL pour y parvenir!

Avez-vous déjà eu besoin d'ajouter un numéro séquentiel aux enregistrements renvoyés par une requête SQL ? Ou peut-être avez-vous besoin de créer un rapport "top n" basé sur un classement spécifique. Dans tous ces cas, vous devez calculer la position de la ligne dans le classement. Pour ce faire, vous avez besoin de la fonction ROW_NUMBER(). Cette fonction attribue un nombre entier séquentiel à chaque ligne de l'ensemble de résultats.

Dans cet article, nous allons voir comment utiliser la fonction ROW_NUMBER() en SQL.

Qu'est-ce que la fonction ROW_NUMBER() ?

ROW_NUMBER ROW_NUMBER() est une fonction de fenêtre en SQL. Elle est utilisée pour ajouter des numéros séquentiels aux lignes d'un ensemble de résultats. Comme toute autre fonction de fenêtre, vous devez l'utiliser avec la clause OVER(). Voici la syntaxe :

SELECT
  ROW_NUMBER() OVER (...) as athlete_num
  …
FROM athletes;

La clause OVER() comporte deux sous-clauses facultatives : PARTITION BY et ORDER BY. Nous montrerons des exemples utilisant plusieurs clauses OVER différentes.

Avant de commencer, parlons un peu des fonctions de fenêtre en général. Les fonctions de fenêtre sont une partie très puissante de SQL, mais elles ne sont pas très connues de l'utilisateur SQL moyen. C'est pourquoi je recommande notre cours interactif sur Fonctions de fenêtrage. Dans ce cours étape par étape, nous vous guiderons à travers les fonctions de fenêtre à l'aide de plus de 200 exercices pratiques. À la fin du cours, vous vous sentirez à l'aise dans l'utilisation des fonctions window sur les bases de données SQL.

Utilisation de ROW_NUMBER() avec OVER() : Un exemple d'introduction

Montrons une requête SQL simple utilisant la fonction de fenêtre ROW_NUMBER. Il n'y a rien de mieux que le sport pour illustrer les classements, alors supposons que nous travaillons pour une société qui organise des compétitions sportives dans de nombreux pays.

Tout d'abord, nous voulons attribuer un numéro séquentiel à chaque athlète ; ce numéro sera utilisé comme identifiant de l'athlète dans notre entreprise. Pour éviter les conflits, nous ne voulons pas qu'il y ait de critères pour déterminer l'ordre de la numérotation séquentielle. Nous voulons que les numéros séquentiels soient attribués à chaque athlète de manière aléatoire, et non par ordre alphabétique de nom, de pays ou de sport.

Nous disposons d'un tableau appelé athlete avec les colonnes firstname, lastname, sport, et country. La requête permettant de générer un rapport comprenant un numéro séquentiel pour chaque athlète est la suivante :

SELECT
  ROW_NUMBER() OVER () as athlete_id,
  firstname
  lastname,
  sport, 
  country
FROM athletes;

L'expression ROW_NUMBER() OVER () attribue une valeur séquentielle entière commençant par 1 à chaque ligne de l'ensemble de résultats de la requête. L'ordre des numéros attribués aux lignes du résultat n'est pas déterministe si vous utilisez la simple clause OVER(). (Notez qu'il n'y a pas de clauses supplémentaires telles que la clause ORDER BY ou PARTITION BY dans OVER()) Le premier enregistrement peut être n'importe quel enregistrement de la table ; pour cet enregistrement, ROW_NUMBER renverra 1. Il en va de même pour le deuxième enregistrement, qui sera le numéro 2, et ainsi de suite. Vous trouverez ci-dessous un résultat partiel de la requête :

Avant de terminer cette section, j'aimerais vous suggérer l'article Qu'est-ce que la clause OVER() en SQL ?, où vous trouverez plusieurs exemples de fonctions de fenêtre utilisant différentes combinaisons de la clause OVER.

Création de classements avec ROW_NUMBER() et ORDER BY

Supposons maintenant que l'entreprise doive créer une étiquette avec le numéro de participant pour tous les athlètes participant à un marathon. Les athlètes doivent être classés par nom de famille, et la société souhaite attribuer un numéro séquentiel à chaque athlète ; les athlètes porteront ces numéros comme étiquettes sur leurs chemises pendant le marathon. Les étiquettes doivent commencer à 1001. La requête est la suivante :

SELECT
  ROW_NUMBER() OVER (ORDER BY lastname) + 1000 as participant_label,
  firstname,
  lastname, 
  country
FROM athletes
WHERE sport = 'Marathon';

Cette requête est similaire à l'exemple précédent. L'une des différences est la clause WHERE, qui renvoie uniquement les athlètes participant au marathon. L'autre différence (qui est la principale) est la clause OVER(ORDER BY lastname). Elle indique à ROW_NUMBER() que le numéro séquentiel doit être attribué dans l'ordre de lastname- par exemple, 1 pour le premier lastname, 2 pour le deuxième, et ainsi de suite.

Dans l'ensemble de résultats précédent, les participants étaient classés par lastname. Toutefois, si deux participants ont le même nom de famille (c'est-à-dire Smith), l'ordre de ces deux lignes n'est pas déterministe ; les lignes peuvent être classées dans n'importe quel ordre. Si nous voulons ordonner à la fois par lastname et firstname, nous devons utiliser l'expression :

ROW_NUMBER() OVER (ORDER BY lastname, firstname)

Utilisation de ORDER BY deux fois dans une même requête

Dans la requête ci-dessus, nous utilisons la clause ORDER BY dans la fonction ROW_NUMBER(). Cependant, le résultat de la requête ne suit aucun ordre, c'est-à-dire que les lignes sont classées de manière aléatoire. Si nous le souhaitions, nous pourrions ajouter une deuxième clause ORDER BY à la fin de la requête pour définir l'ordre dans lequel les enregistrements du résultat sont affichés.

Modifions la requête précédente en y apportant un seul changement : Nous allons ajouter un ORDER BY country:

SELECT
  ROW_NUMBER() OVER (ORDER BY lastname ASC) + 1000 as participant_label,
  firstname,
  lastname, 
  country
FROM athletes
WHERE sport = 'Marathon'
ORDER BY country;

Les lignes du résultat ci-dessous sont les mêmes que celles de la requête précédente, mais elles sont affichées dans un ordre différent. Elles sont désormais classées en fonction du pays de l'athlète. Toutefois, si deux athlètes ou plus sont originaires du même pays, ils sont affichés dans n'importe quel ordre. C'est ce que nous voyons ci-dessous avec les deux athlètes du Canada :

Dans cette requête, nous avons utilisé deux fois la clause ORDER BY. La première fois, nous l'avons utilisée dans la fonction ROW_NUMBER pour attribuer le numéro séquentiel suivant l'ordre du nom de famille. La seconde fois a été utilisée pour définir l'ordre dans lequel les lignes de résultats sont affichées, qui est basé sur le nom du pays.

Utilisation de ROW_NUMBER() avec PARTITION BY et ORDER BY

Dans l'exemple de requête suivant, nous utiliserons ROW_NUMBER() en combinaison avec les clauses PARTITION BY et ORDER BY. Nous allons présenter une requête permettant d'attribuer des numéros de chambre aux athlètes. Supposons que l'entreprise veuille loger les athlètes d'un même pays dans des chambres d'hôtel contiguës. L'idée est de créer une étiquette avec le pays et un numéro séquentiel pour chaque athlète et d'apposer cette étiquette sur la porte de chaque chambre d'hôtel. Par exemple, si le pays est le Canada et qu'il compte trois athlètes, nous voulons que les étiquettes des chambres soient "Canada_1", "Canada_2" et "Canada_3".

La requête permettant de générer les étiquettes de chambre avec le nom de l'athlète assigné à cette chambre est la suivante : "Canada_1, Canada_2 et Canada_3" :

SELECT
  country || '_' || 
  ROW_NUMBER() OVER (PARTITION BY country ORDER BY lastname ASC) 
                                                            as room_label,
  firstname,
  lastname, 
  country
FROM athletes;

Le nouvel élément introduit dans la requête est OVER(PARTITION BY country). Il regroupe les lignes du même country et génère une série séquentielle de nombres différente (à partir de 1) pour chaque pays.

Dans le résultat de la requête suivant, vous pouvez voir que les lignes regroupées par la clause PARTITION BY ont la même couleur. Un groupe de lignes correspond au Canada (bleu clair), un autre à la France (violet), et ainsi de suite.

À l'intérieur de chaque groupe de lignes, la clause ORDER BY lastname est utilisée pour attribuer des numéros séquentiels aux athlètes en fonction de leur nom de famille. Pour "Irlande", nous avons trois lignes ; la première est pour "Barry", la deuxième pour "Fox", et ainsi de suite.

Je vous recommande l'article Comment utiliser la clause SQL PARTITION BY avec OVER, où vous trouverez d'autres exemples des clauses OVER et PARTITION BY.

Autres classements Fonctions de fenêtrage: RANK et DENSE_RANK

Outre ROW_NUMBER, SQL fournit deux autres fonctions de fenêtre pour calculer les classements : RANK et DENSE_RANK. La fonction RANK fonctionne différemment de ROW_NUMBER lorsqu'il y a des égalités entre les lignes. En cas d'égalité, RANK attribue la même valeur aux deux lignes et saute le rang suivant (par exemple, 1, 2, 2, 2, 5 - les rangs 3 et 4 sont omis). La fonction DENSE_RANK ne saute pas le(s) rang(s) suivant(s).

Prenons un exemple simple pour voir les différences entre ces trois fonctions :

SELECT 
  lastname AS athlete_name, 
  time, 
  ROW_NUMBER() OVER (ORDER BY time) AS position_using_row_number, 
  RANK OVER() (ORDER BY time) AS position_using_rank,
  DENSE_RANK() OVER (ORDER BY time) AS position_using_dense_rank
 FROM competition_results
 WHERE sport = ‘Marathon men’; 

Les résultats sont les suivants :

Si vous êtes intéressé par les fonctions de fenêtre RANK et DENSE_RANK, je vous suggère ces articles pour plus de détails et d'exemples :

• Aperçu des fonctions de classement en SQL
• Comment utiliser la fonction RANK Fonctions
• Qu'est-ce que la fonction RANK en SQL et comment l'utiliser ?

Utilisation de ROW_NUMBER() dans la clause WHERE

En SQL, vous ne pouvez pas utiliser les fonctions de fenêtre dans la clause WHERE. Cependant, dans certains cas, vous pouvez être amené à le faire. Dans un rapport Top 10, par exemple, il serait très utile de pouvoir utiliser une condition telle que WHERE ROW_NUMBER OVER() <= 10.

Bien que vous ne puissiez pas utiliser ROW_NUMBER() directement dans WHERE, vous pouvez le faire indirectement par le biais d'une expression de table commune, ou CTE. Par exemple, supposons que nous voulions obtenir les 3 premières positions dans le marathon et la course de 100 mètres. Tout d'abord, nous écrivons l'ETC, qui commence par WITH:

-- CTE starts
WITH positions AS (
  SELECT 
    lastname AS athlete_name,
    sport,
    country,
    time, 
    ROW_NUMBER OVER (PARTITION BY sport ORDER BY time) AS position
  FROM competition_results
  WHERE sport IN (‘Marathon men’, ‘Marathon women’)
)
	--CTE ends

	--main query starts
SELECT 
  sport, 
  athlete_name, 
  time, 
  country, 
  position
FROM positions
WHERE position <= 3
ORDER BY sport, position;

Dans la requête précédente, nous avons créé un ETC appelé positions. Il possède une colonne appelée position qui est remplie avec le résultat de la fonction ROW_NUMBER().

Dans la requête principale (c'est-à-dire la deuxième instruction SELECT ), nous pouvons utiliser la colonne position dans la clause WHERE pour filtrer les athlètes qui terminent la compétition dans les trois premières positions.

Remarque : en cas d'égalité entre deux concurrents, la fonction RANK() pourrait être plus appropriée que la fonction ROW_NUMBER() dans ce rapport.

Les résultats de la requête sont présentés ci-dessous :

Si vous souhaitez vous exercer aux fonctions de fenêtre SQL, je vous recommande notre jeu d'exercices interactif Fonctions de fenêtrage. Il propose 100 exercices pratiques sur les fonctions de fenêtre, y compris la création de classements à l'aide de différentes fonctions de fenêtre de classement.

La pseudo-colonne Oracle ROWNUM

Oracle SQL nous permet de placer une pseudo-colonne appelée ROWNUM dans n'importe quelle requête. Une pseudocolonne se comporte comme une colonne de table, mais n'est pas réellement stockée dans la table. Vous pouvez effectuer une sélection à partir d'une pseudocolonne comme s'il s'agissait d'une colonne de la table.

La pseudocolonne ROWNUM renvoie la position de la ligne dans le jeu de résultats. Elle commence par 1 pour la première ligne et chaque enregistrement suivant est incrémenté de 1.

Cependant, Oracle ROWNUM n'a pas la puissance de la fonction de fenêtre ROW_NUMBER. Par exemple, vous ne pouvez pas utiliser la sous-clause PARTITION BY pour créer plusieurs séquences différentes comme nous l'avons fait dans la requête sur les chambres d'hôtel. Une autre limitation est que vous ne pouvez pas utiliser la clause ORDER BY pour spécifier un ordre de séquence différent de l'ordre de l'ensemble de résultats. La raison de ces limitations est simple : ROWNUM n'est pas une fonction de fenêtre ; il s'agit d'une simple pseudo-colonne.

Prêt à pratiquer ROW_NUMBER() et OVER() en SQL ?

Nous avons abordé plusieurs façons d'ajouter une séquence numérique au résultat d'une requête en utilisant la fonction ROW_NUMBER. Nous avons également montré différentes façons d'utiliser la clause OVER(). Nous avons également présenté deux autres fonctions de classement SQL : RANK et DENSE_RANK.

Les fonctions de fenêtrage sont une ressource puissante en SQL. Si vous souhaitez aller plus loin, je vous suggère de suivre notre cours interactif en ligne. Fonctions de fenêtrage interactif en ligne. Il s'agit d'un tutoriel pas à pas qui vous guide à travers les fonctions de fenêtrage à l'aide d'exemples et d'exercices. Je vous recommande également notre aide-mémoire gratuit sur les fonctions de fenêtrage, qui est mon aide-mémoire préféré. Je l'ai accrochée au mur de mon bureau pour m'aider rapidement à comprendre la syntaxe des fonctions de fenêtre.