Als Data Scientist oder Data Analyst steht Datenarbeit naturgemäß im Mittelpunkt. Es gibt viele Quellen, aus denen du deine Daten beziehst. Häufig liegen sie in einer SQL-Datenbank – dann ist ein Verständnis von SQL-Abfragen entscheidend, um deinen Job erfolgreich zu machen. Dieser Artikel führt dich von grundlegenden bis hin zu fortgeschrittenen Befehlen, die dir im Alltag als Data Analyst oder Data Scientist helfen.
Um die oben genannten Befehle zu veranschaulichen, nutzen wir die Open-Source-Datenbank "DVD Rental" und zeigen dir, wie sich die Befehle anwenden lassen. Das Entity-Relationship-Diagramm (ERD) findest du hier.
Für diesen Artikel haben wir die Datenbank lokal eingerichtet und alle Abfragen mit pgAdmin4 ausgeführt, also mit PostgreSQL. Viele, wenn nicht alle Befehle funktionieren auch auf anderen SQL-Servern. Die Installation von PostgreSQL und das Einrichten der Datenbank selbst sind nicht Teil dieses Artikels; schau dir dafür gern andere Ressourcen an, z. B. DataCamps Beginner's Guide to PostgreSQL.
Einfache Datenabfragen
SELECT FROM
Das einfachste Beispiel ist, alle Inhalte aus einer bestimmten Tabelle abzurufen. Wenn wir alle Filmkategorien aus der Tabelle "category" sehen wollen, kannst du folgende Abfrage verwenden:
SELECT *
FROM category
Das ergibt (hier nur die ersten 10 Zeilen):
Wir haben den Tabellennamen hinter FROM angegeben. Da wir den kompletten Tabelleninhalt möchten, steht nach SELECT ein *.
DISTINCT
Manchmal interessieren uns nur eindeutige Werte. Zum Beispiel: Welche MPAA-Ratings gibt es in der Tabelle "film"? Mit DISTINCT bekommst du nur Unikate:
SELECT DISTINCT(rating)
FROM film
Das ergibt:
Abfragen mit einfachen Bedingungen
WHERE
Oft wollen wir nur Einträge, die bestimmte Bedingungen erfüllen. Mit der WHERE-Klausel legst du Bedingungen fest, die vor der Rückgabe erfüllt sein müssen – im Kern filterst du damit. Beispiel: Nur Filme mit der Bewertung PG-13:
SELECT title
FROM film
WHERE rating = 'PG-13'
Das ergibt (hier nur die ersten 10 Zeilen):
Außerdem können wir uns Filme mit einer Mietrate von mindestens 4.5 ausgeben lassen:
SELECT title, rental_rate
FROM film
WHERE rental_rate >= 4.5
Das ergibt (hier nur die ersten 10 Zeilen):
ORDER BY
Eine Sortierung hilft oft, die Daten besser zu verstehen. Nehmen wir die obige PG-13-Liste, aber sortiert nach Filmlänge von lang nach kurz und mit der jeweiligen Länge. Mit ORDER BY geht das ganz einfach:
SELECT title, length
FROM film
WHERE rating = 'PG-13'
ORDER BY length DESC
Das ergibt (hier nur die ersten 10 Zeilen):
LIMIT
Manchmal interessieren uns nur wenige Einträge. Hier wollen wir tatsächlich nur die 10 längsten PG-13-Filme. Das erreichen wir mit LIMIT.
SELECT title, length
FROM film
WHERE rating = 'PG-13'
ORDER BY length DESC
LIMIT 10
Das ergibt:
Aggregationen
GROUP BY & COUNT( )
Aggregationen fassen Daten zusammen und liefern schnelle Einblicke. Häufig werden sie mit GROUP BY kombiniert. Wenn wir wissen möchten, wie viele Ausleihen jeder Kunde bisher getätigt hat, zählen wir die Ausleihen und sortieren die Ergebnisse mit den meisten Ausleihen oben.
SELECT customer_id, COUNT(rental_id)
FROM rental
GROUP BY customer_id
ORDER BY COUNT(rental_id) DESC
Das ergibt (hier nur die ersten 10 Zeilen):
SUM( )
Wie viel Geld hat jeder Kunde ausgegeben? Das erledigt die Aggregatfunktion SUM. Wir möchten die Top-Spender oben sehen.
SELECT customer_id, SUM(amount)
FROM payment
GROUP BY customer_id
ORDER BY SUM(amount) DESC
Das ergibt (hier nur die ersten 10 Zeilen):
AVG( ) & HAVING
Nach einer gruppierten Aggregation kannst du mit HAVING weitere Bedingungen setzen. Beispiel: Welche MPAA-Rating-Kategorien haben im Schnitt Filme, die länger als 115 Minuten sind?
SELECT rating, AVG(length)
FROM film
GROUP BY rating
HAVING AVG(length) > 115
Das ergibt:
MIN( ) & Alias
Nicht jede Aggregation erfordert eine Gruppierung. Die kürzeste Filmlänge finden wir mit MIN. Außerdem wollen wir die Spalte als 'shortest_movie_length' bezeichnen – das geht mit einem Alias per 'AS'.
SELECT MIN(length) AS shortest_movie_length
FROM film
Das ergibt (hier nur die ersten 10 Zeilen):
Du kannst auch Tabellennamen mit Aliassen versehen. Das ist hilfreich bei langen Namen, vor allem bei Joins (siehe unten). Achte darauf, verständliche Aliasse zu wählen – sie verbessern die Lesbarkeit erheblich.
Weitere nützliche Aggregatfunktionen lernst du im Introduction to SQL course kennen.
Joins
INNER JOIN (JOIN)
Mit einer einzigen Tabelle stößt man schnell an Grenzen. Oft musst du Daten aus mehreren Tabellen kombinieren. Dafür nutzen wir JOINs, die Tabellen über gemeinsame Schlüssel verbinden. Beispiel: Wir suchen Filme, die nicht auf Englisch sind:
SELECT film.title, language.name
FROM film
JOIN language
ON film.language_id = language.language_id
WHERE language.name != 'English'
(Hinweis: In dieser Datenbank gibt es nur englischsprachige Filme. Die Abfrage liefert daher eine leere Tabelle.)
Du kannst auch mehrere Tabellen joinen. Unten möchten wir Kategorien und deren durchschnittliche Mietrate wissen und nach dem höchsten Durchschnitt sortieren.
SELECT category.name, AVG(film.rental_rate) AS average_rating
FROM film
JOIN film_category
ON film.film_id = film_category.film_id
JOIN category
ON film_category.category_id = category.category_id
GROUP BY category.name
ORDER BY average_rating DESC
Das ergibt (hier nur die ersten 10 Zeilen):
Es gibt verschiedene Join-Typen: INNER JOIN, LEFT JOIN, RIGHT JOIN, OUTER JOIN, CROSS JOIN. JOIN ohne Zusatz ist standardmäßig ein INNER JOIN, der nur Einträge zurückgibt, die in beiden Tabellen vorhanden sind. Mehr zu den Unterschieden lernst du im Kurs Joining Data in SQL.
Datentypen ändern
CAST( )
Im obigen Beispiel mit Beträgen behandelt SQL die Werte nur als Zahlen. Wir können sie mit CAST in Geldbeträge umwandeln:
SELECT customer_id, CAST(SUM(amount) AS money)
FROM payment
GROUP BY customer_id
ORDER BY SUM(amount) DESC
Das ergibt (hier nur die ersten 10 Zeilen):
Geld ist nur ein Beispiel. Du kannst Zahlen in Floats, Text sowie in Datum und Uhrzeit konvertieren.
ROUND( )
Ebenso lassen sich Zahlen runden. Im Beispiel zur durchschnittlichen Mietrate hatten wir viele Nachkommastellen. Mit ROUND runden wir auf zwei Dezimalen:
SELECT category.name, ROUND(AVG(film.rental_rate), 2) AS average_rating
FROM film
JOIN film_category
ON film.film_id = film_category.film_id
JOIN category
ON film_category.category_id = category.category_id
GROUP BY category.name
ORDER BY average_rating DESC
Das ergibt (hier nur die ersten 10 Zeilen):
Komplexe Bedingungen
CASE-Anweisung
Durch die Kombination vieler obiger Funktionen und Klauseln lassen sich recht komplexe Abfragen bauen. Es gibt aber Fälle, in denen die Basics nicht reichen. Beispiel: Wir wollen eine Spalte mit Filmlängen-Kategorien hinzufügen. Filme mit weniger als 50 Minuten nennen wir 'Short Film', über 150 Minuten 'Long Film' und dazwischen 'Medium Film'. Das setzen wir mit einer CASE-Anweisung um:
SELECT title, length,
CASE
WHEN length < 50 THEN 'Short Film'
WHEN length > 150 THEN 'Long Film'
ELSE 'Medium Film'
END AS Movie_Length_Category
FROM film
Das ergibt (hier nur die ersten 10 Zeilen):
Wie du siehst, wird das Ergebnis je nach Bedingung zurückgegeben – ähnlich wie bei if-else-Anweisungen in Programmiersprachen.
Subqueries
Jetzt möchten wir wissen, welche Filme am längsten sind, inklusive Titel. Dafür nutzen wir die oben eingeführte WHERE-Klausel mit einer komplexeren Bedingung:
SELECT title, length
FROM film
WHERE length = (SELECT max(length)
FROM film)
Das ergibt:
Subqueries ermöglichen komplexe Abfragen. Im folgenden Beispiel suchen wir Filmtitel mit einer höheren Mietrate als der durchschnittlichen Mietrate aller Comedy-Filme:
SELECT title, rental_rate
FROM film
WHERE rental_rate >
(SELECT AVG(film.rental_rate) AS Average_Rating
FROM film
JOIN film_category AS cat_id
ON film.film_id=cat_id.film_id
JOIN category AS cat
ON cat_id.category_id=cat.category_id
GROUP BY cat.name, cat_id.category_id
HAVING cat.name = 'Comedy')
Das ergibt (hier nur die ersten 10 Zeilen):
Hier ermitteln wir zuerst die durchschnittliche Mietrate für Comedy-Filme. Der so gefundene Wert (in der Klammer oben) dient anschließend als Bedingung in der WHERE-Klausel, um die Filmtitel zu finden.
Common Table Expressions (CTEs)
In komplexeren Fällen nutzen wir Common Table Expressions (CTEs), um eine temporäre Ergebnistabelle zu speichern und darauf weiter zu arbeiten. Im Beispiel wollen wir wissen, wie viele Ausleihen jede Mitarbeiterin bzw. jeder Mitarbeiter pro Woche im Schnitt bearbeitet.
WITH weekly_rentals AS(
SELECT
staff_id,
DATE_PART('week', payment_date) AS week,
COUNT(rental_id) AS rental_numbers
FROM payment
GROUP BY staff_id, week)
SELECT staff_id, AVG(rental_numbers)
FROM weekly_rentals
GROUP BY staff_id
Das ergibt:
Wir speichern also die wöchentlichen Ausleihzahlen in einer temporären Tabelle 'weekly_rentals' und führen darauf eine weitere Abfrage aus, um das Endergebnis zu erhalten.
Window-Funktionen
Window-Funktionen sind ein weiterer wichtiger SQL-Baustein und heben dich aufs nächste Level. Auch wenn sie eher als fortgeschritten gelten, sind sie für viele Data Analysts und Data Scientists essenziell. Es lohnt sich, sie kennenzulernen.
OVER( ) & PARTITION BY. Angenommen, wir möchten für jedes Teammitglied die wöchentlichen Ausleihzahlen sowie die Gesamtsumme aller Ausleihen ausgeben – zusammen mit Staff-ID und Wochennummer. Wir können die oben erzeugte CTE wiederverwenden:
WITH weekly_rentals AS(
SELECT
staff_id,
DATE_PART('week', payment_date) AS week,
COUNT(rental_id) AS rental_numbers
FROM payment
GROUP BY staff_id, week)
SELECT *, SUM(rental_numbers) OVER() AS total_rentals
FROM weekly_rentals
Das ergibt (hier nur die ersten 10 Zeilen):
OVER kennzeichnet die Window-Funktion. Hier wenden wir die Funktion (SUM) auf alle Zeilen an. Mit PARTITION BY kannst du Window-Funktionen nach definierten "Partitionen" berechnen. Jetzt möchten wir statt der Gesamtsumme die Summe je Woche ausgeben (zusätzlich zu den anderen Spalten). Wieder nutzen wir die zuvor erzeugte CTE, wie vorher.
WITH weekly_rentals AS(
SELECT
staff_id,
DATE_PART('week', payment_date) AS week,
COUNT(rental_id) AS rental_numbers
FROM payment
GROUP BY staff_id, week)
SELECT *, SUM(rental_numbers) OVER(PARTITION BY week) AS total_weekly_rentals
FROM weekly_rentals
Das ergibt (hier nur die ersten 10 Zeilen):
Die zusätzliche Spalte enthält nun die wöchentlichen Gesamtsummen statt einer laufenden Gesamtsumme.
Wir haben hier nur ein paar einfache, aber wichtige Beispiele für Window-Funktionen gezeigt, die in vielen Unternehmen zum Handwerkszeug von Data Analysts und Data Scientists gehören. Nutze die verlinkten Ressourcen, um tiefer einzusteigen. Alternativ kannst du mit einer Open-Source-Datenbank üben und diese Befehle an eigenen Fragestellungen ausprobieren. Oder frische dein Wissen im Intermediate SQL course auf.