SQL Supprimer les doublons : Méthodes globales et meilleures pratiques

Les enregistrements en double sont un problème courant qui peut compromettre l'intégrité des données et les performances de la base de données. La suppression de ces doublons est essentielle pour maintenir l'exactitude des données, optimiser le stockage et améliorer les performances des requêtes. Dans cet article, nous allons explorer différentes techniques de suppression des lignes dupliquées en SQL, adaptées à différents cas d'utilisation et systèmes de gestion de bases de données.

Pour commencer, je vous recommande vivement de suivre les cours Introduction à SQL et Apprendre SQL de DataCamp pour acquérir les connaissances de base sur l'extraction et l'analyse de données à l'aide de SQL. De plus, je trouve que l' aide-mémoire SQL Basics, que vous pouvez télécharger, est une référence utile car il contient toutes les fonctions SQL les plus courantes.

Comprendre les lignes dupliquées en SQL

En SQL, les tableaux en double font référence aux enregistrements d'un tableau qui contiennent des valeurs identiques dans toutes les colonnes ou dans certaines d'entre elles. Les causes courantes des lignes dupliquées dans SQL sont les suivantes :

• Clés primaires manquantes : Lorsque les tableaux n'ont pas de clé primaire définie ou de contrainte unique, il n'existe aucun mécanisme pour empêcher l'insertion de données en double. Cela peut se produire lorsqu'un tableau n'est pas normalisé et/ou qu'il existe des problèmes de dépendance transitive.
• Questions relatives à l'intégration des données : Lors de la fusion d'ensembles de données provenant de différentes sources, des jointures incorrectes ou des incohérences dans les formats de données peuvent accidentellement introduire des doublons.
• Erreurs de saisie manuelle des données : L'erreur humaine, telle que la saisie multiple du même enregistrement, est une autre cause fréquente de doublons.

Dans la suite de l'article, nous verrons comment supprimer les doublons dans SQL, et nous diviserons l'article en deux blocs. Dans la première section, nous verrons comment supprimer les doublons dans les données que vous récupérez pour un rapport ou un tableau de bord ; dans la deuxième section, nous verrons comment supprimer les doublons dans la base de données.

Méthodes de suppression des doublons dans les données que vous récupérez

Il existe différentes méthodes pour supprimer les doublons lors de l'extraction d'enregistrements dans SQL. Chaque méthode dépend du SGBD, tel que SQL Server, MySQL et PostgreSQL. Dans cette section, nous examinerons les méthodes de suppression des doublons tout en soulignant les particularités de chaque base de données. Gardez à l'esprit que ces méthodes filtrent les données et renvoient des enregistrements uniques et qu'elles ne modifient pas le tableau sous-jacent.

Utilisation du mot clé DISTINCT

Le mot-clé DISTINCT est utilisé dans une instruction SELECT pour extraire des lignes uniques. La syntaxe du mot-clé DISTINCT pour la suppression des doublons est similaire pour les bases de données MySQL, PostgreSQL et SQL Server. La requête ci-dessous permet d'extraire des noms de clients uniques du tableau customers.

SELECT DISTINCT Name 
FROM customers;

Utilisation de GROUP BY avec des fonctions d'agrégation

La clause GROUP BY, combinée à d'autres fonctions d'agrégation telles que MAX(), MIN() ou COUNT(), peut aider à supprimer les enregistrements en double dans les tableaux. La clause GROUP BY permet de sélectionner des enregistrements spécifiques à conserver tout en supprimant les autres doublons.

Supposons que vous souhaitiez supprimer les enregistrements de clients en double mais conserver celui dont l'ID est le plus élevé. Vous utiliserez la clause GROUP BY avec la fonction MAX(), comme indiqué ci-dessous.

-- Delete duplicate rows from the 'customers' table (aliased as c1)
DELETE c1
FROM customers c1
-- Find the maximum ID for each unique Name
JOIN (
    SELECT Name, MAX(ID) AS MaxID
    FROM customers
    GROUP BY Name
) c2
-- Match rows based on 'Name' and keep the row with the maximum ID
ON c1.Name = c2.Name 
AND c1.ID < c2.MaxID;

MySQL et SQL Server prennent en charge la syntaxe ci-dessus de GROUP BY avec les fonctions d'agrégation et la clause JOIN. 

Utilisation de ROW_NUMBER() avec des expressions de tableau communes (CTE)

La fonction ROW_NUMBER() combinée à une expression de tableau commune (CTE) vous permet de filtrer les doublons en fonction de vos critères. La fonction ROW_NUMBER, lorsqu'elle est utilisée avec les clauses PARTITION BY et ORDER BY, attribue un numéro séquentiel unique à chaque ligne. Cette méthode permet de filtrer les lignes qui ne répondent pas aux critères requis.

La requête suivante identifie les doublons et supprime toutes les occurrences sauf la première.

