SQL Duplikate entfernen: Umfassende Methoden und bewährte Praktiken

Doppelte Datensätze sind ein häufiges Problem, das die Datenintegrität und die Datenbankleistung beeinträchtigen kann. Das Entfernen dieser Duplikate ist wichtig, um die Datengenauigkeit zu erhalten, die Speicherung zu optimieren und die Abfrageleistung zu verbessern. In diesem Artikel werden wir verschiedene Techniken zum Entfernen doppelter Zeilen in SQL untersuchen, die auf verschiedene Anwendungsfälle und Datenbankmanagementsysteme zugeschnitten sind.

Für den Anfang empfehle ich dir die DataCamp-Kurse Introduction to SQL und Learn SQL, um die Grundlagen der Datenextraktion und -analyse mit SQL zu erlernen. Außerdem finde ich das SQL Basics Cheat Sheet, das du herunterladen kannst, sehr hilfreich, weil es alle gängigen SQL-Funktionen enthält.

Verstehen von doppelten Zeilen in SQL

Doppelte Zeilen in SQL beziehen sich auf Datensätze innerhalb einer Tabelle, die in allen oder ausgewählten Spalten identische Werte enthalten. Zu den häufigsten Ursachen für doppelte Zeilen in SQL gehören die folgenden:

• Fehlende Primärschlüssel: Wenn Tabellen keinen definierten Primärschlüssel oder keine eindeutige Einschränkung haben, gibt es keinen Mechanismus, der das Einfügen von doppelten Daten verhindert. Das kann passieren, wenn eine Tabelle nicht normalisiert ist und/oder es Probleme mit transitiven Abhängigkeiten gibt.
• Fragen der Datenintegration: Beim Zusammenführen von Datensätzen aus verschiedenen Quellen können unsachgemäße Verknüpfungen oder Inkonsistenzen in den Datenformaten versehentlich zu Duplikaten führen.
• Fehler bei der manuellen Dateneingabe: Menschliche Fehler, wie die mehrfache Eingabe desselben Datensatzes, sind eine weitere häufige Ursache für doppelte Zeilen.

Im weiteren Verlauf des Artikels werden wir uns ansehen, wie man Duplikate in SQL entfernt, und wir werden den Artikel in zwei Blöcke unterteilen. Im ersten Abschnitt geht es darum, wie du Duplikate in den Daten, die du für einen Bericht oder ein Dashboard abrufst, entfernst; im zweiten Abschnitt schauen wir uns an, wie du Duplikate in der Datenbank entfernst.

Methoden zum Entfernen von Duplikaten in den Daten, die du abrufst

Es gibt verschiedene Methoden, um Duplikate beim Abrufen von Datensätzen in SQL zu entfernen. Jede Methode hängt vom DBMS ab, wie z.B. SQL Server, MySQL und PostgreSQL. In diesem Abschnitt werden wir uns die Methoden zum Entfernen von Duplikaten ansehen und dabei auf die Besonderheiten der einzelnen Datenbanken eingehen. Beachte, dass diese Methoden die Daten filtern und eindeutige Datensätze zurückgeben und die zugrunde liegende Tabelle nicht verändern.

Verwendung des Schlüsselworts DISTINCT

Das Schlüsselwort DISTINCT wird in einer SELECT Anweisung verwendet, um eindeutige Zeilen abzurufen. Die Syntax des Schlüsselworts DISTINCT zum Entfernen von Duplikaten ist für MySQL-, PostgreSQL- und SQL Server-Datenbanken ähnlich. Mit der folgenden Abfrage werden eindeutige Kundennamen aus der Tabelle customers abgerufen.

SELECT DISTINCT Name 
FROM customers;

Verwendung von GROUP BY mit Aggregatfunktionen

Die GROUP BY Klausel kann in Kombination mit anderen Aggregatfunktionen wie MAX(), MIN() oder COUNT() helfen, doppelte Datensätze aus Tabellen zu entfernen. Die GROUP BY Klausel hilft bei der Auswahl bestimmter Datensätze, die beibehalten werden sollen, während andere Duplikate gelöscht werden.

