Duplikate erkennen und bereinigen in SQL: Strategien für die Praxis

1. Wie Duplikate in der Praxis entstehen

Die häufigste Ursache für Duplikate ist schlicht eine fehlende Unique-Constraint auf einer fachlich eindeutigen Spalte, etwa der E-Mail-Adresse eines Kunden oder einer externen Referenznummer. Ohne diese Absicherung auf Datenbankebene verlässt sich die Eindeutigkeit allein auf Anwendungslogik, die bei parallelen Anfragen, fehlerhaften Retries nach einem Timeout oder schlicht einem vergessenen Check versagen kann.

Eine zweite häufige Quelle sind wiederholte oder fehlerhafte Batch-Importe: Ein Import bricht in der Hälfte ab, wird ohne vorherige Bereinigung erneut gestartet, und die bereits eingefügten Zeilen der ersten Hälfte existieren danach doppelt. Beim Zusammenführen mehrerer Datenquellen, etwa nach einer Systemmigration oder einer Firmenübernahme, kommt häufig noch eine dritte Ursache hinzu: derselbe reale Sachverhalt wurde in beiden Quellsystemen unabhängig voneinander erfasst und ist damit ein Duplikat, obwohl beide Systeme ihn jeweils für eindeutig hielten.

2. Exakte Duplikate erkennen mit GROUP BY und HAVING

Für exakte Duplikate, bei denen die relevanten Spalten byte-identisch übereinstimmen, ist eine GROUP-BY-Abfrage mit anschließendem HAVING COUNT der zuverlässigste Startpunkt. Sie gruppiert alle Zeilen anhand der Spalten, die fachlich eindeutig sein sollten, und filtert anschließend auf Gruppen mit mehr als einem Eintrag. Das Ergebnis liefert unmittelbar eine Liste aller betroffenen Werte samt Anzahl der jeweiligen Duplikate, bevor überhaupt ein einziger Datensatz verändert wird.

Wichtig ist, die Gruppierungsspalten bewusst zu wählen: Eine zu breite Gruppierung übersieht relevante Duplikate, eine zu enge erzeugt Falschmeldungen für Zeilen, die zufällig in wenigen Feldern übereinstimmen, ohne fachlich dasselbe zu repräsentieren. In der Praxis lohnt es sich, zunächst mit der engsten sinnvollen fachlichen Eindeutigkeitsdefinition zu starten und die Abfrage bei Bedarf schrittweise zu erweitern.

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

3. Unscharfe Duplikate mit Ähnlichkeitsfunktionen finden

Nicht jedes Duplikat ist byte-identisch. Tippfehler, unterschiedliche Schreibweisen oder abweichende Formatierung erzeugen Datensätze, die fachlich denselben Sachverhalt beschreiben, aber bei einem exakten Vergleich als verschieden erscheinen, etwa zwei Firmennamen, die sich nur durch einen fehlenden Rechtsformzusatz unterscheiden. Für diese Fälle braucht es Ähnlichkeitsfunktionen statt exakter Gleichheit.

PostgreSQL bietet mit der Erweiterung pg_trgm trigram-basierte Ähnlichkeitsvergleiche, die auch bei kleineren Tippfehlern noch hohe Ähnlichkeitswerte liefern. Andere Systeme stellen phonetische Funktionen wie SOUNDEX bereit, die ähnlich klingende Namen unabhängig von der genauen Schreibweise gruppieren, sowie teils native Levenshtein-Distanz-Funktionen zur Messung der minimalen Anzahl notwendiger Zeichenänderungen zwischen zwei Werten.

-- PostgreSQL: Firmennamen mit hoher Trigram-Ähnlichkeit finden
SELECT a.id, a.company_name, b.id, b.company_name,
       similarity(a.company_name, b.company_name) AS score
FROM companies a
JOIN companies b ON a.id < b.id
WHERE similarity(a.company_name, b.company_name) > 0.6
ORDER BY score DESC;

4. ROW_NUMBER() für die sichere Auswahl des zu behaltenden Datensatzes

Sind Duplikate identifiziert, folgt die schwierigere Frage: Welcher der mehreren Datensätze bleibt erhalten? ROW_NUMBER() als Fensterfunktion löst dieses Problem elegant, indem es innerhalb jeder Duplikatgruppe eine fortlaufende Nummerierung nach einem frei wählbaren Kriterium vergibt, etwa nach dem jüngsten Erstellungsdatum zuerst oder nach der Vollständigkeit der Zeile.

Alle Zeilen mit einer Nummer größer als eins innerhalb ihrer Gruppe gelten dann als Löschkandidaten, während genau eine Zeile pro Gruppe die Nummer eins trägt und erhalten bleibt. Dieser Ansatz macht die Auswahllogik explizit und nachvollziehbar, statt sich auf die zufällige physische Reihenfolge der Zeilen zu verlassen, die je nach Datenbanksystem und Ausführungsplan variieren kann.

5. Praxisbeispiel: Sicheres Löschen mit CTE und ROW_NUMBER()

