Duplikate entfernen

Wer in die Verlegenheit kommt Duplikate aus einer Tabelle zu entfernen, kommt schnell auf die Lösung, die Sätze mit DISTINCT herauszukopieren, die Daten in der Quell-Tabelle zu löschen und die distinkten Sätze zurück zu kopieren. Hier kommt ein ganz anderer Weg mit Window-Functions.

Ausgangslage

Durch welchen Fehler auch immer haben wir unsere Daten gleich dreimal in der Tabelle und natürlich keinen Unique-Key, der das verhindert hätte. Die Spalten Part und Ord sollten in der Kombination eigentlich eindeutig sein. Die anderen Spalten der Tabelle werden hier nicht betrachtet.

Lösung

Wir nutzen die beiden Window-Functions ROW_NUMBER() und RANK(). Die erste nummeriert alle Sätze in der Tabelle durch und die zweite erstellt ein Ranking über diese Sätze. Da wir die gesamte Tabelle betrachten wollen, verwenden wir nicht die Klausel PARTITION BY, sondern nur das ORDER BY.
Gleiche Sätze erhalten den gleichen Rang und beim nächsten wechselnden Schlüssel wird der Rang durch die Anzahl Ränge vorher + 1 ermittelt. Hier erfolgt also ein Sprung zu jeder Gruppe.

Vergleicht man jetzt die Werte von ROW_NUMBER() und RANK(), so stellt man fest, dass immer beim ersten Satz einer Gruppe diese beiden Werte übereinstimmen. Die Lösung ist also alle Sätze zu löschen, bei denen RowNum und Rank unterschiedlich sind.

Diese Löschung geht über eine Common-Table-Expression und ist ansonsten sehr simpel:

WITH NumberedRows (Part, Ord, rownum, rnk)
AS
(
SELECT Part, Ord,
  
ROW_NUMBER()    OVER(ORDER BY Part, Ord ) AS rownum,
  
RANK()                    OVER(ORDER BY Part, Ord ) AS rnk
FROM #Duplikate
)
DELETE FROM NumberedRows
WHERE rownum <> rnk;

Anhänge:

  • 5 stars
    Kommentar von: klausobd
    01.09.13 @ 14:15:28

    Hi,

    mit dieser Idee kann man auch das Problem bei einer Reservierung

    wie viele Tage hintereinander ist dieser Raum frei (oder belegt)

    wunderbar lösen.

    ROW_NUMBER() und RANK() gibt es wohl seit SQL Server 2005.

    mfg Klaus Oberdalhoff

Einen Kommentar hinterlassen

You must be logged in to leave a comment. Log in now!

If you have no account yet, you can register now...
(It only takes a few seconds!)