Dieser Blogpost behandelt ein etwas technischeres Thema als die sonstigen Posts im SpikeTime Blog. Die meisten Entwickler die viel mit relationalen Datenbanken arbeiten, sind schon einmal in die Verlegenheit gekommen in einer Datenbanktabelle Duplikate entfernen zu müssen. Es gibt viele Gründe für Dopplungen. Angefangen von unsauberne Quelldaten bis zu Bugs in der Anwendung die in seltenen Eingabekombinationen zu doppelten Einträgen führen. Grundsätzlich kann gesagt werden, dass diese Probleme durch den durchgängigen Einsatz von Primary Keys und Unique Constraints nicht auftreten kann, aber wie sagt man so schön "Nobody's perfect".
Im Folgenden werden 3 Methoden vorgestellt die zeigen wie man doppelte Datensätze aus einer Datenbanktabelle entfernt. Die Beispiele basieren auf dem SQL Server. Eine Verwendung in anderen Datenbanksystemen ist davon abhängig, ob und wie die verwendeten Features implementiert sind (Common Table Expression, Cursors, Window Functions etc.)
Erstellen der Beispieldaten
Unsere Beispieltabelle beinhaltet die Fähigkeiten von Mitarbeitern. In einer echten Applikation würden die Fähigkeiten wahrscheinlich in einer eigenen Tabelle abgelegt werden und die EmployeeSkills Tabelle würde nur die Referenz darauf enthalten. Um das Beispiel möglichst einfach zu halten verzichten wir an dieser Stelle aber darauf.
Zuerst erzeugen wir die Tabelle EmployeeSkills
CREATE TABLE EmployeeSkills
(
EmployeeId INT NOT NULL,
SkillDescription VARCHAR(max) NOT NULL
)
Anschließend fügen wir die doppelten Datensätze ein
INSERT INTO dbo.EmployeeSkills (EmployeeId, SkillDescription)
VALUES (1, 'Datenbankentwicklung')
INSERT INTO dbo.EmployeeSkills (EmployeeId, SkillDescription)
VALUES (1, 'Datenbankentwicklung')
INSERT INTO dbo.EmployeeSkills (EmployeeId, SkillDescription)
VALUES (2, 'Webentwicklung')
INSERT INTO dbo.EmployeeSkills (EmployeeId, SkillDescription)
VALUES (2, 'Webentwicklung')
INSERT INTO dbo.EmployeeSkills (EmployeeId, SkillDescription)
VALUES (2, 'Webentwicklung')
INSERT INTO dbo.EmployeeSkills (EmployeeId, SkillDescription)
VALUES (3, 'Projektmanagement')
Wir erhalten folgendes Ergebnis
| EmployeeId | SkillDescription |
|---|---|
| 1 | Datenbankentwicklung |
| 1 | Datenbankentwicklung |
| 2 | Webentwicklung |
| 2 | Webentwicklung |
| 2 | Webentwicklung |
| 3 | Projektmanagement |
Methode 1: Temporäre Tabelle
Mit der ersten und einfachsten Methode erzeugen wir eine temporäre Tabelle die nur die eindeutigen Datensätze enthält. Anschließend löschen wir den Inhalt der eigentlichen Tabelle und fügen abschließend die deduplizierten Datensätze wieder ein.
SELECT DISTINCT * INTO #TempTable FROM dbo.EmployeeSkills
TRUNCATE TABLE dbo.EmployeeSkills
INSERT INTO dbo.EmployeeSkills
SELECT * FROM #TempTable
DROP TABLE #TempTable
Diese Methode ist zwar einfach zu verstehen, aber es ist nötig alle Daten der Zieltabelle zu löschen. Dies ist z.B. durch vorhandene Fremdschlüsselbeziehungen nicht immer möglich. Die zweite Methode zeigt daher wie man die Duplikate entfernen kann, ohne die komplette Tabelle leeren zu müssen.
Methode 2: DELETE TOP(x) FROM
Die DELETE TOP(x) Syntax des SQL Server ermöglicht es uns alle doppelt vorhandenen Datensätze bis auf einen zu löschen. Dazu ermitteln wir die Anzahl der mehrfach vorkommenden Datensätze und reduzieren die Anzahl um 1 (Ein Datensatz soll in der Tabelle verbleiben). In anderen Datenbanksystemen (z.B. Oracle) muss die DELETE TOP Syntax durch das LIMIT Keyword ersetzt werden.
DELETE TOP
(SELECT COUNT(*) -1 FROM dbo.EmployeeSkills
WHERE EmployeeId = 2 AND SkillDescription = 'Webentwicklung')
FROM dbo.EmployeeSkills
WHERE EmployeeId = 2
AND SkillDescription = 'Webentwicklung'
Für eine geringe Anzahl an Datensätzen ist diese manuelle Vorgehensweise praktikabel. Haben wir es mit einer größeren Datenmenge zu tun, können wir unsere DELETE Query in einen Cursor verpacken um damit alle Duplikate zu löschen.
DECLARE @EmployeeId INT
DECLARE @SkillDescription VARCHAR(MAX)
DECLARE db_cursor CURSOR FOR
SELECT EmployeeId, SkillDescription
FROM dbo.EmployeeSkills
GROUP BY EmployeeId, SkillDescription
HAVING COUNT(*) > 1
OPEN db_cursor
FETCH NEXT FROM db_cursor
INTO @EmployeeId, @SkillDescription
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE TOP
(SELECT COUNT(*) -1 FROM dbo.EmployeeSkills
WHERE EmployeeId = @EmployeeId
AND SkillDescription = @SkillDescription)
FROM dbo.EmployeeSkills
WHERE EmployeeId = @EmployeeId
AND SkillDescription = @SkillDescription
FETCH NEXT FROM db_cursor
INTO @EmployeeId, @SkillDescription
END
CLOSE db_cursor
DEALLOCATE db_cursor
Wir schreiben alle Duplikate in einen Cursor und iterieren darüber. Für jeden Durchlauf führen wir unser DELETE TOP (x) Statement aus. Damit können wir alle Duplikate entfernen, ohne die komplette Zieltabelle leeren zu müssen.
Methode 3: Common Table Expression
Unsere zweite Methode zum Entfernen doppelter Datensätze funktioniert wunderbar, allerdings benötigen wir dafür ungefähr 20 Zeilen Code. Durch die Verwendung von sogenannten Common Table Expressions (CTEs) WITH common_table_expression und Window Functions SELECT - OVER-Klausel schaffen wir es die Duplikate mit einem Bruchteil der Codezeilen zu entfernen. Diese Features sind in den meisten gängigen relationalen Datenbanksystemen implementiert, allerdings kann die Syntax abweichen.
WITH EmployeeSkillsDuplicates AS
(
SELECT EmployeeId, SkillDescription , ROW_NUMBER()
OVER(PARTITION BY EmployeeId, SkillDescription
ORDER BY EmployeeId, SkillDescription ) RowNumber
FROM dbo.EmployeeSkills
)
DELETE FROM EmployeeSkillsDuplicates WHERE RowNumber > 1
Im ersten Schritt verwenden wir die ROW_NUMBER Funktion. Diese Funktion weist jeder Zeile eines SELECT Results dynamisch eine Zeilennummer zu und gibt diese aus.
Um dies zu veranschaulichen, führen wir folgendes SQL Statement aus
SELECT EmployeeId, SkillDescription , ROW_NUMBER() FROM dbo.EmployeeSkills
Die Ausgabe zeigt die aufsteigende Nummerierung der einzelnen Zeilen
| EmployeeId | SkillDescription | RowNumber |
|---|---|---|
| 1 | Datenbankentwicklung | 1 |
| 1 | Datenbankentwicklung | 2 |
| 2 | Webentwicklung | 3 |
| 2 | Webentwicklung | 4 |
| 2 | Webentwicklung | 5 |
| 3 | Projektmanagement | 6 |
Fügen wir dem Statement nun die OVER Klausel hinzu sieht das Ergebnis wie folgt aus:
| EmployeeId | SkillDescription | RowNumber |
|---|---|---|
| 1 | Datenbankentwicklung | 1 |
| 1 | Datenbankentwicklung | 2 |
| 2 | Webentwicklung | 1 |
| 2 | Webentwicklung | 2 |
| 2 | Webentwicklung | 3 |
| 3 | Projektmanagement | 1 |
Die OVER Klausel erzeugt mit Hilfe des PARTITION BY Befehls für alle eindeutigen Kombinationen der angegebenen Spalten eine Gruppe in welcher die ROW_NUMBER Funktion jeweils wieder von 1 anfängt zu zählen.
Da es möglich ist auf eine Common Table Expression, die nur auf eine Tabelle zugreift, den DELETE Befehl anzuwenden ist der Rest des Skripts leicht verständlich. Wir löschen alle Zeilen mit einer RowNumber größer als 1. Damit werden alle Duplikate entfernt und es bleibt jeweils 1 Datensatz in der Tabelle.
Das Ergebnis aller drei Methoden ist der folgende Inhalt der Tabelle EmployeeSkills:
| EmployeeId | SkillDescription |
|---|---|
| 1 | Datenbankentwicklung |
| 2 | Webentwicklung |
| 3 | Projektmanagement |
Fazit
In diesem Blogpost haben wir drei Methoden vorgestellt, mit denen man doppelte Zeilen in einer relationalen Datenbanktabelle entfernen kann. Die Beispiele wurden für den SQL Server erstellt, da wir für die SpikeTime Zeiterfassung den SQL Server verwenden. Die Skripte lassen sich jedoch leicht für die meisten gängigen Datenbanksysteme adaptieren. Wir bei SpikeTime versuchen immer darauf zu achten, bereits beim Design der Datenbank darauf zu achten, dass unsaubere Daten, wie Duplikate, gar nicht erst entstehen können (Stichwort Datenbank-Constraints).
Feedback
Wie immer freuen wir uns über jegliches Feedback zu SpikeTime und auch zu diesem Artikel. Dazu schreibt eine E-Mail an mail@spiketime.de oder verwendet das Supportformular in SpikeTime.