In der Praxis wird ROW_NUMBER() meist innerhalb einer Common Table Expression verwendet, deren Ergebnis anschließend als Grundlage für ein DELETE oder UPDATE dient. Vor jedem tatsächlichen Löschen sollte dieselbe Abfrage zunächst als reines SELECT ausgeführt werden, um die Löschkandidaten manuell zu prüfen, bevor die Änderung endgültig wird.

Diese zweistufige Vorgehensweise, erst prüfen, dann löschen, verhindert den häufigsten Fehler bei Duplikat-Bereinigungen: eine falsch formulierte PARTITION-BY-Klausel, die versehentlich alle Zeilen einer Tabelle als eine einzige Gruppe behandelt und damit fast den gesamten Datenbestand löscht statt nur der tatsächlichen Duplikate.

WITH ranked AS (
    SELECT id,
           ROW_NUMBER() OVER (
               PARTITION BY email
               ORDER BY created_at DESC
           ) AS rn
    FROM customers
)
DELETE FROM customers
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

6. Duplikate mit abhängigen Fremdschlüsseln bereinigen

Ein direktes Löschen scheitert, sobald andere Tabellen per Fremdschlüssel auf den zu entfernenden Duplikat-Datensatz verweisen, etwa Bestellungen, die auf eine doppelte Kundenzeile zeigen. Ein einfaches DELETE würde entweder an einer Constraint-Verletzung scheitern oder, im schlimmeren Fall bei fehlender Absicherung, verwaiste Referenzen hinterlassen.

Der korrekte Ablauf ist deshalb zweistufig: Zuerst werden alle abhängigen Fremdschlüssel-Referenzen auf den zu behaltenden Datensatz umgehängt, meist über ein UPDATE, das den alten Fremdschlüsselwert durch den neuen ersetzt, erst danach kann der eigentliche Duplikat-Datensatz gefahrlos gelöscht werden. Diese Reihenfolge muss innerhalb einer einzigen Transaktion ablaufen, damit kein Zwischenzustand mit inkonsistenten Referenzen jemals sichtbar wird.

BEGIN;

UPDATE orders
SET customer_id = 4711
WHERE customer_id = 9832;

DELETE FROM customers
WHERE id = 9832;

COMMIT;

7. Prävention: Unique-Constraints trotz bestehender Duplikate nachrüsten

Die beste Bereinigung nützt wenig, wenn dieselbe Ursache unmittelbar wieder neue Duplikate erzeugt. Nach dem Aufräumen sollte deshalb eine Unique-Constraint auf der betroffenen Spalte ergänzt werden, was jedoch nur gelingt, wenn zu diesem Zeitpunkt tatsächlich keine Duplikate mehr vorhanden sind, da die Datenbank die Constraint sonst ablehnt.

In gewachsenen Systemen mit sehr großen Tabellen empfiehlt sich, den Constraint-Aufbau in zwei Schritten zu planen: zunächst ein eindeutiger Index ohne harte Erzwingung zur Validierung im laufenden Betrieb, danach die eigentliche Constraint-Umwandlung zu einem Zeitpunkt mit geringer Last, um Sperrzeiten auf einer produktiven Tabelle so gering wie möglich zu halten.

8. Batching bei großen Tabellen: Lock-Zeiten vermeiden

Ein einzelnes DELETE, das Millionen Zeilen in einem Rutsch entfernt, kann eine produktive Tabelle für die gesamte Dauer der Operation sperren und parallele Schreibzugriffe blockieren. Statt eines einzigen großen Statements empfiehlt sich deshalb ein Vorgehen in kleinen Batches, etwa jeweils einige tausend Zeilen pro Durchlauf mit einer kurzen Pause dazwischen, um anderen Transaktionen Gelegenheit zum Zugriff zu geben.

Ein solches Batching lässt sich mit einer LIMIT-Klausel kombiniert mit einer Schleife auf Anwendungsebene umsetzen, wobei jeder Durchlauf in einer eigenen kurzen Transaktion committet wird. Dieses Vorgehen verlängert die Gesamtdauer der Bereinigung, reduziert aber das Risiko spürbarer Beeinträchtigungen im produktiven Betrieb erheblich.

9. Merge-Strategie: Duplikate zu einem vollständigen Datensatz konsolidieren

Manchmal ist keiner der Duplikat-Datensätze vollständig, sondern jeder enthält unterschiedliche, jeweils gültige Informationen, etwa eine Zeile mit korrekter Telefonnummer und eine andere mit korrekter Adresse. In diesem Fall ist reines Löschen die falsche Strategie, weil dabei Information verloren geht, die in keinem der beiden Duplikate vollständig vorlag.

Stattdessen bietet sich ein Merge an, bei dem der zu behaltende Datensatz feldweise mit COALESCE aus allen Duplikaten aufgefüllt wird, sodass jedes nicht leere Feld eines Duplikats in den finalen Datensatz übernommen wird, bevor die übrigen Duplikate wie gewohnt gelöscht oder umgehängt werden. Diese Strategie erfordert zwar mehr Sorgfalt bei der Feldauswahl, verhindert aber den stillen Verlust wertvoller Information.

10. Zusammenfassung

11. FAQ: Duplikate bereinigen: Das Wichtigste auf einen Blick