SQL Interview Prep · Leçon

Dédupliquer les lignes en toute sécurité

Supprimer les lignes strictement identiques ou presque dupliquées tout en conservant un enregistrement de référence

Dédupliquer les lignes en toute sécurité est une leçon SQL Interview Prep gratuite sur CoddyKit. Ceci est la leçon 3 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage SQL Interview Prep, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours SQL Interview Prep comprend 4 leçons au total.

Le problème de déduplication

« Cette table contient des lignes en double. Supprimez-les, mais conservez un exemplaire de chacune. » Presque chaque entretien d’ingénierie des données comporte une variante de cette question. Le défi consiste à le faire en toute sécurité : conserver exactement une ligne canonique et ne pas supprimer accidentellement des enregistrements distincts qui semblent simplement similaires.

Nous allons voir comment détecter les doublons, choisir la copie à conserver, dédupliquer dans un SELECT et supprimer physiquement les doublons d’une table.

Définir d’abord un doublon

La première question à poser à l’intervieweur est : « Qu’est-ce qui fait que deux lignes sont des doublons ? » Plusieurs possibilités existent :

Doublons exacts : toutes les colonnes sont identiques.
Doublons de clé : même clé métier (par ex. même email), mais les autres colonnes peuvent différer.

La technique diffère selon le cas. Ne présumez jamais de la définition ; la clarifier est l’étape la plus importante, et les intervieweurs s’attendent à ce que vous posiez la question.

Détecter les doublons

Pour trouver les clés en double, regroupez selon les colonnes qui définissent un doublon et ne conservez que les groupes dont le nombre est supérieur à un. Vous saurez ainsi quelles clés sont concernées et combien d’exemplaires existent avant de modifier quoi que ce soit.

Exécuter d’abord une requête de détection est une bonne pratique qu’il vaut la peine de mentionner : vous vérifiez l’ampleur du problème avant toute suppression.

SELECT email, COUNT(*) AS copies
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY copies DESC;

Doublons exacts : DISTINCT

Si les doublons sont réellement identiques sur toutes les colonnes, une vue dédupliquée en lecture seule s’obtient simplement avec SELECT DISTINCT *. UNION (sans ALL) supprime également les lignes en double.

Mais DISTINCT n’est utile que si vous souhaitez dédupliquer la ligne entière et n’avez pas besoin de choisir quel exemplaire conserver. Pour les doublons de clé dont les colonnes diffèrent, vous devez utiliser un classement.

-- Read-only dedup of exact-duplicate rows
SELECT DISTINCT customer_id, name, signup_date
FROM customers;

Doublons de clé : ROW_NUMBER

Lorsque les lignes partagent une clé mais diffèrent dans d’autres colonnes, partitionnez selon la clé et numérotez chaque exemplaire. rn = 1 désigne la ligne à conserver ; rn > 1 désigne les exemplaires supplémentaires à supprimer.

L’ORDER BY à l’intérieur de la fenêtre détermine quel exemplaire est canonique. Choisissez-le délibérément ; vous pouvez par exemple conserver la ligne mise à jour le plus récemment.

SELECT *,
  ROW_NUMBER() OVER (
    PARTITION BY email
    ORDER BY updated_at DESC
  ) AS rn
FROM users;

Sélectionner la copie canonique

Encapsulez la numérotation dans un CTE et ne conservez que rn = 1. Vous obtenez ainsi une ligne par clé, précisément celle que votre ORDER BY a classée en première position.

Cette forme de SELECT est non destructive : elle est idéale pour créer une vue propre ou alimenter une table cible dédupliquée avec un INSERT ... SELECT, sans modifier la source.

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY email ORDER BY updated_at DESC
    ) AS rn
  FROM users
)
SELECT user_id, email, name, updated_at
FROM ranked
WHERE rn = 1;

Le choix de l’ordre est important

L’ORDER BY à l’intérieur de la partition est une décision métier, pas une simple formalité :

ORDER BY updated_at DESC conserve l’enregistrement le plus récent.
ORDER BY created_at ASC conserve l’original.
ORDER BY id ASC conserve la clé de substitution la plus petite, ce qui est utile pour un choix arbitraire mais stable.

Ajoutez un critère de départage unique afin que la ligne choisie soit déterministe lorsque la colonne de tri principale présente elle aussi une égalité.

ROW_NUMBER() OVER (
  PARTITION BY email
  ORDER BY updated_at DESC, id ASC
) AS rn

Supprimer physiquement les doublons

