Antwort auf die Frage

Verwenden Sie einen CTE (Common Table Expression), kombiniert mit der ROW_NUMBER() Fensterfunktion, um Duplikate deterministisch zu kennzeichnen. Partitionieren Sie den Datensatz nach den einzigartigen logischen Schlüsselspalten (sensor_id, granularity) und wenden Sie eine ORDER BY-Klausel an, die Ihre Aufbewahrungshierarchie widerspiegelt: signal_strength DESC, gefolgt von timestamp ASC, und entscheidend ist der PRIMARY KEY (z.B. log_id) als endgültiger Brecher zur Garantierung der Determinismus. Die äußere Abfrage löscht dann alle Datensätze, bei denen die zugewiesene Zeilennummer größer als eins ist, sodass nur die höchste Prioritätszeile pro Gruppe überlebt.

WITH RankedLogs AS (
  SELECT log_id,
         ROW_NUMBER() OVER (
           PARTITION BY sensor_id, granularity 
           ORDER BY signal_strength DESC, timestamp ASC, log_id ASC
         ) AS priority_rank
  FROM telemetry_logs
)
DELETE FROM telemetry_logs
WHERE log_id IN (
  SELECT log_id 
  FROM RankedLogs 
  WHERE priority_rank > 1
);

Situation aus dem Leben

Eine industrielle IoT-Plattform nahm hochfrequente Vibrationsdaten von Fertigungsrobotern in eine Tabelle namens machine_telemetry auf. Aufgrund von MQTT- Broker-Wiederholungen während Netzwerkpartitionen bestanden etwa vierzig Prozent der Tabelle aus doppelten Aufzeichnungen mit denselben robot_id und time_bucket, sich jedoch geringfügig in den Payload-Prüfziffern unterscheidend. Reporting-Dashboards zählten die Betriebsstunden doppelt, was die Wartungspläne verzerrte.

Lösung 1: Korreliertes Selbst-Join. Ein Ansatz bestand darin, die Tabelle mit sich selbst zu verknüpfen auf robot_id und time_bucket, zeilen zu löschen, bei denen der surrogate key größer war als der des Partners. Diese Methode erforderte keine Fensterfunktionen. Ihre Zeitkomplexität näherte sich jedoch O(N²) und führte zu starken Leistungsverschlechterungen bei dem Datensatz von 300 Millionen Zeilen, und sie wurde mit NULL-Werten in zusammengesetzten Schlüsseln nicht korrekt umgegangen, da sie nicht übereinstimmten.

Lösung 2: Staging-Tabelle mit Gruppierung. Ingenieure erwogen, eine temporäre Tabelle zu erstellen, die nur die überlebenden log_ids enthielt, die durch GROUP BY und MIN()-Aggregationen ermittelt wurden, und die Originaltabelle dann zu leeren und neu einzufügen. Obwohl logisch sinnvoll, erforderte dies erheblichen temporären Speicherplatz und erforderte DDL-Rechte, die in der eingeschränkten Produktionsumgebung nicht verfügbar waren, und schuf ein kurzes Zeitfenster, in dem Daten für gleichzeitige Leser fehlend erschienen.

Lösung 3: Fensterfunktion CTE. Das Team implementierte die ROW_NUMBER()-Strategie, partitionierte nach dem Duplikatschlüssel (robot_id, time_bucket) und ordnete nach Signalqualitätsmetriken. Diese Lösung wurde als eine einzige atomare Transaktion ausgeführt, die Dateninkonsistenzen während der Bereinigung verhinderte. Sie bearbeitete den gesamten Rückstand in weniger als vier Minuten und reduzierte die Speicherkosten um vierzig Prozent, ohne die Tabelle offline zu nehmen.

Was Kandidaten oft übersehen

Warum muss ein wirklich einzigartiger Primärschlüssel immer die letzte Spalte in der ORDER BY-Klausel einer Fensterfunktion zur Duplikatsbeseitigung sein, selbst wenn die Geschäftlogik scheinbar eine Sortierung allein nach einem nicht einzigartigen Zeitstempel diktiert?

In ANSI SQL ist die Reihenfolge von Zeilen, die identische Werte für alle angegebenen ORDER BY-Schlüssel besitzen, nicht deterministisch. Wenn zwei Duplikatdatensätze denselben timestamp und signal_strength teilen, ist die Datenbank-Engine frei, sie willkürlich anzuordnen. Folglich könnte das mehrmalige Ausführen der Löschlogik zufällig verschiedene Zeilen zur Erhaltung auswählen, was zu inkonsistenten Ergebnissen und potenziellem Verlust kritischer Daten führt. Das Anhängen des PRIMARY KEY gewährleistet eine totale Ordnung und garantiert idempotente und reproduzierbare Löschungen.

Wie behandelt ANSI SQL NULL-Werte innerhalb einer PARTITION BY-Klausel im Vergleich zu Standardgleichheitsprädikaten in einer Join-Bedingung, und warum gefährdet diese Unterscheidung die Genauigkeit der Duplikatsbeseitigung?

In GROUP BY oder PARTITION BY-Klauseln behandelt ANSI SQL NULL-Werte als indistinguierbar und gruppiert sie zusammen (wirksam bedeutet, NULL gleich NULL für die Aggregation). Umgekehrt bewertet der Ausdruck NULL = NULL in WHERE-Klauseln oder Joins (ON t1.x = t2.x) als UNKNOWN, nicht TRUE. Daher werden beim Duplizierung über ein Selbst-Join Zeilen mit NULL-Werten in den übereinstimmenden Spalten niemals als Duplikate erkannt, was dazu führt, dass sie fälschlicherweise überleben. Um NULLs in Joins korrekt zu handhaben, müssen Sie die Syntax IS NOT DISTINCT FROM verwenden (ANSI SQL:1999).

Was für eine spezifische Konkurrenz- und Ressourcenbedrohung gefährdet die Produktionsstabilität beim Entfernen von Millionen von Duplikaten in einer einzigen Transaktion, und welche ANSI SQL-Technik mildert dieses Risiko?

Eine monolithische DELETE-Anweisung erworben EXCLUSIVE LOCKS auf jede betroffene Zeile, was zu einem Tabellenlevel-Lock eskalieren kann, das alle gleichzeitigen Einfügungen und Lesevorgänge blockiert. Darüber hinaus erzeugt sie massives TRANSACTION LOG-Wachstum, das das Risiko von Speicherausfällen oder Wiederherstellungsfehlern birgt. Um dies zu mildern und sich an ANSI SQL zu halten, muss man die Löschvorgänge in Batches durchführen. Dies beinhaltet das iterative Löschen einer begrenzten Teilmenge, die durch FETCH FIRST n ROWS ONLY innerhalb einer Unterabfrage oder die Verwendung eines scrollbaren Cursors identifiziert wird, wobei jede kleine Transaktion unabhängig verbucht wird, um Locks freizugeben und Protokollsegmente schrittweise zu kürzen.