ROW_NUMBER() de MySQL expliqué (MySQL 8.0) : classement, requêtes Top-N et déduplication

1. Introduction

La version 8.0 de MySQL a introduit de nombreuses nouvelles fonctionnalités, dont l’une des plus remarquables est la prise en charge des fonctions de fenêtre. Dans cet article, nous nous concentrerons sur l’une des fonctions les plus fréquemment utilisées : ROW_NUMBER().

La fonction ROW_NUMBER() offre de puissantes capacités d’analyse et de reporting, facilitant le tri et le classement des données selon des conditions spécifiques. Cet article explique tout, de l’utilisation de base et des exemples pratiques aux approches alternatives pour les versions plus anciennes de MySQL.

Lecteurs cibles

• Utilisateurs débutants à intermédiaires disposant de connaissances de base en SQL
• Ingénieurs et analystes de données qui traitent et analysent des données avec MySQL
• Toute personne envisageant de migrer vers la dernière version de MySQL

Avantages de ROW_NUMBER()

Cette fonction vous permet d’attribuer un numéro unique à chaque ligne en fonction de conditions spécifiques. Par exemple, vous pouvez facilement écrire des requêtes telles que « créer un classement en ordre décroissant des ventes » ou « extraire et organiser les données dupliquées » de manière concise.

Dans les versions antérieures, il fallait souvent écrire des requêtes complexes en utilisant des variables définies par l’utilisateur. Avec ROW_NUMBER(), votre SQL devient plus simple et plus lisible.

Dans cet article, nous utiliserons des exemples de requêtes concrets et les expliquerons de manière accessible aux débutants. Dans la section suivante, nous examinerons de plus près la syntaxe de base et le comportement de cette fonction.

2. Qu’est‑ce que la fonction ROW_NUMBER() ?

La fonction ROW_NUMBER(), ajoutée dans MySQL 8.0, est un type de fonction de fenêtre qui attribue des numéros séquentiels aux lignes. Elle peut numéroter les lignes selon un ordre spécifique et/ou au sein de chaque groupe, ce qui est extrêmement utile pour l’analyse de données et le reporting. Nous expliquerons ici la syntaxe de base en détail avec des exemples pratiques.

Syntaxe de base de ROW_NUMBER()

Tout d’abord, le format de base de ROW_NUMBER() est le suivant.

SELECT
    column_name,
    ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY sort_column) AS row_num
FROM
    table_name;

Signification de chaque élément

• ROW_NUMBER() : attribue un numéro séquentiel à chaque ligne.
• OVER : mot‑clé utilisé pour définir la fenêtre d’une fonction de fenêtre.
• PARTITION BY : regroupe les données selon la colonne spécifiée. Optionnel. Si omis, la numérotation s’applique à l’ensemble des lignes.
• ORDER BY : définit l’ordre utilisé pour attribuer les numéros, c’est‑à‑dire le critère de tri.

Exemple de base

Par exemple, supposons que vous avez une table nommée « sales » contenant les données suivantes.

Pour attribuer des numéros séquentiels au sein de chaque département en ordre décroissant des ventes, utilisez la requête suivante.

SELECT
    employee,
    department,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS row_num
FROM
    sales;

Résultat

À partir de ce résultat, vous pouvez voir que les classements par ventes au sein de chaque département sont affichés.

Comment utiliser PARTITION BY

Dans l’exemple ci‑dessus, les données sont regroupées par la colonne « department ». Cela attribue une séquence distincte pour chaque département.

Si vous omettez PARTITION BY, la numérotation est appliquée à l’ensemble des lignes comme une seule séquence.

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (ORDER BY sale DESC) AS row_num
FROM
    sales;

Résultat

Caractéristiques et limites de ROW_NUMBER()

• Numérotation unique : même si les valeurs sont identiques, les numéros attribués restent uniques.
• Gestion des NULL : si ORDER BY comprend des NULL, ils apparaissent en premier en ordre ascendant et en dernier en ordre descendant.
• Impact sur les performances : pour de grands ensembles de données, ORDER BY peut être coûteux, il est donc important d’avoir des index appropriés.

3. Cas d’utilisation pratiques

