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:
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:
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:
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;
Hat eine Zeile nun in der [DuplicateCount]
Spalte einen Wert größer als 1, handelt es sich bei dieser um ein Duplikat einer bereits bestehenden Zeile:
Mit folgendem CTE können wir diese Spalten nun entfernen:
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.
Als professioneller SQL Server Support und zertifizierter Microsoft Partner unterstützen wir Sie in allen Fragen und individuellen Problemen rund um Ihre Serverumgebung, egal ob vor Ort oder remote. Überzeugen Sie sich selbst von unserem vielfältigen Angebot und den individuellen Leistungspaketen.
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.
Kontaktieren Sie uns gerne über das
Kontaktformular und vereinbaren ein unverbindliches
Beratungsgespräch mit unseren Berater:innen zur
Bedarfsevaluierung. Gemeinsam optimieren wir Ihre
Umgebung und steigern Ihre Performance!
Wir freuen uns auf Ihre Kontaktaufnahme!