Angenommen, du möchtest doppelte Kundendatensätze löschen, aber den mit der höchsten ID behalten. Du verwendest die GROUP BY Klausel mit der Funktion MAX(), wie unten gezeigt.

-- Delete duplicate rows from the 'customers' table (aliased as c1)
DELETE c1
FROM customers c1
-- Find the maximum ID for each unique Name
JOIN (
    SELECT Name, MAX(ID) AS MaxID
    FROM customers
    GROUP BY Name
) c2
-- Match rows based on 'Name' and keep the row with the maximum ID
ON c1.Name = c2.Name 
AND c1.ID < c2.MaxID;

MySQL und SQL Server unterstützen die oben genannte Syntax von GROUP BY mit Aggregatfunktionen und der JOIN Klausel. 

Verwendung von ROW_NUMBER() mit Common Table Expressions (CTE)

Mit der Funktion ROW_NUMBER() in Kombination mit einer Common Table Expression (CTE) kannst du Duplikate nach deinen Kriterien herausfiltern. Die Funktion ROW_NUMBER, die mit den Klauseln PARTITION BY und ORDER BY verwendet wird, weist jeder Zeile eine eindeutige fortlaufende Nummer zu. Mit dieser Methode können die Zeilen herausgefiltert werden, die die erforderlichen Kriterien nicht erfüllen.

Die folgende Abfrage identifiziert Duplikate und entfernt alle bis auf das erste Vorkommen.

-- Common Table Expression (CTE) to rank rows based on 'Name'
WITH CTE AS (
    SELECT ID, Name, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY ID ASC) AS RowNum
    FROM customers
)
-- Select only the unique records where RowNum = 1
SELECT ID, Name
FROM CTE
WHERE RowNum = 1;

Diese Methode funktioniert gut für moderne Versionen von SQL Server, MySQL und PostgreSQL. Sie ist nützlich für größere Datensätze oder komplexere Bedingungen, da du so genau festlegen kannst, welches Duplikat beibehalten werden soll.

Entfernen von Duplikaten mit Self-JOIN

Ein Self-Join ermöglicht es dir, eine Tabelle mit sich selbst zu vergleichen. Das ist hilfreich, um doppelte Zeilen zu identifizieren und zu entfernen, indem du Datensätze anhand bestimmter Kriterien vergleichst. Im folgenden Beispiel wird der Self-Join verwendet, um die Zeile mit der höheren ID zu löschen, wobei nur das erste Vorkommen jedes Namens erhalten bleibt.

-- Delete duplicate rows using self-join
DELETE c1
FROM customers c1
JOIN customers c2
ON c1.Name = c2.Name AND c1.ID > c2.ID;

Die oben beschriebene Methode funktioniert in allen gängigen Datenbanken, einschließlich SQL Server, MySQL und PostgreSQL. In unserem SQL-Kurs für Fortgeschrittene erfährst du mehr über die Verwendung von Aggregatfunktionen und Joins zum Filtern von Daten.

Methoden zum Entfernen von Duplikaten in der Datenbank

Du kannst doppelte Datensätze zwar mit Hilfe von Abfragen entfernen, aber du kannst sie auch dauerhaft aus der Datenbank löschen. Dieser Ansatz ist wichtig, um die Datenqualität zu erhalten. Die folgenden Methoden werden verwendet, um Duplikate aus der Datenbank zu entfernen.

ROW_NUMBER() und DELETE verwenden

Die Funktion ROW_NUMBER() weist den Zeilen innerhalb einer definierten Partition eine fortlaufende Nummer zu. In Verbindung mit der Anweisung DELETE hilft sie bei der Identifizierung von Duplikaten, indem sie Zeilen nach bestimmten Spalten ordnet und unerwünschte Datensätze entfernt. Diese Methode gilt für moderne Versionen von MySQL (ab 8.0), PostgreSQL und SQL Server.

Angenommen, du möchtest doppelte Kundendatensätze anhand der Spalte Name entfernen und nur das erste Vorkommen behalten (kleinste ID):

