Course
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.
Erhalte eine Top-SQL-Zertifizierung
Stelle deine SQL-Kenntnisse unter Beweis und bringe deine Datenkarriere voran.
Häufig gestellte SQL-Fragen
Was verursacht doppelte Zeilen in SQL-Datenbanken?
Doppelte Zeilen können durch verschiedene Faktoren entstehen, z. B. durch ein unsachgemäßes Datenbankdesign, fehlende Primärschlüssel, Datenintegration aus verschiedenen Quellen, manuelle Dateneingabefehler oder Datenmigrationsprobleme, bei denen die Validierung nicht richtig durchgesetzt wurde.
Kann ich Duplikate basierend auf mehreren Spalten verhindern?
Ja, du kannst die Eindeutigkeit über mehrere Spalten hinweg mit zusammengesetzten Schlüsseln oder eindeutigen Beschränkungen erzwingen. Dadurch wird sichergestellt, dass Kombinationen von Werten in diesen Spalten eindeutig bleiben.
Wie werden mit dem Schlüsselwort DISTINCT doppelte Zeilen entfernt?
Mit dem Schlüsselwort DISTINCT
werden nur Duplikate in den Abfrageergebnissen entfernt und die zugrunde liegenden Daten nicht verändert.
Welche Methode kannst du verwenden, um doppelte Datensätze dauerhaft aus der Datenbank zu löschen?
Du kannst ROW_NUMBER()
mit DELETE
, DELETE
mit Subquery, GROUP
BY mit HAVING
Klausel und temporäre Tabellen für die Stapelverarbeitung verwenden, um doppelte Zeilen dauerhaft aus der Datenbank zu löschen.
Können Duplikate die Leistung meiner Datenbank beeinträchtigen?
Ja, Duplikate können die Leistung beeinträchtigen, indem sie die Speicherkosten erhöhen, Abfragen verlangsamen und die Datenanalyse erschweren.
Lerne SQL mit DataCamp
Course
Datenmanipulation in SQL
Course