Blog
Wednesday, 13. January 2021

Duplikate aus SQL Tabellen entfernen

Simon
IT-Consultant

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:

Mainzer Datenfabrik - Duplikate aus SQL Tabellen 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;
Mainzer Datenfabrik - Duplikate aus SQL Tabellen entfernen

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:

Mainzer Datenfabrik - Duplikate aus SQL Tabellen entfernen

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:

Mainzer Datenfabrik - Duplikate aus SQL Tabellen entfernen

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;

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:

Mainzer Datenfabrik - Duplikate aus SQL Tabellen entfernen

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.

Mainzer Datenfabrik - Duplikate aus SQL Tabellen entfernen

Leistungspakete der Mainzer Datenfabrik

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.

SQL Server auf Azure Troubleshooting

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
Mainzer Datenfabrik - Duplikate aus SQL Tabellen entfernen

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;
Mainzer Datenfabrik - Duplikate aus SQL Tabellen entfernen

Wir haben also gesehen, dass es einige Möglichkeiten gibt, mit wenig Aufwand, doppelte Einträge aus einer Tabelle zu entfernen.

Interesse geweckt?

Unsere Expert:innen stehen Ihnen bei allen Fragen rund um Ihre IT Infrastruktur zur Seite.

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!

Taunusstraße 72
55118 Mainz
info@madafa.de
+49 6131 3331612
Bürozeiten
Montag bis Donnerstag:
9:00 - 17:00 Uhr MEZ

Freitags:
9:30 - 14:00 Uhr MEZ