-- Common Table Expression (CTE) to rank rows based on 'Name'
WITH CTE AS (
    SELECT ID, Name, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY ID ASC) AS RowNum
    FROM customers
)
-- Delete rows from the 'customers' table where the row number is greater than 1
DELETE FROM customers
WHERE ID IN (SELECT ID FROM CTE WHERE RowNum > 1);

DELETE mit Unterabfrage verwenden

Manchmal kann eine einfache DELETE Operation mit einer Subquery Duplikate aus der Datenbank entfernen. Diese Methode ist für ältere Versionen von MySQL oder PostgreSQL geeignet, bei denen ROW_NUMBER() möglicherweise nicht verfügbar ist.

Die folgende Abfrage löscht Zeilen aus der Tabelle customers, bei denen ID nicht das Minimum für jedes Name ist, und behält nur die Zeile mit dem kleinsten ID für jedes einzigartige Name.

-- Delete rows from the 'customers' table
DELETE FROM customers
WHERE ID NOT IN (
    -- Subquery to find the minimum ID for each unique Name
    SELECT MIN(ID)
    FROM customers
    GROUP BY Name
);

Verwendung von GROUP BY mit HAVING-Klausel

Wenn du nach doppelten Werten in bestimmten Spalten suchen musst, kann die GROUP BY Klausel in Kombination mit der HAVING Klausel verwendet werden, um Duplikate zu identifizieren. Mit dieser Methode kannst du bestimmte Zeilen anhand der angegebenen Kriterien löschen. Diese Methode ist mit SQL Server, MySQL und PostgreSQL kompatibel.

Die folgende Abfrage löscht Zeilen aus der Tabelle customers, bei denen die ID zu einer Gruppe von Duplikaten gehört.

-- Delete rows from the 'customers' table where there are duplicates
DELETE FROM customers
WHERE ID IN (
    -- Subquery to find IDs of duplicate rows
    SELECT ID
    FROM customers
    GROUP BY ID
    HAVING COUNT(*) > 1
);

Temporäre Tabellen für die Stapelverarbeitung verwenden

Temporäre Tabellen sind effizient für die Stapelverarbeitung und das Entfernen von Duplikaten in großen Datenbeständen. Diese Methode ist nützlich, wenn einzelne Abfragen Leistungsprobleme verursachen können. Die folgende Abfrage erstellt eine temporäre Tabelle, um das Minimum ID für jede customer_name zu speichern und Zeilen aus der Tabelle customers zu löschen, wenn die ID nicht in der Tabelle temp_customers enthalten ist.

-- Create a temporary table
CREATE TEMPORARY TABLE temp_customers AS
SELECT MIN(customer_id) AS ID, customer_name
FROM customers
GROUP BY customer_name;
DELETE FROM customers
WHERE customer_id NOT IN (SELECT ID FROM temp_customers);

Die obige Syntax mit CREATE TEMPORARY TABLE wird nur in MySQL- und PostgreSQL-Datenbanken unterstützt. 

Duplikate in SQL Server entfernen

SQL Server bietet verschiedene Methoden, um doppelte Datensätze aus der Datenbank zu entfernen. Zu diesen Methoden gehört die Verwendung von DISTINCT mit INTO, ROW_NUMBER() und temporären Tabellen.

Verwendung von DISTINCT mit INTO

Du kannst das Schlüsselwort DISTINCT in einer SELECT Anweisung verwenden, um eine neue Tabelle mit eindeutigen Datensätzen zu erstellen. Du kannst die alte Tabelle löschen, sobald du überprüft hast, dass die neue Tabelle die angegebenen Datensätze enthält. Im folgenden Beispiel wird die Tabelle unique_customers mit eindeutigen Datensätzen aus der Tabelle customers erstellt.

-- Select distinct rows from 'customers' and create a new table 'unique_customers'
SELECT DISTINCT *
INTO unique_customers
FROM customers;
-- Drop the original 'customers' table to remove it from the database
DROP TABLE customers;
-- Rename the 'unique_customers' table to 'customers' to replace the original table
EXEC sp_rename 'unique_customers', 'customers';

Using ROW_NUMBER()