Voici des scénarios pratiques utilisant la fonction ROW_NUMBER() de MySQL. Cette fonction est utile dans de nombreux cas concrets, comme le classement de données et la gestion des doublons.

3-1. Classement au sein de chaque groupe

Par exemple, considérez le cas où vous souhaitez « classer les employés par ventes au sein de chaque département » en utilisant les données de ventes. Utilisez le jeu de données suivant comme exemple.

Exemple de requête : Classement des ventes par département

SELECT
    employee,
    department,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

Résultat :

De cette façon, chaque département obtient sa propre séquence en ordre décroissant des ventes, ce qui facilite la génération des classements.

3-2. Extraction des N premières lignes

Ensuite, examinons le cas où vous souhaitez « extraire les 3 meilleurs employés par ventes au sein de chaque département ».

Exemple de requête : Extraire les N premières lignes

WITH RankedSales AS (
    SELECT
        employee,
        department,
        sale,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
    FROM
        sales
)
SELECT
    employee,
    department,
    sale
FROM
    RankedSales
WHERE
    rank <= 3;

Résultat :

Cet exemple récupère uniquement les 3 premières lignes par ventes au sein de chaque département. Comme vous pouvez le constater, ROW_NUMBER() convient non seulement au classement mais aussi au filtrage des meilleurs résultats.

3-3. Recherche et suppression des données en double

Les bases de données contiennent parfois des enregistrements en double. Dans ces cas, vous pouvez également les gérer facilement en utilisant ROW_NUMBER().

Exemple de requête : Détecter les doublons

SELECT *
FROM (
    SELECT
        employee,
        sale,
        ROW_NUMBER() OVER (PARTITION BY employee ORDER BY sale DESC) AS rank
    FROM
        sales
) tmp
WHERE rank > 1;

Cette requête détecte les doublons lorsqu’il existe plusieurs enregistrements pour le même nom d’employé.

Exemple de requête : Supprimer les doublons

DELETE FROM sales
WHERE id IN (
    SELECT id
    FROM (
        SELECT
            id,
            ROW_NUMBER() OVER (PARTITION BY employee ORDER BY sale DESC) AS rank
        FROM
            sales
    ) tmp
    WHERE rank > 1
);

Résumé

ROW_NUMBER() est utile dans divers scénarios, tels que :

1. Classement au sein de chaque groupe
2. Extraction des N premières lignes
3. Détection et suppression des doublons

Cela rend le traitement et l’analyse de données complexes plus simples et plus efficaces.

4. Comparaison avec d’autres fonctions de fenêtre

Dans MySQL 8.0, en plus de ROW_NUMBER(), il existe des fonctions de fenêtre comme RANK() et DENSE_RANK() qui peuvent être utilisées pour les classements et les calculs de position. Bien qu’elles aient des rôles similaires, leur comportement et leurs résultats diffèrent. Ici, nous comparerons chaque fonction et expliquerons quand les utiliser.

4-1. Fonction RANK()

La fonction RANK() attribue des rangs, donnant le même rang aux valeurs égales et sautant le rang suivant.

Syntaxe de base

SELECT
    column_name,
    RANK() OVER (PARTITION BY group_column ORDER BY sort_column) AS rank
FROM
    table_name;

Exemple

En utilisant les données suivantes, calculez les rangs de ventes.

Exemple de requête : Utilisation de RANK()

SELECT
    employee,
    sale,
    RANK() OVER (ORDER BY sale DESC) AS rank
FROM
    sales;

Résultat :

Points clés :

• A et B avec le même montant de ventes (800) sont tous deux traités comme rang « 1 ».
• Le rang suivant « 2 » est sauté, donc C devient rang « 3 ».

4-2. Fonction DENSE_RANK()

La fonction DENSE_RANK() attribue également le même rang aux valeurs égales, mais elle ne saute pas le rang suivant.

Syntaxe de base

SELECT
    column_name,
    DENSE_RANK() OVER (PARTITION BY group_column ORDER BY sort_column) AS dense_rank
FROM
    table_name;

Exemple

En utilisant les mêmes données que ci‑dessus, essayez la fonction DENSE_RANK().

Exemple de requête : Utilisation de DENSE_RANK()