Pour supprimer réellement les doublons de la table, identifiez les lignes supplémentaires (rn > 1) et supprimez-les. Dans PostgreSQL et SQL Server, vous pouvez supprimer des lignes à l’aide d’un CTE ; dans MySQL, une auto-jointure ou une sous-requête est courante.

Exécutez toujours d’abord le SELECT correspondant pour prévisualiser exactement les lignes qui vont disparaître. Supprimer à l’aveugle est la meilleure façon d’échouer à cette question.

WITH ranked AS (
  SELECT ctid,
    ROW_NUMBER() OVER (
      PARTITION BY email ORDER BY updated_at DESC, id ASC
    ) AS rn
  FROM users
)
DELETE FROM users
WHERE ctid IN (SELECT ctid FROM ranked WHERE rn > 1);

Modèle de suppression par auto-jointure

Une approche classique et portable conserve, pour chaque clé en double, la ligne dont l’id est le plus petit et supprime les autres au moyen d’une auto-jointure. Elle ne nécessite pas de fonctions de fenêtre, ce qui est important avec les anciens moteurs.

La condition de jointure associe chaque ligne à une autre ligne qui partage la même clé mais possède un id plus petit ; toute ligne ayant un tel doublon avec un identifiant inférieur doit être supprimée.

DELETE u1
FROM users u1
JOIN users u2
  ON u1.email = u2.email
 AND u1.id > u2.id;

Liste de contrôle de sécurité

Avant de supprimer, protégez-vous :

Placez la suppression dans une transaction afin de pouvoir effectuer un ROLLBACK si le nombre paraît incorrect.
Exécutez d’abord SELECT COUNT(*) sur les lignes à supprimer et vérifiez que le résultat est cohérent.
Envisagez une table de sauvegarde : CREATE TABLE users_bak AS SELECT * FROM users.
Vérifiez que vos colonnes PARTITION BY définissent réellement un doublon, sans quoi vous risquez de supprimer des enregistrements distincts.

BEGIN;
-- run the DELETE, inspect row count
-- COMMIT; if correct, otherwise ROLLBACK;

Quasi-doublons et normalisation

Parfois, les lignes ne sont pas exactement égales, mais sont logiquement identiques : 'Ann@X.com' contre 'ann@x.com', ou encore des espaces en fin de chaîne. Partitionnez selon une expression normalisée plutôt que selon la colonne brute.

Mentionner la normalisation montre votre maturité : dans la pratique, les doublons se cachent souvent derrière des différences de casse, d’espacement ou de formatage qu’une comparaison naïve des clés ne détecte pas.

ROW_NUMBER() OVER (
  PARTITION BY LOWER(TRIM(email))
  ORDER BY updated_at DESC, id ASC
) AS rn

Vérification rapide

Choisissez l’approche de déduplication sûre.

Récapitulatif : déduplication sûre

Dédupliquez méthodiquement :

Commencez par définir ce qu’est un doublon, puis détectez-les avec GROUP BY / HAVING COUNT(*) > 1.
Doublons exacts → DISTINCT. Doublons de clé → ROW_NUMBER partitionné selon la clé, en conservant rn = 1.
L’ORDER BY de la fenêtre choisit la copie canonique ; ajoutez un critère de départage unique.
Supprimez les lignes rn > 1 dans une transaction après avoir prévisualisé le nombre concerné.
Normalisez les clés pour détecter les quasi-doublons.

Questions Fréquemment Posées

La leçon « Dédupliquer les lignes en toute sécurité » est-elle gratuite ?

Oui — le texte complet de « Dédupliquer les lignes en toute sécurité » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours SQL Interview Prep, passe à CoddyKit PRO. Le cours SQL Interview Prep comprend 4 leçons au total.

Qu'est-ce que j'apprendrai dans « Dédupliquer les lignes en toute sécurité » ?

Supprimer les lignes strictement identiques ou presque dupliquées tout en conservant un enregistrement de référence Tu pratiques SQL Interview Prep avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.

Dois-je avoir de l'expérience pour commencer SQL Interview Prep ?

Aucune expérience préalable n'est requise. SQL Interview Prep sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 3 sur 4.

Combien de temps prend la leçon « Dédupliquer les lignes en toute sécurité » ?

La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.

Peux-tu écrire et exécuter du code dans cette leçon SQL Interview Prep ?

Oui. Chaque leçon SQL Interview Prep inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.

Toutes les leçons de ce cours

N premières lignes par groupe avec ROW_NUMBER
Gérer les égalités parmi les N premiers
Dédupliquer les lignes en toute sécurité
Conserver la dernière ligne par clé

← Retour à SQL Interview Prep