Datenbereinigung in SQL

Die Datenbereinigung ist ein wichtiger Bestandteil jeder Datenanalyse. Hier werden wir Techniken zur Datenbereinigung in SQL besprechen.

Es ist fast unmöglich, sich auf die Arbeit zu konzentrieren, wenn mein Schreibtisch unordentlich ist. Wenn er mit Papier, Kaffeebechern oder zufälligen Spielsachen, die meine Tochter irgendwie in mein Büro geschmuggelt hat, vollgestopft ist, habe ich keine Chance, irgendetwas zu erledigen, bis mein Schreibtisch wieder in Ordnung ist. Aus irgendeinem Grund ist es so, als ob die Unordnung auf meinem Schreibtisch irgendwie den Weg in meinen Kopf gefunden hat.

Dieser Gedanke gilt auch für die Datenbereinigung. Ich habe schon oft Stunden mit einer Analyse verbracht und meine Schlussfolgerungen gezogen, nur um dann eine Unstimmigkeit in den Daten zu entdecken, die meinen gesamten Bericht zunichte macht. Als Analysten stürzen wir uns in der Regel sofort in die Analyse von Daten, ohne uns vorher die Zeit zu nehmen, sicherzustellen, dass unsere Daten sauber sind. Das kann zu vielen Stunden verschwendeter Zeit führen - oder schlimmer noch, zu ungenauen Berichten.

Was ist Datenbereinigung?

Bei der Datenbereinigung (auch Data Cleansing genannt) geht es darum, alle Ungenauigkeiten in einem Datensatz zu identifizieren und zu beheben. Dies ist der erste Schritt einer jeden Analyse und umfasst das Löschen von Daten, das Aktualisieren von Daten und das Auffinden von Inkonsistenzen oder Dingen, die einfach keinen Sinn ergeben.

Alle SQL-Funktionen, die für die Datenbereinigung in SQL erforderlich sind, lernen Sie in unserem SQL von A bis Z Kurs. Der Kurs enthält 7 interaktive SQL-Kurse, in denen Sie das gesamte Spektrum von SQL erlernen, von den Grundlagen über mittlere Themen bis hin zu fortgeschrittenen SQL-Konzepten wie Fensterfunktionen und rekursiven Abfragen. Es handelt sich um die umfassendste Sammlung von SQL-Kursen, die im Internet verfügbar ist.

Datenbereinigungstechniken

Nachdem Sie nun eine Vorstellung davon bekommen haben, wollen wir uns nun die SQL-Techniken ansehen, die Sie zur Datenbereinigung einsetzen können. Für jedes Beispiel verwenden wir die unten abgebildete Tabelle Unternehmen. Sie enthält Informationen über verschiedene Unternehmen:

Wie und wann man Daten löscht

Manchmal werden Sie auf Szenarien stoßen, in denen Sie Daten aus Ihrem Dataset entfernen müssen. Dies kann der Fall sein, wenn die Daten für Ihre Analyse nicht relevant sind oder wenn sie doppelt vorhanden oder ungenau sind. In den folgenden Beispielen werden wir diese verschiedenen Szenarien und die entsprechenden Vorgehensweisen untersuchen.

Beispiel 1: Löschen von doppelten Daten

Als Erstes suchen wir nach Daten, die gelöscht werden müssen. Dies kann aufgrund von Duplikaten geschehen oder weil die Daten nicht relevant sind. In dieser Tabelle können wir schnell sehen, dass die Zeile für das Unternehmen Toughtam doppelt vorhanden ist. In einem großen Datensatz ist dies nicht so leicht zu erkennen. Bevor wir diese Zeile einfach löschen, sollten wir besprechen, wie wir sie finden können.

In diesem Datensatz sollte jedes Unternehmen nur eine Zeile haben, also verwenden wir die Klauseln GROUP BY und HAVING, um doppelte Namen zu identifizieren. In dieser Abfrage wird mit GROUP BY gezählt, wie oft jeder Name in der Datenbank vorhanden ist. Dann verwendet sie die HAVING Klausel, um die Ergebnisse nur nach den Namen zu filtern, die mehr als einmal vorhanden sind.

SELECT name, 
	 COUNT(name) as count
FROM companies
GROUP BY name
HAVING(count > 1)

Diese Abfrage wird das folgende Ergebnis liefern:

Sehr gut! Jetzt wissen wir, dass die Firma Toughtam doppelt vorhanden ist, aber wie löschen wir eine der Zeilen? Wir verwenden eine Kombination aus ROW_NUMBER() und DELETE. Zunächst fügen wir für jede Zeile eine Zeilennummer hinzu, die auf der Spalte Name basiert:

SELECT name, 
	 ROW_NUMBER() OVER(PARTITION BY name) AS rn
FROM companies