SELECT
    employee,
    sale,
    DENSE_RANK() OVER (ORDER BY sale DESC) AS dense_rank
FROM
    sales;

Résultat :

Points clés :

• A et B avec le même montant de ventes (800) sont tous deux traités comme rang « 1 ».
• Contrairement à RANK(), le rang suivant commence à « 2 », préservant ainsi la continuité des rangs.

4-3. Comment ROW_NUMBER() diffère

La fonction ROW_NUMBER() diffère des deux autres en ce qu’elle attribue un numéro unique même lorsque les valeurs sont identiques.

Exemple

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (ORDER BY sale DESC) AS row_num
FROM
    sales;

Résultat :

Points clés :

• Même si les valeurs sont identiques, chaque ligne reçoit un numéro unique, il n’y a donc pas de rangs dupliqués.
• Cela est utile lorsque vous avez besoin d’un contrôle strict de l’ordre ou d’une unicité par ligne.

4-4. Résumé rapide des cas d’utilisation

Résumé

ROW_NUMBER(), RANK() et DENSE_RANK() doivent être utilisés de manière appropriée en fonction du scénario.

1. ROW_NUMBER() est idéal lorsque vous avez besoin de numéros uniques par ligne.
2. RANK() est utile lorsque vous voulez que les égalités partagent un rang et que vous souhaitez souligner les écarts de rang.
3. DENSE_RANK() est adapté lorsque vous voulez des rangs continus sans écarts.

5. Alternatives pour les versions de MySQL inférieures à 8.0

Dans les versions antérieures à MySQL 8.0, ROW_NUMBER() et les autres fonctions de fenêtre ne sont pas prises en charge. Cependant, vous pouvez obtenir un comportement similaire en utilisant des variables définies par l’utilisateur. Cette section explique des alternatives pratiques pour les versions de MySQL inférieures à 8.0.

5-1. Numérotation séquentielle à l’aide de variables définies par l’utilisateur

Dans MySQL 5.7 et versions antérieures, vous pouvez utiliser des variables définies par l’utilisateur pour attribuer des numéros séquentiels par ligne. Regardons l’exemple suivant.

Exemple : Classement des ventes par département

Données d’exemple :

Requête :

SET @row_num = 0;
SET @dept = '';

SELECT
    employee,
    department,
    sale,
    @row_num := IF(@dept = department, @row_num + 1, 1) AS rank,
    @dept := department
FROM
    (SELECT * FROM sales ORDER BY department, sale DESC) AS sorted_sales;

Résultat :

5-2. Extraction des N premières lignes

Pour récupérer les N premières lignes, vous pouvez utiliser des variables définies par l’utilisateur de manière similaire.

Requête :

SET @row_num = 0;
SET @dept = '';

SELECT *
FROM (
    SELECT
        employee,
        department,
        sale,
        @row_num := IF(@dept = department, @row_num + 1, 1) AS rank,
        @dept := department
    FROM
        (SELECT * FROM sales ORDER BY department, sale DESC) AS sorted_sales
) AS ranked_sales
WHERE rank <= 3;

Résultat :

Cette requête attribue des rangs par département, puis n’extrait que les lignes dans le top 3.

5-3. Détection et suppression des doublons

Vous pouvez également gérer les données en double à l’aide de variables définies par l’utilisateur.

Exemple de requête : Détecter les doublons

SET @row_num = 0;
SET @id_check = '';

SELECT *
FROM (
    SELECT
        id,
        name,
        @row_num := IF(@id_check = name, @row_num + 1, 1) AS rank,
        @id_check := name
    FROM
        (SELECT * FROM customers ORDER BY name, id) AS sorted_customers
) AS tmp
WHERE rank > 1;

Exemple de requête : Supprimer les doublons

DELETE FROM customers
WHERE id IN (
    SELECT id
    FROM (
        SELECT
            id,
            @row_num := IF(@id_check = name, @row_num + 1, 1) AS rank,
            @id_check := name
        FROM
            (SELECT * FROM customers ORDER BY name, id) AS sorted_customers
    ) AS tmp
    WHERE rank > 1
);

5-4. Précautions lors de l’utilisation de variables définies par l’utilisateur

1. Dépendance à la session

