Utilisation de la fonction Row_Number() dans MySQL

1. Utilisation de ROW_NUMBER() dans MySQL à l’aide de la clause ORDER BY
2. Utilisation de ROW_NUMBER() dans MySQL à l’aide de la clause PARTITION BY
3. Utilisation de ROW_NUMBER() dans MySQL à l’aide des clauses PARTITION BY et ORDER BY
4. Réplication de ROW_NUMBER() dans MySQL à l’aide de la variable de session
5. Conclusion

Dans ce tutoriel, nous allons vous présenter comment vous pouvez utiliser la fonction ROW_NUMBER() dans MySQL. Il s’agit d’une méthode de classement qui attribue des numéros consécutifs dans la partition à partir de 1. Il est important de noter que deux lignes dans la partition n’ont pas le même numéro.

Nous verrons également comment PARTITION BY et ORDER BY affectent les résultats MySQL. Vous devez utiliser la clause ORDER BY pour utiliser ROW_NUMBER() car elle est obligatoire. Mais la clause PARTITION BY est facultative.

Les résultats seront indéterminés si vous utilisez les deux clauses, PARTITION BY et ORDER BY. Ici, nous allons voir comment émuler la fonction ROW_NUMBER() en utilisant la variable de session pour obtenir les résultats souhaités.

Veuillez noter que ROW_NUMBER() n’était pas disponible avant la version 8.0 de MySQL. Vous voyez les nouveautés de MySQL version 8.0 ici.

Utilisation de ROW_NUMBER() dans MySQL à l’aide de la clause ORDER BY

Nous n’utiliserons que la fonction ROW_NUMBER() avec la clause ORDER BY et observerons les résultats. Commençons par créer la table et remplissons-y des données.

Exemple de code :

# SQL Programming Using MySQL Version 8.27
CREATE TABLE `test_db`.`tb_student` (
 STUDENT_ID	INTEGER NOT NULL,
 FIRST_NAME	VARCHAR(30) NOT NULL,
 LAST_NAME	VARCHAR(30) NOT NULL,
 GENDER	VARCHAR(30) NOT NULL,
 CITY_NAME	VARCHAR(64) NOT NULL,
 EMAIL_ADDRESS	VARCHAR(64) NOT NULL,
 REGISTRATION_YEAR INTEGER NOT NULL,
 PRIMARY KEY	(STUDENT_ID)
);

Cette requête créera une table nommée tb_student, que vous pourrez confirmer dans la base de données MySQL.

Insérez les six enregistrements dans la table nommée tb_student en utilisant la syntaxe suivante de la requête INSERT.

# SQL Programming Using MySQL Version 8.27
INSERT INTO test_db.tb_student
(STUDENT_ID, FIRST_NAME, LAST_NAME, GENDER, CITY_NAME, EMAIL_ADDRESS, REGISTRATION_YEAR)
VALUES
(1,'Ayush','Kumar', 'Male', 'Washington', 'akuman@yahoo.com', 2010);

Sélectionnez ensuite toutes les données de la table à afficher à l’aide de la requête suivante.

# SQL Programming Using MySQL Version 8.27
SELECT * FROM test_db.tb_student

Votre tableau contiendra les données suivantes. Vous pouvez également vérifier de votre côté et comparer.

# SQL Programming Using MySQL Version 8.27
SELECT *,
    ROW_NUMBER() OVER(ORDER BY REGISTRATION_Year) AS row_numb
FROM test_db.tb_student;

Après avoir exécuté la requête ci-dessus, vous obtiendrez le résultat suivant.

Observez la sortie ci-dessus et vous verrez que tous les enregistrements sont affichés, qui sont classés par année d’enregistrement (voir la colonne dans la case verte). Et le row_number est également le même que prévu, en partant de 1 et en continuant d’augmenter séquentiellement jusqu’à la fin du tableau, car nous lisons toutes les données de tb_student.

Utilisation de ROW_NUMBER() dans MySQL à l’aide de la clause PARTITION BY

Nous n’utiliserons que la fonction ROW_NUMBER() avec la clause PARTITION BY et observerons les résultats. Nous comparerons également cette sortie avec les résultats que nous avons obtenus en utilisant ROW_NUMBER() avec la clause ORDER BY.

