Doppelte Zeilen in einer Datenbanktabelle löschen

  • 15.07.2018

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.