• Les variables définies par l’utilisateur ne sont valides que dans la session actuelle. Elles ne peuvent pas être réutilisées dans différentes requêtes ou sessions.

1. Dépendance à l’ordre de traitement

• Les variables définies par l’utilisateur dépendent de l’ordre d’exécution, il est donc crucial de définir ORDER BY correctement.

1. Lisibilité et maintenabilité du SQL

• Les requêtes peuvent devenir complexes, il est donc recommandé d’utiliser les fonctions de fenêtre dans MySQL 8.0 et versions ultérieures.

Résumé

Dans les versions de MySQL antérieures à 8.0, vous pouvez utiliser des variables définies par l’utilisateur pour implémenter une numérotation séquentielle et un classement à la place des fonctions de fenêtre. Cependant, comme les requêtes ont tendance à devenir plus complexes, il est préférable d’envisager une migration vers une version plus récente chaque fois que possible.

6. Mise en garde et bonnes pratiques

MySQL’s ROW_NUMBER() function and variable-based alternatives are very convenient, but there are important points to keep in mind to run them accurately and efficiently. This section explains practical caveats and best practices for performance optimization.

La fonction ROW_NUMBER() de MySQL et les alternatives basées sur des variables sont très pratiques, mais il y a des points importants à garder à l’esprit pour les exécuter avec précision et efficacité. Cette section explique les mises en garde pratiques et les bonnes pratiques pour l’optimisation des performances.

6-1. Considérations de performance

1. Coût de ORDER BY

ROW_NUMBER() est toujours utilisé avec ORDER BY. Comme il nécessite un tri, le temps de traitement peut augmenter de façon significative pour de grands ensembles de données.

Atténuation :

• Utiliser des index : Ajoutez des index aux colonnes utilisées dans ORDER BY pour accélérer le tri.
• Utiliser LIMIT : Récupérez uniquement le nombre de lignes dont vous avez réellement besoin afin de réduire la quantité de données traitées.

Exemple :

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales
LIMIT 1000;

2. Augmentation de l’utilisation de la mémoire et des E/S disque

Les fonctions de fenêtre sont traitées à l’aide de tables temporaires et de la mémoire. À mesure que le volume de données augmente, la consommation de mémoire et les E/S disque peuvent croître.

Atténuation :

• Diviser les requêtes : Découpez le traitement en requêtes plus petites et extrayez les données étape par étape pour réduire la charge.
• Utiliser des tables temporaires : Stockez les données extraites dans une table temporaire et effectuez l’agrégation à partir de celle-ci afin de répartir la charge de travail.

6-2. Astuces d’optimisation des requêtes

1. Vérifier le plan d’exécution

Dans MySQL, vous pouvez utiliser EXPLAIN pour vérifier le plan d’exécution de la requête. Cela vous aide à vérifier si les index sont utilisés correctement.

Exemple :

EXPLAIN
SELECT
    employee,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

Exemple de sortie :

Si vous voyez Using index, cela indique que l’index est utilisé de manière appropriée.

2. Optimiser les index

Assurez-vous d’ajouter des index aux colonnes utilisées dans ORDER BY et WHERE. Portez une attention particulière aux éléments suivants.

• Index à colonne unique : Bon pour les conditions de tri simples
• Index composites : Efficaces lorsque plusieurs colonnes sont impliquées dans les conditions

Exemple :

CREATE INDEX idx_department_sale ON sales(department, sale DESC);

3. Utiliser le traitement par lots

Au lieu de traiter un énorme ensemble de données en une seule fois, vous pouvez réduire la charge en traitant les données par lots.

Exemple :

SELECT * FROM sales WHERE department = 'Sales Department' LIMIT 1000 OFFSET 0;
SELECT * FROM sales WHERE department = 'Sales Department' LIMIT 1000 OFFSET 1000;

6-3. Maintenir la cohérence des données

1. Mises à jour et recalcul

Lorsque des lignes sont insérées ou supprimées, la numérotation peut changer. Mettez en place un mécanisme pour recalculer les numéros au besoin.

Exemple :

CREATE VIEW ranked_sales AS
SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

L’utilisation d’une vue vous aide à maintenir les classements à jour en fonction des dernières données.

6-4. Exemple de requête selon les meilleures pratiques

