Duplikate innerhalb einer SQL Tabelle können aus den verschiedensten Gründen auftreten, wie beispielsweise durch unsaubere Quelldaten oder Bugs in Anwendungen. Natürlich kann durch den konsequenten Einsatz von Primary Keys und Unique Constraints das Auftreten von Duplikaten verhindert werden. Doch wie sagt man so schön ”Nobody’s perfect”. So ist man als Entwickler, der viel mit relationalen Datenbanken arbeitet, früher oder später mit der Aufgabe konfrontiert, Duplikate entfernen zu müssen.
Für diesen Fall wollen wir in diesem Beitrag einige Wege vorstellen, wie wir mittels SQL, Duplikate aus Tabellen entfernen können.
Für dieses Beispiel erstellen wir eine Tabelle die den Bestand eines Buchladens repräsentiert. Sie enthält den Titel eines Buches, sein Erscheinungsjahr und den Autor:
CREATE TABLE books
(
[ID] INT identity(1,1),
[Title] VARCHAR(100),
[Author] VARCHAR(200),
[Year] VARCHAR(100),
)
GO
INSERT INTO books([Title], [Author], [Year]) VALUES('Per Anhalter durch die Galaxis','Douglas Adams', '1979');
INSERT INTO books([Title], [Author], [Year]) VALUES('Harry Potter und der Stein der Weisen','JK Rolling', '1997');
INSERT INTO books([Title], [Author], [Year]) VALUES('Der Herr der Ringe','JRR Tolkien', '1954');
INSERT INTO books([Title], [Author], [Year]) VALUES('A Game of Thrones','George RR Martin', '1996');
INSERT INTO books([Title], [Author], [Year]) VALUES('Per Anhalter durch die Galaxis','Douglas Adams', '1979');
INSERT INTO books([Title], [Author], [Year]) VALUES('Der Herr der Ringe','JRR Tolkien', '1954');
INSERT INTO books([Title], [Author], [Year]) VALUES('Per Anhalter durch die Galaxis','Douglas Adams', '1979');Wir haben ein paar Duplikate eingebaut, die wir nun im folgenden entfernen:
Doppelte Einträge mit GROUP BY und HAVING entfernen
In der ersten Methode, die wir zum Löschen von Duplikaten vorstellen möchten, werden wir zunächst das GROUP BY SQL Statement verwenden, um die doppelten Einträge zu identifizieren. Hiermit können wir die Einträge der Tabelle gemäß den definierten spalten gruppieren und anschließend die COUNT Funktion verwenden, um genau zu überprüfen wie oft eine Spalte vorkommt.
Mit Folgender SQL Abfrage können wir nun alle Spalten erhalten, die öfter als 1 mal in unserer Tabelle vorkommen:
SELECT [Title],
[Author],
[YEAR],
COUNT(*) AS CNT
FROM books
GROUP BY [Title],
[Author],
[YEAR]
HAVING COUNT(*) > 1;
Wir können sehen, dass:
“Der Herr der Ringe” zweimal, und
“Per Anhalter durch die Galaxis” dreimal
innerhalb unserer Tabelle auftreten. Nun müssen wir, jeweils eine einzelne Zeile der mehrfach vorkommenden Einträge behalten, und alle Duplikate entfernen. Es müssen also zwei Zeilen von “Per Anhalter durch die Galaxis” und eine Zeile von “Der Herr der Ringe” entfernt werden.
Hierfür verwenden wir die SQL MAX Funktion um die Max ID aller Reihen auszurechnen:
SELECT *
FROM books
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM books
GROUP BY [Title],
[Author],
[Year]
);Im folgenden Screenshot können wir sehen, dass obiges SELECT Statement genau die Zeilen ausgibt, die wir aus unserer Tabelle entfernen müssen:
Um nun die überschüssigen Daten zu entfernen, tauschen wir das SELECT im obigen Statement durch ein DELETE aus:
DELETE
FROM books
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM books
GROUP BY [Title],
[Author],
[Year]
);Nach dem Ausführen dieser Abfrage wurden alle Duplikate aus der Tabelle entfernt. Führen wir nun ein SELECT * Statement aus, erhalten wir eine Tabelle ohne Duplikate:
Doppelte Einträge mit Common Table Expressions (CTE) entfernen
Um Duplikate aus einer Tabelle zu entfernen, können wir auch auch die sogenannten Common Table Expressions, kurz CTE, verwenden. Diese sind ab SQL Server 2005 verfügbar.
Wir nutzen hier die ROW_NUMBER Funktion, welche an jede Zeile eine einzigartige, sequenzielle Zeilennummer vergibt.
In der folgenden CTE, werden mit dem SQL Kommando PARTITION BY, die Daten der Spalten [Title], [Author] und [Year] partitioniert und für jede Zeile eine einzigartige Zeilennummer generiert:
WITH CTE([title],
[author],
[year],
duplicatecount)
AS (SELECT [title],
[author],
[year],
ROW_NUMBER() OVER(PARTITION BY [title],
[author],
[year]
ORDER BY id) AS DuplicateCount
FROM books
SELECT *
FROM CTE;
WITH CTE([Title],
[Author],
[Year],
duplicatecount)
AS (SELECT [Title],
[Author],
[Year],
ROW_NUMBER() OVER(PARTITION BY [Title],
[Author],
[Year]
ORDER BY id) AS DuplicateCount
FROM books
DELETE FROM CTE
WHERE DuplicateCount > 1;Es werden alle Spalten mit einem [DuplicateCount] > 1 entfernt.
RANK Funktion zum löschen doppelter Einträge
Eine weitere SQL Funktion mit der Duplikate innerhalb einer Tabelle gelöscht werden können, ist die RANK Funktion. Diese gibt jeder Zeile einer Tabelle eine eindeutige Zeilen-ID, unabhängig davon, ob es sich um ein Duplikat handelt oder nicht.
In der Folgenden Abfrage nutzen wir die RANK Funktion in Kombination mit der PARTITION BY Klausel. So wird eine Teilmenge von den Daten der angegebenen Spalten erstellt und ein Rang für die Partition vergeben:
SELECT B.ID,
B.Title,
B.Author,
B.Year,
T.rank
FROM books B
INNER JOIN
(
SELECT *,
RANK() OVER(PARTITION BY Title,
Author,
Year
ORDER BY id) rank
FROM books
) T ON B.ID = t.ID
Aus dem Output können wir entnehmen, dass wir nun alle Zeilen mit einem Rank Wert > 1 entfernen müssen. Dies tun wir mit folgender Abfrage:
DELETE B
FROM books B
INNER JOIN
(
SELECT *,
RANK() OVER(PARTITION BY Title,
Author,
Year
ORDER BY id) rank
FROM books
) T ON B.ID = t.ID
WHERE rank > 1;
Wir haben also gesehen, dass es einige Möglichkeiten gibt, mit wenig Aufwand, doppelte Einträge aus einer Tabelle zu entfernen.