SQL Interview Prep · Lektion

Zeilen sicher deduplizieren

Exakte und nahezu identische Zeilen entfernen und dabei einen kanonischen Datensatz beibehalten

Zeilen sicher deduplizieren ist eine kostenlose SQL Interview Prep-Lektion auf CoddyKit. Dies ist Lektion 3 von 4. Du kannst die komplette Lektion unten kostenlos lesen – dann übst du sie direkt im Browser mit einem integrierten Code-Editor und einem KI-Tutor rund um die Uhr. Sie ist Teil des SQL Interview Prep-Lernpfads, und dein Fortschritt wird über Web und CoddyKit-App synchronisiert. Der SQL Interview Prep-Kurs umfasst insgesamt 4 Lektionen.

Das Deduplizierungsproblem

„Diese Tabelle enthält doppelte Zeilen. Entfernen Sie sie, behalten Sie aber jeweils eine Kopie.“ Fast jedes Vorstellungsgespräch im Data Engineering enthält eine Variante dieser Aufgabe. Die Herausforderung besteht darin, dies sicher zu tun: genau eine kanonische Zeile beizubehalten und nicht versehentlich unterschiedliche Datensätze zu löschen, die nur ähnlich aussehen.

Wir behandeln das Erkennen von Duplikaten, die Auswahl der beizubehaltenden Kopie, die Deduplizierung in einem SELECT sowie das physische Löschen von Duplikaten aus einer Tabelle.

Duplikate zuerst definieren

Die erste Frage an die interviewende Person lautet: „Was macht zwei Zeilen zu Duplikaten?“ Mögliche Definitionen sind:

Exakte Duplikate: Jede Spalte ist identisch.
Schlüsselduplikate: Derselbe fachliche Schlüssel (z. B. dieselbe email), andere Spalten können sich jedoch unterscheiden.

Die Vorgehensweise unterscheidet sich je nach Definition. Gehen Sie nie von einer Definition aus: Die Klärung, was ein Duplikat ist, ist der wichtigste Schritt, und Interviewende erwarten, dass Sie danach fragen.

Duplikate erkennen

Um doppelte Schlüssel zu finden, gruppieren Sie nach den Spalten, die ein Duplikat definieren, und behalten Sie Gruppen mit einer Anzahl von mehr als eins bei. So erkennen Sie, welche Schlüssel betroffen sind und wie viele Kopien vor Änderungen existieren.

Eine Erkennungsabfrage zuerst auszuführen, ist eine gute Praxis, die Sie ausdrücklich erwähnen sollten: Sie überprüfen den Umfang des Problems, bevor Sie etwas löschen.

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

Exakte Duplikate: DISTINCT

Wenn Duplikate über jede Spalte hinweg wirklich identisch sind, ist eine schreibgeschützte deduplizierte Ansicht so einfach wie SELECT DISTINCT *. UNION (ohne ALL) entfernt ebenfalls doppelte Zeilen.

DISTINCT hilft jedoch nur, wenn Sie die gesamte Zeile deduplizieren möchten und nicht auswählen müssen, welche Kopie beibehalten wird. Bei schlüsselbasierten Duplikaten, deren Spalten sich unterscheiden, benötigen Sie Ranking.

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

Schlüsselduplikate: ROW_NUMBER

Wenn Zeilen denselben Schlüssel haben, sich aber in anderen Spalten unterscheiden, partitionieren Sie nach dem Schlüssel und nummerieren Sie jede Kopie. rn = 1 kennzeichnet die beizubehaltende Zeile; rn > 1 kennzeichnet die zusätzlichen Zeilen, die verworfen werden.

Das ORDER BY innerhalb des Fensters entscheidet, welche Kopie kanonisch ist. Wählen Sie es bewusst, beispielsweise um die zuletzt aktualisierte Zeile beizubehalten.

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

Die kanonische Kopie auswählen

Umschließen Sie die Nummerierung mit einer CTE und behalten Sie nur rn = 1 bei. Dadurch erhalten Sie eine Zeile pro Schlüssel, und zwar genau die Zeile, die Ihr ORDER BY an die erste Stelle gesetzt hat.

Diese SELECT-Form ist nicht destruktiv: Sie eignet sich ideal zum Erstellen einer sauberen Ansicht oder zum Befüllen einer deduplizierten Zieltabelle mit INSERT ... SELECT, ohne die Quelle zu verändern.

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;

Die Wahl der Sortierung ist entscheidend

Das ORDER BY innerhalb der Partition ist eine Geschäftsentscheidung, keine Formalität:

ORDER BY updated_at DESC behält den aktuellsten Datensatz bei.
ORDER BY created_at ASC behält den ursprünglichen Datensatz bei.
ORDER BY id ASC behält den niedrigsten Surrogatschlüssel bei, was eine stabile beliebige Auswahl ermöglicht.

Fügen Sie einen eindeutigen Tie-Breaker hinzu, damit die Auswahl deterministisch bleibt, wenn auch die primäre Sortierspalte gleiche Werte enthält.

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

Duplikate physisch löschen

Um Duplikate tatsächlich aus der Tabelle zu entfernen, identifizieren Sie die zusätzlichen Zeilen (rn > 1) und löschen Sie sie. In Postgres und SQL Server können Sie mithilfe einer CTE löschen; in MySQL sind ein Self-Join oder eine Unterabfrage üblich.