Dazu fügen wir eine neue Spalte hinzu, die die Zeilennummer für jeden Namen anzeigt. Wie Sie sehen können, gibt es jetzt eine 1 und eine 2 für die Zeilen für Toughtam. Nun führen wir eine DELETE -Anweisung aus, um alle Zeilen zu entfernen, in denen die Spalte rn größer als 1 ist.

DELETE 
FROM (
SELECT name, 
	 	ROW_NUMBER() OVER(PARTITION BY name) AS rn
FROM companies
) 
WHERE rn > 1

Unser Datensatz sieht nun wie folgt aus:

Beispiel 2: Ordnen der Daten vor dem Löschen

In diesem Beispiel sind die Zeilen für Toughtam identisch (mit Ausnahme von id), so dass wir die Zeilen nicht nach etwas anderem ordnen als danach, wie sie in der Datenbank erscheinen. Oftmals werden Sie feststellen, dass Zeilen doppelt vorhanden sind, aber vielleicht sind die Felder nicht identisch. In diesem Fall können Sie eine ORDER BY Klausel nach PARTITION BY hinzufügen.

Nehmen wir zum Beispiel an, die Daten sähen wie folgt aus:

Es sieht so aus, als ob dieser Datensatz für dieses Unternehmen am 20.1.2023 aktualisiert wurde und die Anzahl der Mitarbeiter gestiegen ist. Wenn wir den neuesten Datensatz behalten wollten, würden wir diesen ausführen:

DELETE 
FROM (
SELECT name, 
	 	ROW_NUMBER() OVER(PARTITION BY name ORDER BY created DESC) AS rn
FROM companies
) 
WHERE rn > 1

Wie Sie sehen, ordnen wir die Anweisung ROW_NUMBER() nach dem Feld created in absteigender Reihenfolge an, wobei der zuletzt erstellte Datensatz zuerst gezogen wird. Anschließend löschen wir die Datensätze, die nach dem ersten Datensatz kommen.

Weitere Informationen zum Auffinden doppelter Werte in SQL finden Sie in unserem Artikel How to Find Duplicate Values in SQL.

Beispiel 3: Entfernen von NULL-Werten

Werfen wir nun einen Blick auf NULLs. NULL zeigt einen fehlenden Wert an; mehr darüber können Sie hier lesen. Je nachdem, welche Auswirkungen NULL Werte in Ihren Daten haben, können Sie diese Zeilen entweder entfernen oder sie aktualisieren. In unserem Beispiel sehen wir zwei NULL Werte. Eine Zeile enthält den Wert NULL für die Branche und die andere den Wert year_founded. Wir werden beide auf unterschiedliche Weise behandeln.

Ein Unternehmen muss ein Jahr haben, in dem es gegründet wurde. Wir verwenden DELETE, um die Zeile zu entfernen, in der der Wert fehlt, da es sich um fehlerhafte Daten zu handeln scheint.

SELECT
FROM companies 
WHERE year_founded IS NULL 

In der obigen Abfrage verwenden wir die Klausel IS NULL. Diese Klausel prüft die Spalte year_founded und gibt alle Zeilen zurück, in denen sie IS NULL enthält. Sobald wir uns vergewissert haben, dass es sich um die Zeile handelt, die wir entfernen wollen, können wir sie durch Ausführen löschen:

DELETE
FROM companies 
WHERE year_founded IS NULL 

An diesem Punkt sind wir mit dem Löschen von fehlerhaften Daten fertig und können mit der Anweisung UPDATE fortfahren. Damit korrigieren wir unseren anderen Wert NULL.

Wie man Daten aktualisiert

Die Anweisung UPDATE wird verwendet, um vorhandene Daten zu ändern. Sie können diese Datenbereinigungstechnik verwenden, um ungenaue Daten zu korrigieren oder um Ihre Daten zu formatieren (um sie lesbarer zu machen). In den folgenden Beispielen werden wir diese Arten von Szenarien durchgehen, um zu verstehen, wie man Daten mit UPDATE manipulieren kann.

Beispiel 1: NULL-Werte mit einer aussagekräftigen Bezeichnung versehen

Wie wir bereits gesehen haben, gibt es ein Unternehmen, das einen NULL Wert für die Branche hat. Das ist in Ordnung, denn in unserer hypothetischen Situation wissen wir, dass nicht alle Branchen in unserer Datenbank verfügbar sind. Was wir in dieser Situation tun müssen, ist, den NULL-Wert in "Andere" zu ändern. Eine andere Möglichkeit wäre, NULLs durch "NA" oder "Nicht anwendbar" zu ersetzen.

Verwenden wir zunächst SELECT, um die Zeile mit der Branche NULL zu finden:

SELECT *
FROM companies 
WHERE industry IS NULL 

