Identifier et Gérer les Doublons dans une Base de Données avec SQL
Avoir des données en double peut avoir différentes conséquences: augmentation de la taille des tables, ralentissement des requêtes, données incorrectes, exécution problématique du code,...
Il est donc important de les surveiller et de les faire disparaître!
Les différents type de doublons
Dans une base de données, les doublons peuvent prendre différentes formes qui selon votre usage sont légitime ou non.
- Doublons exacts: Des lignes totalement identiques, où toutes les colonnes contiennent les mêmes valeurs.
- Doublons partiels: Des enregistrements similaires sur certaines colonnes, mais différant par une ou plusieurs, comme un identifiant unique (id).
- Doublon proches: Des lignes presque identiques, avec des différences mineures comme des fautes de frappe, des espaces superflus ou des variations de casse.
Donnée test
Pour illustrer les exemples qui vont suivre, je vous propose ce tableau de données contenant les colonnes: id, nom, type1, type2 et region.
+----+------------+------------+--------+--------+
| id | nom | type1 | type2 | region |
+----+------------+------------+--------+--------+
| 1 | Bulbizarre | Plante | Poison | Kanto |
| 2 | Herbizarre | Plante | Poison | Kanto |
| 3 | Florizarre | Plante | Poison | Kanto |
| 4 | Salamèche | Feu | NULL | Kanto |
| 5 | Reptincel | Feu | NULL | Kanto |
| 6 | Dracaufeu | Feu | Vol | Kanto |
| 7 | Pikachu | Électrique | NULL | Kanto |
| 8 | Raichu | Électrique | NULL | Kanto |
| 9 | Pikachu | Électrique | NULL | Kanto |
| 10 | Dracaufeu | Feu | Vol | Kanto |
+----+------------+------------+--------+--------+Voici le script permettant de générer la table et de la remplir
-- Création de la table Pokemon CREATE TABLE pokemon ( id INT PRIMARY KEY, -- Identifiant unique pour chaque Pokémon nom VARCHAR(50) NOT NULL, -- Nom du Pokémon type1 VARCHAR(50) NOT NULL, -- Type principal type2 VARCHAR(50), -- Type secondaire (peut être NULL) region VARCHAR(100) NOT NULL -- Région d'origine ); -- Insertion des données dans la table Pokemon INSERT INTO pokemon (id, nom, type1, type2, region) VALUES (1, 'Bulbizarre', 'Plante', 'Poison', 'Kanto'), (2, 'Herbizarre', 'Plante', 'Poison', 'Kanto'), (3, 'Florizarre', 'Plante', 'Poison', 'Kanto'), (4, 'Salamèche', 'Feu', NULL, 'Kanto'), (5, 'Reptincel', 'Feu', NULL, 'Kanto'), (6, 'Dracaufeu', 'Feu', 'Vol', 'Kanto'), (7, 'Pikachu', 'Électrique', NULL, 'Kanto'), (8, 'Raichu', 'Électrique', NULL, 'Kanto'), (9, 'Pikachu', 'Électrique', NULL, 'Kanto'), -- Doublon exact avec id=7 (10, 'Dracaufeu', 'Feu', 'Vol', 'Kanto'); -- Quasi-doublon avec id=6
Identifier les doublons
Nous allons donc voir comment trouver les doublons en fonction de nos besoin.
Doublons exacts
Les doublons exacts peuvent être identifiés en regroupant les colonnes et en comptant le nombre d’occurrences de chaque combinaison. Voici une requête pour les repérer :
SELECT COUNT(*) AS nbr_doublon, nom, type1, type2, region
FROM pokemon
GROUP BY nom, type1, type2, region
HAVING COUNT(*) > 1;Cette requête retourne uniquement les groupes de lignes qui apparaissent plus d’une fois.
+-------------+-----------+------------+-------+--------+
| nbr_doublon | nom | type1 | type2 | region |
+-------------+-----------+------------+-------+--------+
| 2 | Dracaufeu | Feu | Vol | Kanto |
| 2 | Pikachu | Électrique | NULL | Kanto |
+-------------+-----------+------------+-------+--------+Doublons partiels
Pour détecter des doublons où seules certaines colonnes diffèrent, comme l’id, on peut utiliser une sous-requête avec la commande EXISTS. Voici un exemple :
SELECT DISTINCT *
FROM pokemon t1
WHERE EXISTS (
SELECT 1
FROM pokemon t2
WHERE t1.id <> t2.id
AND t1.nom = t2.nom
AND t1.type1 = t2.type1
AND t1.type2 = t2.type2
AND t1.region = t2.region );Cette requête compare chaque ligne avec toutes les autres pour identifier les doublons partiels.
+----+-----------+-------+-------+--------+
| id | nom | type1 | type2 | region |
+----+-----------+-------+-------+--------+
| 10 | Dracaufeu | Feu | Vol | Kanto |
| 6 | Dracaufeu | Feu | Vol | Kanto |
+----+-----------+-------+-------+--------+Doublon proches
Les doublons proches, comme ceux causés par des espaces superflus ou des différences de casse, nécessitent une manipulation des données. Voici un exemple pour détecter les doublons en ignorant la casse et les espaces inutiles :
SELECT t1.id, t2.id, t1.nom, t2.nom
FROM pokemon t1
JOIN pokemon t2
ON LOWER(TRIM(t1.nom)) = LOWER(TRIM(t2.nom))
AND t1.id <> t2.id;Résultats:
+----+----+-----------+-----------+
| id | id | nom | nom |
+----+----+-----------+-----------+
| 10 | 6 | Dracaufeu | Dracaufeu |
| 9 | 7 | Pikachu | Pikachu |
| 7 | 9 | Pikachu | Pikachu |
| 6 | 10 | Dracaufeu | Dracaufeu |
+----+----+-----------+-----------+Doublons dans une colonne spécifique
Si vous souhaitez détecter les doublons sur une seule colonne, comme le nom des Pokémon, voici une requête adaptée :
SELECT COUNT(nom) AS nbr_doublon, nom
FROM pokemon
GROUP BY nom
HAVING COUNT(nom) > 1;Résultats:
+-------------+-----------+
| nbr_doublon | nom |
+-------------+-----------+
| 2 | Dracaufeu |
| 2 | Pikachu |
+-------------+-----------+Éviter les doublons à l’avenir
Pour éviter les doublons, vous pouvez mettre en place des mécanismes de contrôle dès la création de vos tables :
ALTER TABLE pokemon ADD CONSTRAINT unique_nom_type_region UNIQUE (nom, type1, type2, region);Nettoyage automatique
Supprimer les doublons tout en conservant une seule instance :
DELETE FROM pokemon
WHERE id NOT IN (
SELECT MIN(id)
FROM pokemon
GROUP BY nom, type1, type2, region
);Conclusion
Bien que fastidieux, gérer les doublons reste une opération obligatoire pour garder une base de données fiable et performante. Heureusement, comme vous l’aurez compris, quelques scripts suffisent à les gérer et les faire disparaître!
J’espère qu’ils vous seront utiles !