Voici un exemple de meilleures pratiques qui prennent en compte la performance et la maintenabilité.

Exemple : Extraire les N premières lignes

WITH RankedSales AS (
    SELECT
        employee,
        department,
        sale,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
    FROM
        sales
)
SELECT *
FROM RankedSales
WHERE rank <= 3;

Cette structure utilise une expression de table commune (CTE) pour améliorer la lisibilité et la réutilisabilité.

Résumé

Lors de l’utilisation de ROW_NUMBER() ou de ses alternatives, gardez ces points à l’esprit :

1. Améliorer la vitesse grâce à l’optimisation des index .
2. Identifier les goulots d’étranglement en vérifiant le plan d’exécution .
3. Planifier les mises à jour des données et maintenir la cohérence.
4. Utiliser le traitement par lots et les CTE pour répartir la charge.

Appliquer ces meilleures pratiques permettra un traitement efficace pour l’analyse et le reporting de données à grande échelle.

7. Conclusion

Dans cet article, nous nous sommes concentrés sur la fonction ROW_NUMBER() de MySQL, en expliquant tout, de l’utilisation de base et des exemples pratiques aux alternatives pour les versions plus anciennes, ainsi que les mises en garde et les meilleures pratiques. Dans cette section, nous récapitulerons les points clés et résumerons les enseignements pratiques.

7-1. Pourquoi ROW_NUMBER() est utile

La fonction ROW_NUMBER() est particulièrement pratique pour l’analyse et le reporting de données de la manière suivante :

1. Numérotation séquentielle au sein des groupes : Créez facilement des classements de ventes par département ou des classements basés sur les catégories.
2. Extraction des N premières lignes : Filtrez et extrayez efficacement les données selon des conditions spécifiques.
3. Détection et suppression des doublons : Utile pour le nettoyage et l’organisation des données.

Parce qu’elle simplifie les requêtes complexes, elle améliore considérablement la lisibilité et la maintenabilité du SQL.

7-2. Comparaison avec d’autres fonctions de fenêtre

Comparée aux fonctions de fenêtre telles que RANK() et DENSE_RANK(), ROW_NUMBER() se distingue en attribuant un numéro unique même pour des valeurs identiques.

Choisir la bonne fonction :
Sélectionner la fonction la plus adaptée à votre besoin permet un traitement efficace des données.

7-3. Gestion des versions MySQL plus anciennes

Pour les environnements antérieurs à MySQL 8.0, nous avons également présenté des approches utilisant des variables définies par l’utilisateur. Cependant, vous devez prendre en compte les mises en garde suivantes :

• Lisibilité réduite en raison d’un SQL plus complexe
• L’optimisation des requêtes peut être plus difficile dans certains cas
• Un traitement supplémentaire peut être nécessaire pour maintenir la cohérence des données

Si possible, envisagez fortement de migrer vers MySQL 8.0 ou une version ultérieure et d’utiliser les fonctions de fenêtre.

7-4. Points clés pour l’optimisation des performances

1. Utiliser les index : Ajoutez des index aux colonnes utilisées dans ORDER BY pour améliorer la vitesse.
2. Vérifier les plans d’exécution : Validez les performances à l’avance avec EXPLAIN.
3. Adopter le traitement par lots : Traitez les grands ensembles de données en plus petits morceaux pour répartir la charge.
4. Utiliser les vues et les CTE : Améliorez la réutilisabilité et simplifiez les requêtes complexes.

En appliquant ces techniques, vous pouvez obtenir un traitement des données efficace et stable.

7-5. Notes finales

ROW_NUMBER() est un outil puissant qui peut améliorer considérablement l’efficacité de l’analyse de données.
Dans cet article, nous avons couvert tout, de la syntaxe de base et des exemples pratiques aux mises en garde et aux alternatives.

Nous vous encourageons à exécuter vous-même les requêtes tout en suivant cet article. Améliorer vos compétences en SQL vous aidera à aborder des analyses et des reportings de données plus complexes avec confiance.

Annexe : Ressources de référence

• Documentation officielle : MySQL Window Functions
• Environnement SQL en ligne : SQL Fiddle (un outil qui vous permet d’exécuter et de tester du SQL en ligne)