Da wir nun wissen, dass wir die richtige Zeile ausgewählt haben, können wir die Spalte "Branche" unter UPDATE aufrufen. Wir tun dies, indem wir ausführen:

UPDATE companies 
SET industry = ‘Other’
WHERE industry IS NULL 

Wenn Sie UPDATE verwenden, müssen Sie zunächst die Tabelle identifizieren, die Sie ändern möchten. In unserem Beispiel ist diese Tabelle companies. Als Nächstes müssen wir angeben, welche Spalte wir aktualisieren und worauf wir sie aktualisieren wollen. Wir identifizieren die Spalte mit SET [column name]. Dann legen wir mit = [ Wert ] fest, in was wir die Spalte ändern wollen. Die Klausel WHERE ist die gleiche wie die Anweisung SELECT. Wir wollen die Branche nur dann in "Andere" ändern, wenn die Branche IS NULL ist.

Beispiel 2: Die Großschreibung von Werten korrigieren

An dieser Stelle sehen unsere Daten schon besser aus, aber die Spalte state könnte noch ein wenig aufgeräumt werden. Einige der Werte sind in Großbuchstaben und einige in Kleinbuchstaben. Normalerweise wird die Abkürzung des Bundeslandes großgeschrieben, also sollten wir alle Werte, die klein geschrieben werden, in Großbuchstaben umwandeln.

Da wir sicherstellen wollen, dass alle Statuswerte in Großbuchstaben geschrieben werden, können wir die Anweisung ausführen:

UPDATE companies 
SET state = UPPER(state)

Wenn Sie die Anweisung UPPER() um einen Spaltennamen herum schreiben, ändern Sie alle Buchstaben in Großbuchstaben. (Mit der Anweisung LOWER() machen Sie das Gegenteil - Sie ändern alle Buchstaben in Kleinbuchstaben). Jetzt sieht unsere Tabelle wie folgt aus:

Beispiel 3: Korrektur von logischen Fehlern

Ein letzter Punkt, auf den Sie in Ihrem Datensatz achten sollten, sind logische Fehler. In unseren Daten sehen wir, dass ein Unternehmen einen year_founded Wert von 2032 hat. Das ist einfach nicht möglich, da ein Unternehmen nicht in der Zukunft gegründet werden kann. Wir können in die Zukunft datierte Datensätze identifizieren, indem wir ausführen:

SELECT *
FROM companies 
WHERE year_founded > CURRENT_TIMESTAMP()

CURRENT_TIMESTAMP() gibt das aktuelle Datum und die aktuelle Uhrzeit zurück. In der obigen Anweisung werden alle Datensätze abgerufen, bei denen year_founded nach dem Zeitpunkt der Ausführung der Abfrage liegt. Sie können auch ein Datum hart codieren, z. B. das heutige Datum.

Nach ein wenig Recherche stellen wir fest, dass es sich um einen Tippfehler handelt und das Unternehmen 2012 gegründet wurde, also ändern wir diesen Datensatz:

UPDATE companies
SET year_founded = 2012
FROM companies 
WHERE id = 3

In diesem Szenario haben wir eine bestimmte ID in der WHERE -Klausel angegeben. Der Grund dafür ist, dass Sie höchstwahrscheinlich nicht alle year_founded -Werte aktualisieren möchten, um dasselbe Jahr für jedes Unternehmen zu erhalten, dessen Gründungsjahr in der Zukunft liegt. Wir kennen das Gründungsjahr für dieses spezielle Unternehmen, also aktualisieren wir nur diesen Datensatz.

Denken Sie immer daran, Ihre Daten zu bereinigen

Unsere Daten sehen jetzt viel klarer aus als der ursprüngliche Datensatz. Die Datenbereinigung ist zwar mühsam, aber ein unerlässlicher Teil des Datenanalyseprozesses. Gehen Sie nie davon aus, dass die Daten, mit denen Sie arbeiten, sauber sind. Untersuchen Sie die Daten, indem Sie nach Duplikaten, NULLs und logischen Irrtümern suchen.

Sie kennen nun mehrere SQL-Techniken, mit denen Sie Ihre Daten verändern können, darunter DELETE und UPDATE. Ein guter nächster Schritt ist es, die SQL von A bis Z Kurs zu besuchen. Er enthält 7 interaktive SQL-Kurse, darunter einen kompletten Kurs über die Klauseln DELETE, UPDATE und INSERT. Es handelt sich dabei um die umfassendste Sammlung von SQL-Kursen, die im Internet verfügbar ist. Dieser Track wird Ihnen helfen, alles, was Sie in diesem Artikel gelernt haben, zu festigen und gibt Ihnen die Möglichkeit, zusätzliche Übungen durchzuführen. Viel Spaß bei der Datenbereinigung!