-- Common Table Expression (CTE) to rank rows based on 'Name'
WITH CTE AS (
    SELECT ID, Name, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY ID ASC) AS RowNum
    FROM customers
)
-- Select only the unique records where RowNum = 1
SELECT ID, Name
FROM CTE
WHERE RowNum = 1;

Cette méthode fonctionne bien avec les versions modernes de SQL Server, MySQL et PostgreSQL. Elle est utile pour les grands ensembles de données ou les conditions plus complexes, car elle vous permet de spécifier exactement les doublons à conserver.

Suppression des doublons à l'aide de self-JOIN

Une jointure automatique vous permet de comparer un tableau à lui-même, ce qui est utile pour identifier et supprimer les lignes en double en comparant les enregistrements sur la base de critères spécifiques. L'exemple suivant utilise l'auto-jonction pour supprimer la ligne dont l'ID est le plus élevé, en ne conservant que la première occurrence de chaque nom.

-- Delete duplicate rows using self-join
DELETE c1
FROM customers c1
JOIN customers c2
ON c1.Name = c2.Name AND c1.ID > c2.ID;

La méthode ci-dessus fonctionne avec les principales bases de données, notamment SQL Server, MySQL et PostgreSQL. Consultez notre cours SQL intermédiaire pour en savoir plus sur l'utilisation des fonctions agrégées et des jointures pour filtrer les données.

Méthodes de suppression des doublons dans la base de données

Si vous pouvez supprimer les enregistrements en double à l'aide de requêtes, vous pouvez également les supprimer définitivement de la base de données. Cette approche est importante pour maintenir la qualité des données. Les méthodes suivantes sont utilisées pour supprimer les doublons de la base de données.

Utilisation de ROW_NUMBER() et DELETE

La fonction ROW_NUMBER() attribue un numéro séquentiel aux lignes d'une partition définie. Utilisée avec l'instruction DELETE, elle permet d'identifier les doublons en classant les lignes en fonction de colonnes spécifiques et en supprimant les enregistrements indésirables. Cette méthode s'applique aux versions modernes de MySQL (à partir de 8.0), PostgreSQL et SQL Server.

Supposons que vous souhaitiez supprimer les enregistrements de clients en double sur la base de la colonne Name, en ne conservant que la première occurrence (la plus petite ID) :

-- Common Table Expression (CTE) to rank rows based on 'Name'
WITH CTE AS (
    SELECT ID, Name, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY ID ASC) AS RowNum
    FROM customers
)
-- Delete rows from the 'customers' table where the row number is greater than 1
DELETE FROM customers
WHERE ID IN (SELECT ID FROM CTE WHERE RowNum > 1);

Utilisation de DELETE avec une sous-requête

Parfois, une simple opération DELETE à l'aide d'une sous-requête permet de supprimer les doublons de la base de données. Cette méthode convient aux anciennes versions de MySQL ou PostgreSQL pour lesquelles ROW_NUMBER() n'est pas disponible.

La requête ci-dessous supprime du tableau customers les tableaux dont l'adresse ID n'est pas la plus petite pour chaque Name, en ne conservant que la ligne contenant l'adresse ID la plus petite pour chaque Name unique.

-- Delete rows from the 'customers' table
DELETE FROM customers
WHERE ID NOT IN (
    -- Subquery to find the minimum ID for each unique Name
    SELECT MIN(ID)
    FROM customers
    GROUP BY Name
);

Utilisation de la clause GROUP BY avec HAVING

Lorsque vous devez rechercher des valeurs en double dans des colonnes spécifiques, la clause GROUP BY combinée à la clause HAVING peut être utilisée pour identifier les doublons. Cette méthode vous permet de supprimer des lignes spécifiques en fonction de critères donnés. Cette méthode est compatible avec SQL Server, MySQL et PostgreSQL.

La requête suivante supprime les tableaux du tableau customers dont le ID appartient à un groupe de doublons.

-- Delete rows from the 'customers' table where there are duplicates
DELETE FROM customers
WHERE ID IN (
    -- Subquery to find IDs of duplicate rows
    SELECT ID
    FROM customers
    GROUP BY ID
    HAVING COUNT(*) > 1
);

Utilisation de tableaux temporaires pour le traitement par lots

Les tableaux temporaires sont efficaces pour le traitement par lots et la suppression des doublons dans les grands ensembles de données. Cette méthode est utile lorsque des requêtes uniques peuvent entraîner des problèmes de performance. La requête suivante crée un tableau temporaire pour stocker le minimum ID pour chaque customer_name et supprime les tableaux du tableau customers lorsque le ID n'est pas dans le tableau temp_customers.

-- Create a temporary table
CREATE TEMPORARY TABLE temp_customers AS
SELECT MIN(customer_id) AS ID, customer_name
FROM customers
GROUP BY customer_name;
DELETE FROM customers
WHERE customer_id NOT IN (SELECT ID FROM temp_customers);

La syntaxe ci-dessus, qui utilise CREATE TEMPORARY TABLE, n'est prise en charge que dans les bases de données MySQL et PostgreSQL. 

