La déduplication en SQL : supprimer les doublons proprement (ROW_NUMBER, QUALIFY)

Les doublons, c'est l'un des problèmes data les plus fréquents. Un export relancé deux fois, un JOIN qui duplique des lignes, une source pas propre, et d'un coup les chiffres sont faux. Et le réflexe SELECT DISTINCT ne règle presque jamais le vrai problème.

Dans cet article, on voit comment dédoublonner proprement, en détectant les doublons, comprendre la raison, et surtout choisir quelle ligne tu gardes. Les exemples viennent du SQL Lab de DataCertification, tu peux les écrire et les exécuter directement dans ton navigateur. Si tu as besoin de revoir ROW_NUMBER ou les OVER, j'ai un guide complet sur les window functions, et pour les bases, le guide des fondamentaux SQL.

D'abord, c'est quoi un doublon

Avant de supprimer quoi que ce soit, il faut savoir ce que tu supprimes. Il y a deux types de doublons, et on ne les traite pas pareil.

Dans la vraie vie, c'est presque toujours le second cas. Tes lignes ne sont pas identiques totalement car il y a un petit détail technique diffèrent comme un timestamp. Et c'est exactement là que DISTINCT te lâche.

La seule question qui compte avant de dédoublonner c'est sur quelles colonnes deux lignes sont "les mêmes" (la clé du doublon), et laquelle tu veux garder (la plus récente ? la plus complète ?). Tant que tu n'as pas répondu à ça, tu ne dédoublonnes pas, tu vas supprimer au hasard.

Étape 1 : détecter les doublons

Le bon réflexe c'est de regarder les doublons avant de les supprimer. Le plus simple est un GROUP BY sur ta clé, et tu gardes les groupes qui ont plus d'une ligne.

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

Tu obtiens la liste des emails en double et leur nombre d'occurrences. Si tu veux carrément voir les paires de lignes concernées, un self-join fait le travail.

💡 À toi de jouer ==> Self-join : trouver les doublons (ra-13).

Étape 2 : DISTINCT(utile mais vite limité)

DISTINCT élimine du résultat les lignes entièrement identiques. Pour dédoublonner une liste de valeurs simples, c'est parfait.

SELECT DISTINCT category FROM products;

Mais il a deux limites qui le rendent inutile dans la plupart des cas réels :

• Dès qu'une colonne diffère, les lignes ne sont plus identiques et DISTINCT ne supprime rien. Un id ou un created_at qui change, et tes "doublons" passent tous au travers.
• Il ne te laisse pas choisir quelle ligne garder. Tu ne contrôles rien, tu écrases.

Si tu as juste besoin de compter des valeurs uniques (le nombre de clients distincts, pas de commandes) alors COUNT(DISTINCT ...) est l'outil. Pour supprimer des lignes en gardant la bonne, il faut passer à la vitesse supérieure.

💡 À toi de jouer : Clients distincts par statut (gb-12).

Étape 3 : ROW_NUMBER + filtre

C'est le pattern de déduplication le plus efficace, celui que tu vas utiliser 90 % du temps. L'idée est de numéroter les lignes à l'intérieur de chaque groupe de doublons, puis ne garder que la première.

Trois décisions à prendre, toutes dans la clause OVER :

• PARTITION BY = la clé du doublon (sur quoi deux lignes sont "les mêmes").
• ORDER BY = le critère pour choisir la ligne à garder (la plus récente d'abord, par exemple).
• WHERE rn = 1 = on ne garde que la première de chaque groupe.

Prenons une table commandes où chaque client a passé plusieurs commandes, et où on veut garder uniquement la plus récente par client.

Table commandes :

WITH derniere_commande AS (
  SELECT client, produit, date_commande,
    ROW_NUMBER() OVER (
      PARTITION BY client
      ORDER BY date_commande DESC
    ) AS rn
  FROM commandes
)
SELECT client, produit, date_commande
FROM derniere_commande
WHERE rn = 1
ORDER BY client;

Résultat : une seule ligne par client, la plus récente.

💡 À toi de jouer : CTE pour dédoublonner (ct-21).

Étape 4 : QUALIFY, la solution des DW modernes

Le pattern ci-dessus marche partout, mais il est un peu verbeux car il faut un CTE juste pour pouvoir filtrer. Les DW modernes proposent un raccourci, QUALIFY, qui filtre une window function directement, sans CTE ni sous-requête.

-- Même résultat
SELECT client, produit, date_commande
FROM commandes
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY client
  ORDER BY date_commande DESC
) = 1;

QUALIFY est à la window function ce que HAVING est à l'agrégat donc un filtre qui s'applique après son calcul. C'est la façon la plus propre de dédoublonner quand ton moteur le supporte.

Nb, tous ne le supportent pas :

C'est pour ça que dans le SQL Lab (qui tourne sur SQLite), tu pratiques la version CTE de l'étape 3. Sur Snowflake ou BigQuery au travail, tu pourras la raccourcir avec QUALIFY. Le raisonnement est identique, seule l'écriture change. (pour pratiquer Qualify)

Étape 5 : Choisir quelle ligne garder

Dédoublonner, ce n'est pas supprimer des lignes au hasard, c'est décider laquelle survit. Tout se joue dans l'ORDER BY du OVER :

• La plus récente : ORDER BY date_commande DESC.
• La première arrivée : ORDER BY date_commande ASC.
• La plus complète : ORDER BY (CASE WHEN email IS NULL THEN 1 ELSE 0 END) pour pousser les lignes incomplètes en dernier.

Pour un résultat stable, ajoute une colonne qui départage, typiquement l'id :

ROW_NUMBER() OVER (
  PARTITION BY client
  ORDER BY date_commande DESC, id DESC
)

Et à ne pas confondre avec RANK car ROW_NUMBER donne un numéro unique (idéal pour garder exactement une ligne), alors que RANK attribue le même rang aux lignes identiques (donc tu peux en garder plusieurs sans le vouloir). Pour de la déduplication stricte, c'est ROW_NUMBER.

Le même pattern sert aussi à garder les N premières lignes par groupe (par exemple, les 2 meilleurs salaires par département etc..) en remplaçant juste = 1 par <= 2.

💡 À toi de jouer : Top 2 salaires par département (wf-17).

Cas particulier : dédoublonner un résultat avec UNION

Quand tu combines deux requêtes, UNION retourne un résultat distinct, donc il élimine les doublons entre les deux jeux de données. UNION ALL, lui, garde tout et évite le coût de déduplication.

SELECT name FROM employees
UNION            -- dédoublonne
SELECT name FROM contractors;

💡 À toi de jouer : UNION : combiner deux résultats (ra-01).

Pour aller plus loin et pratiquer

La déduplication, comme tout le SQL, s'apprend en écrivant les requêtes, pas en les lisant. Les modules "CTEs", "Window Functions" et "Requêtes avancées" du SQL Lab couvrent tous ces patterns avec un éditeur intégré et une validation automatique. Commence par l'exercice ct-21, c'est le pattern de référence.

Si tu travailles déjà sur des pipelines (qualité de données, modélisation, incrémental), le Lab Analytics Engineering applique la déduplication à des cas complets.

Si tu débutes sur SQL, voir le guide des fondamentaux SQL, les window functions, et la différence CTE vs table dérivée.

Tu veux que je t'accompagne sur un projet data (qualité de données, modélisation, performance SQL, Snowflake, dbt) ?

👉 Réserver un appel de 30 minutes

Questions fréquentes