Du kannst auch die Funktion ROW_NUMBER() verwenden, um doppelte Datensätze aus dem SQL Server zu entfernen. Angenommen, du hast eine Tabelle Customers mit doppelten Zeilen, die auf der Spalte CustomerName basieren, und du möchtest alle doppelten Gruppen außer dem ersten Vorkommen löschen.

-- Common Table Expression (CTE) to assign a row number to each customer 
WITH CTE AS (
    SELECT CustomerID, CustomerName, ROW_NUMBER() OVER (PARTITION BY CustomerName ORDER BY CustomerID ASC) AS RowNum
    FROM Customers
)
-- Delete rows from the CTE
DELETE FROM CTE
WHERE RowNum > 1;

Temporäre Tabelle verwenden

Da SQL Server die Funktion CREATE TEMPORARY TABLE nicht unterstützt, verwendest du die Funktion SELECT INTO. Temporäre Tabellen in SQL Server verwenden # als Präfix für den Tabellennamen.

-- Create a temporary table
SELECT MIN(CustomerID) AS ID, CustomerName
INTO #temp_customers
FROM customers
GROUP BY CustomerName;
-- Delete rows from the 'customers' table where the ID is not in the temporary table
DELETE FROM customers
WHERE CustomerIDNOT IN (SELECT ID FROM #temp_customers);
-- Optionally drop the temporary table after use
DROP TABLE #temp_customers;

Ich empfehle dir, unseren SQL Server Fundamentals Skill Track auszuprobieren, um deine Fähigkeiten zum Verbinden von Tabellen und zur Datenanalyse zu verbessern. In der Ausbildung zum/zur SQL Server Developer/in lernst du, wie du mit SQL Server Abfragen schreibst, Fehler behebst und sie optimierst.  

Beste Praktiken

Doppelte Zeilen sind ein häufiges Problem, das die Datenqualität und die Datenbankleistung beeinträchtigt. Beachte die folgenden Best Practices, um zu verhindern, dass doppelte Datensätze in deine Datenbank eingefügt werden.

• Verwende Primärschlüssel: Die Primärschlüsselspalte stellt sicher, dass jeder Datensatz eindeutige Informationen enthält und verhindert, dass doppelte Werte in die Tabelle gelangen.
• Implementiere eindeutige Beschränkungen: Die Anwendung von eindeutigen Einschränkungen auf jede Spalte stellt sicher, dass es keine Duplikate in Spalten mit nicht primären Schlüsseln gibt, wie z. B. E-Mail-Adressen oder Telefonnummern.
• Richtiges Datenbankdesign und Normalisierung: Ein effektives Schema-Design und die Normalisierung der Datenbank helfen, Redundanzen und doppelte Daten zu reduzieren. Dieser Ansatz stellt sicher, dass jeder Datensatz in bestimmten Tabellen gespeichert wird.
• Verwende eindeutige Indizes: Verwende eindeutige Indizes, um sicherzustellen, dass bestimmte Spaltenkombinationen eindeutig sind, ohne dass vollständige Beschränkungen auf Tabellenebene für den gesamten Datensatz erforderlich sind.
• Regelmäßige Datenaudits: Führe regelmäßig Datenprüfungen durch, indem du Abfragen durchführst, um potenzielle Duplikate auf der Grundlage deiner Geschäftsregeln zu identifizieren.

Fazit

Das Erkennen und Entfernen von doppelten Zeilen ist wichtig, um die Effizienz der Datenbank und die Genauigkeit der Daten zu erhalten. Es ist immer eine gute Praxis, deine Daten zu sichern, bevor du Änderungen vornimmst, um einen versehentlichen Datenverlust zu vermeiden.

Wenn du daran interessiert bist, ein kompetenter Datenanalytiker zu werden, dann schau dir unseren Karrierepfad Associate Data Analyst in SQL an, um die notwendigen Fähigkeiten zu erlernen. Der Kurs Reporting in SQL ist auch geeignet, wenn du lernen willst, wie man professionelle Dashboards mit SQL erstellt. Schließlich empfehle ich dir, die SQL Associate-Zertifizierung zu erwerben, um zu zeigen, dass du SQL für die Datenanalyse beherrschst und dich von anderen Datenexperten abhebst.