Supprimez les doublons dans le serveur SQL

SQL Server propose différentes méthodes pour supprimer les enregistrements en double de la base de données. Ces méthodes comprennent l'utilisation de DISTINCT avec INTO, ROW_NUMBER(), et des tableaux temporaires.

Utilisation de DISTINCT avec INTO

Vous pouvez utiliser le mot-clé DISTINCT dans une instruction SELECT pour créer un nouveau tableau avec des enregistrements uniques. Vous pouvez supprimer l'ancien tableau une fois que vous avez vérifié que le nouveau tableau contient les enregistrements spécifiés. L'exemple suivant crée le tableau unique_customers avec des tableaux uniques provenant du tableau customers.

-- Select distinct rows from 'customers' and create a new table 'unique_customers'
SELECT DISTINCT *
INTO unique_customers
FROM customers;
-- Drop the original 'customers' table to remove it from the database
DROP TABLE customers;
-- Rename the 'unique_customers' table to 'customers' to replace the original table
EXEC sp_rename 'unique_customers', 'customers';

Utilisation de ROW_NUMBER()

Vous pouvez également utiliser la fonction ROW_NUMBER() pour supprimer les enregistrements en double du serveur SQL. Supposons que vous ayez un tableau Customers avec des lignes dupliquées basées sur la colonne CustomerName, et que vous souhaitiez supprimer toutes les occurrences sauf la première pour chaque groupe dupliqué.

-- Common Table Expression (CTE) to assign a row number to each customer 
WITH CTE AS (
    SELECT CustomerID, CustomerName, ROW_NUMBER() OVER (PARTITION BY CustomerName ORDER BY CustomerID ASC) AS RowNum
    FROM Customers
)
-- Delete rows from the CTE
DELETE FROM CTE
WHERE RowNum > 1;

Utilisation d'un tableau temporaire

Comme SQL Server ne prend pas en charge la fonction CREATE TEMPORARY TABLE, vous utilisez la fonction SELECT INTO. Les tableaux temporaires de SQL Server utilisent # comme préfixe pour le nom du tableau.

-- Create a temporary table
SELECT MIN(CustomerID) AS ID, CustomerName
INTO #temp_customers
FROM customers
GROUP BY CustomerName;
-- Delete rows from the 'customers' table where the ID is not in the temporary table
DELETE FROM customers
WHERE CustomerIDNOT IN (SELECT ID FROM #temp_customers);
-- Optionally drop the temporary table after use
DROP TABLE #temp_customers;

Je vous suggère d'essayer notre cursus de compétences SQL Server Fundamentals pour améliorer vos compétences en matière de tableaux de jointure et d'analyse de données. Le cursus de développeur SQL Server vous permettra d'acquérir les compétences nécessaires pour écrire, dépanner et optimiser vos requêtes à l'aide de SQL Server.  

Meilleures pratiques

Les lignes en double sont un problème courant qui affecte la qualité des données et les performances des bases de données. Prenez en compte les meilleures pratiques suivantes pour éviter l'insertion d'enregistrements en double dans votre base de données.

• Utilisez des clés primaires : La colonne de la clé primaire garantit que chaque enregistrement contient des informations uniques, empêchant ainsi les valeurs en double d'entrer dans le tableau.
• Mettre en œuvre des contraintes uniques : L'application de contraintes uniques à n'importe quelle colonne permet de s'assurer qu'il n'existe pas de doublons dans les colonnes qui ne sont pas des clés primaires, telles que les adresses électroniques ou les numéros de téléphone.
• Conception correcte de la base de données et normalisation : Une conception efficace des schémas et une normalisation de la base de données permettent de réduire la redondance et les données en double. Cette approche garantit que chaque enregistrement est stocké dans des tableaux spécifiques.
• Utilisez des index uniques : Utilisez des index uniques pour vous assurer que certaines combinaisons de colonnes sont uniques sans avoir besoin de contraintes complètes au niveau des tableaux pour l'ensemble du jeu de données.
• Audits réguliers des données : Effectuez régulièrement des audits de données en exécutant des requêtes pour identifier les doublons potentiels sur la base de vos règles de gestion.

Conclusion

L'identification et la suppression des lignes en double sont importantes pour maintenir l'efficacité de la base de données et l'exactitude des données. Il est toujours préférable de sauvegarder vos données avant d'effectuer des modifications afin d'éviter toute perte accidentelle de données.

Si vous souhaitez devenir un analyste de données compétent, consultez notre cursus d'analyste de données associé en SQL pour acquérir les compétences nécessaires. Le cours Reporting in SQL est également approprié si vous souhaitez apprendre à construire des tableaux de bord professionnels à l'aide de SQL. Enfin, je vous recommande d'obtenir la certification SQL Associate pour démontrer votre maîtrise de l'utilisation de SQL pour l'analyse des données et vous démarquer parmi les autres professionnels des données.