Führen Sie immer zuerst das passende SELECT aus, um genau die Zeilen in einer Vorschau zu sehen, die verschwinden werden. Blindes Löschen ist der Grund, warum Kandidaten bei dieser Frage scheitern.

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);

Das Löschmuster mit Self-Join

Ein klassischer portabler Ansatz behält pro doppeltem Schlüssel die Zeile mit der kleinsten id und löscht die übrigen Zeilen mithilfe eines Self-Joins. Er benötigt keine Fensterfunktionen, was bei älteren Datenbank-Engines wichtig ist.

Die Join-Bedingung ordnet jede Zeile einer anderen Zeile mit demselben Schlüssel und einer kleineren ID zu; jede Zeile, für die ein solches Gegenstück mit kleinerer ID existiert, ist ein zu löschendes Duplikat.

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

Sicherheitscheckliste

Bevor Sie löschen, sichern Sie sich ab:

Führen Sie das Löschen innerhalb einer Transaktion aus, damit Sie bei einer unerwarteten Anzahl ein ROLLBACK durchführen können.
Führen Sie zuerst SELECT COUNT(*) für die zu löschenden Zeilen aus und prüfen Sie, ob das Ergebnis plausibel ist.
Erwägen Sie eine Sicherungstabelle: CREATE TABLE users_bak AS SELECT * FROM users.
Bestätigen Sie, dass Ihre PARTITION BY-Spalten tatsächlich ein Duplikat definieren, da Sie sonst unterschiedliche Datensätze löschen könnten.

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

Beinahe-Duplikate und Normalisierung

Manchmal sind Zeilen nicht exakt gleich, aber logisch identisch: 'Ann@X.com' gegenüber 'ann@x.com' oder aufgrund nachgestellter Leerzeichen. Partitionieren Sie nach einem normalisierten Ausdruck statt nach der unveränderten Spalte.

Wenn Sie Normalisierung erwähnen, zeigen Sie Praxisverständnis: Duplikate verbergen sich in der Realität oft hinter Unterschieden bei Groß- und Kleinschreibung, Leerzeichen oder Formatierung, die ein naiver Schlüsselvergleich nicht erkennt.

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

Kurztest

Wählen Sie die sichere Vorgehensweise zur Deduplizierung aus.

Rückblick: Sichere Deduplizierung

Gehen Sie bei der Deduplizierung systematisch vor:

Definieren Sie zunächst, was ein Duplikat ist, und erkennen Sie es dann mit GROUP BY / HAVING COUNT(*) > 1.
Exakte Duplikate → DISTINCT. Schlüsselduplikate → ROW_NUMBER, partitioniert nach dem Schlüssel; behalten Sie rn = 1 bei.
Das ORDER BY des Fensters wählt die kanonische Kopie aus; fügen Sie einen eindeutigen Tie-Breaker hinzu.
Löschen Sie die Zeilen mit rn > 1 innerhalb einer Transaktion, nachdem Sie die Anzahl in einer Vorschau geprüft haben.
Normalisieren Sie Schlüssel, um Beinahe-Duplikate zu erkennen.

Häufig gestellte Fragen

Ist die Lektion „Zeilen sicher deduplizieren“ kostenlos?

Ja — der vollständige Text von „Zeilen sicher deduplizieren“ ist hier im Web kostenlos zu lesen. Um sie interaktiv zu üben (integrierter Code-Editor und 24/7 KI-Tutor) und den Rest des SQL Interview Prep-Kurses freizuschalten, upgrade auf CoddyKit PRO. Der SQL Interview Prep-Kurs umfasst insgesamt 4 Lektionen.

Was lerne ich in „Zeilen sicher deduplizieren“?

Exakte und nahezu identische Zeilen entfernen und dabei einen kanonischen Datensatz beibehalten Du übst SQL Interview Prep mit praktischem Code, den du direkt im Browser ausführst, und ein 24/7 KI-Tutor beantwortet deine Fragen während du die Lektion bearbeitest.

Brauche ich Erfahrung, um SQL Interview Prep zu starten?

Keine Vorkenntnisse erforderlich. SQL Interview Prep auf CoddyKit ist für Anfänger bis fortgeschrittene Lernende strukturiert, sodass du hier starten oder von Anfang an beginnen und in deinem eigenen Tempo voranschreiten kannst. Dies ist Lektion 3 von 4.

Wie lange dauert die Lektion „Zeilen sicher deduplizieren“?

Die meisten CoddyKit-Lektionen dauern etwa 5–10 Minuten. Jede ist kompakt und interaktiv, sodass du stetig Fortschritte machst und genau dort weitermachst, wo du aufgehört hast – im Web und in der App.

Kann ich in dieser SQL Interview Prep-Lektion Code schreiben und ausführen?

Ja. Jede SQL Interview Prep-Lektion enthält einen integrierten Code-Editor, sodass du echten Code direkt in deinem Browser schreibst und ausführst und sofort KI-Feedback erhältst — ohne lokale Einrichtung erforderlich.

Alle Lektionen in diesem Kurs

Die obersten N Zeilen je Gruppe mit ROW_NUMBER
Gleichstände bei den obersten N Werten behandeln
Zeilen sicher deduplizieren
Die aktuellste Zeile je Schlüssel beibehalten

← Zurück zu SQL Interview Prep