Exemple de code :

# SQL Programming Using MySQL Version 8.27
SELECT *,
    ROW_NUMBER() OVER(PARTITION BY REGISTRATION_Year) AS row_numb
FROM test_db.tb_student;

Maintenant, vous obtiendrez les résultats suivants.

Jetez un œil à la colonne REGISTRATION_YEAR ; il comporte 5 partitions (2010, 2011, 2012, 2013 et 2014). Il y a deux lignes dans le tableau pour la partition 2010, et les numéros de ligne sont correctement attribués (voir à nouveau la capture d’écran ci-dessus). Il n’y a qu’une seule ligne pour partition 2011, 2012, 2013, 2014 ; c’est pourquoi vous pouvez voir 1 dans la colonne row_numb.

Si nous utilisons la clause PARTITION BY, alors pourquoi la colonne nommée REGISTRATION_YEAR est-elle dans l’ordre croissant ? Parce que la clause PARTITION BY ordonne les données dans ces partitions. Insérons un autre enregistrement pour lequel la valeur de REGISTRATION_YEAR serait 2009 et observons les résultats.

# SQL Programming Using MySQL Version 8.27

INSERT INTO test_db.tb_student
(STUDENT_ID, FIRST_NAME, LAST_NAME, GENDER, CITY_NAME, EMAIL_ADDRESS, REGISTRATION_YEAR)
VALUES
(7,'Mashal','Naaz', 'Female', 'Florida', 'mashalnaaz@gmail.com', 2009);

SELECT *,
    ROW_NUMBER() OVER(PARTITION BY REGISTRATION_Year) AS row_numb
FROM test_db.tb_student;

Maintenant, vous verrez que le record récent est au sommet.

Utilisation de ROW_NUMBER() dans MySQL à l’aide des clauses PARTITION BY et ORDER BY

Maintenant, nous n’utiliserons que la fonction ROW_NUMBER() avec les clauses PARTITION BY et ORDER BY et nous verrons si elle fournit toujours les bons numéros de ligne.

Exemple de code :

# SQL Programming Using MySQL Version 8.27
SELECT *,
    ROW_NUMBER() OVER(PARTITION BY REGISTRATION_YEAR ORDER BY REGISTRATION_YEAR) AS row_numb
FROM test_db.tb_student;

Après avoir exécuté la requête ci-dessus, vous verrez la même sortie que celle que nous avons obtenue en utilisant ROW_NUMBER() avec la clause PARTITION BY. Voir la capture d’écran suivante :

Voir la colonne à fond jaune, c’est ce à quoi nous nous attendions. Ici, nous utiliserons la variable de session pour attribuer correctement les numéros de ligne.

Réplication de ROW_NUMBER() dans MySQL à l’aide de la variable de session

MySQL ne fournit pas la fonctionnalité de classement correcte lorsque nous utilisons simultanément les clauses PARTITION BY et ORDER BY. Dans ce scénario, nous émulons cela en utilisant Session Variable. Les variables de session sont définies par l’utilisateur ; vous pouvez le voir ici pour des informations détaillées.

Exemple de code :

# SQL Programming Using MySQL Version 8.27
SET @row_numb = 0;
SELECT *,
    (@row_numb:=@row_numb + 1) AS row_numb
FROM test_db.tb_student ORDER BY REGISTRATION_YEAR;

Comme vous pouvez le voir ci-dessous, row_numb commence à partir de 1 et augmente consécutivement.

Comment ça marche ? Nous avons d’abord défini une variable de session row_numb en utilisant le préfixe @ et initialisé avec le 0. Ensuite, nous avons sélectionné les données de la table, les avons ordonnées et imprimées. (@row_numb:=@row_numb + 1) revient à incrémenter et mettre à jour la valeur de la variable.

Conclusion

À la lumière de la discussion ci-dessus, nous avons conclu que bien que nous puissions également utiliser la fonction ROW_NUMBER() dans MySQL si nous avons la version 8.0 ou supérieure, il existe encore des situations où nous devons utiliser Session Variables à des fins de classement.