SQL: Gruppieren und Verbinden

1. Vorbereitung

Diese Aktivität setzt die Einführung in SQL fort: SELECT, WHERE, ORDER BY, LIMIT und COUNT solltest du kennen. Öffne den Ordner sql-einfuehrung aus der Einführung mit StrgK StrgO; gibt es ihn noch nicht, legt VS Code ihn an. Öffne dann ein Terminal und maximiere es, dann passen auch längere Ergebnisse hinein.

2. Die ganze Datenbank laden

Lade die ganze Fassung der Länder der Welt herunter und importiere sie in deine Datenbank:

wget https://github.com/specht/workspace-files/raw/main/country-archive/countries.sql

mysql < countries.sql

countries.sql legt sieben Tabellen an. Hast du in der Einführung die kleine Fassung geladen, ersetzt es deren drei Tabellen: Die Länder, Städte und Kontinente sind dieselben, deine Abfragen von dort gehen weiter. Starte mycli wieder und sieh dir die Tabellen an:

Neu in country sind vier Spalten: currency_code, die Währung, ein Fremdschlüssel auf currency; tld, die Internet-Endung wie .de; calling_code, die Vorwahl wie +49; und driving_side, left oder right, je nachdem, auf welcher Straßenseite gefahren wird. border enthält die Grenzen an Land, jede als Paar von zwei Ländern; erst beide Spalten zusammen sind der Primärschlüssel. language und country_language sagen, welche Sprachen in welchem Land Amtssprache sind.

3. Gruppen bilden: GROUP BY

COUNT(*) hat bisher alle Zeilen gezählt, die übrig blieben. Mit GROUP BY teilt die Datenbank die Zeilen vorher in Gruppen ein und rechnet für jede Gruppe einmal. In wie vielen Ländern wird rechts gefahren, in wie vielen links?

SELECT driving_side, COUNT(*)
FROM country
GROUP BY driving_side;

Jede Gruppe wird zu einer Zeile. Neben COUNT gibt es SUM (die Summe), AVG (den Durchschnitt), MIN und MAX. Wie viele Städte jedes Land in city hat und wie viele Menschen darin wohnen, die fünf Länder mit den meisten zuerst:

SELECT country_code, COUNT(*) AS cities, SUM(population) AS people
FROM city
GROUP BY country_code
ORDER BY people DESC
LIMIT 5;

Hinter SELECT dürfen bei GROUP BY nur die Spalten stehen, nach denen gruppiert wird, und Funktionen wie COUNT oder SUM: Welchen name sollte die Datenbank für China auch zeigen, es sind 309 Städte? Gruppen filtern kannst du mit HAVING; anders als WHERE, das einzelne Zeilen prüft, kommt es nach dem Gruppieren. Die Länder mit mehr als 200 Städten:

SELECT country_code, COUNT(*) AS cities
FROM city
GROUP BY country_code
HAVING cities > 200;

Wie die Gruppen entstehen, zeigt der Query Stepper: Er färbt bei GROUP BY jede Gruppe ein, und HAVING streicht ganze Gruppen.

4. Tabellen verbinden: JOIN

In country steht nicht, wie die Hauptstadt eines Landes heißt; das steht in city, bei der Stadt mit is_capital = 1. Ein JOIN setzt die beiden Tabellen zusammen: Zu jeder Zeile von country holt er die Zeilen von city, für die die Bedingung hinter ON stimmt, hier die Städte des Landes.

SELECT country.name, city.name
FROM country
JOIN city ON city.country_code = country.code
WHERE city.is_capital = 1 AND country.continent = 'Europa'
  AND country.population > 50000000;

Weil es name in beiden Tabellen gibt, steht der Name der Tabelle davor: country.name, city.name. Die Bedingung hinter ON ist fast immer dieselbe Art: Ein Fremdschlüssel ist gleich dem Primärschlüssel, auf den er verweist. So kommt auch der Name der Währung dazu, über country.currency_code und currency.code.

Wie ein JOIN arbeitet, zeigt der Query Stepper Schritt für Schritt: Nach FROM stehen alle Länder da, nach JOIN ist jedes Land so oft da, wie es Städte hat, und erst WHERE lässt die Hauptstädte übrig.

Die Nachbarn: dieselbe Tabelle zweimal

In border stehen nur Kürzel. Für die Namen der Nachbarn verbindest du border mit country, und zwar über neighbor_code. Bekommt eine Tabelle mit AS einen eigenen Namen, liest sich das besser:

SELECT neighbor.name
FROM border
JOIN country AS neighbor ON neighbor.code = border.neighbor_code
WHERE border.country_code = 'DEU';

Jede Grenze steht in border zweimal, einmal in jeder Richtung: ('DEU', 'FRA') und ('FRA', 'DEU'). Das ist Absicht, sonst müsste die Abfrage in beiden Spalten nach Deutschland suchen.

Auch ohne Partner: LEFT JOIN

Ein JOIN lässt ein Land weg, für das es keine passende Zeile gibt: Inselstaaten haben keine Nachbarn und tauchen bei einem JOIN mit border gar nicht auf. Ein LEFT JOIN behält jede Zeile der linken Tabelle und füllt die Spalten der rechten mit NULL, wenn es keinen Partner gibt:

SELECT country.name, border.neighbor_code
FROM country
LEFT JOIN border ON border.country_code = country.code
WHERE country.continent = 'Ozeanien' AND country.population > 1000000;

Australien und Neuseeland bleiben im Ergebnis, mit NULL; Papua-Neuguinea hat einen Nachbarn, Indonesien auf derselben Insel. Mit WHERE border.country_code IS NULL bleiben die Länder ohne Nachbarn übrig.

5. Viele zu viele: Amtssprachen

Ein Land kann mehrere Amtssprachen haben, Singapur etwa gleich vier, und eine Sprache kann in vielen Ländern Amtssprache sein. Das passt weder in eine Spalte von country noch in eine von language. Deshalb gibt es eine Tabelle dazwischen, country_language, mit einer Zeile für jedes Paar aus Land und Sprache. Für die Namen braucht es zwei JOINs:

SELECT country.name, language.name
FROM country
JOIN country_language ON country_language.country_code = country.code
JOIN language ON language.code = country_language.language_code
WHERE country.name = 'Singapur';

Eine solche Tabelle in der Mitte brauchst du immer, wenn auf beiden Seiten viele stehen: bei Filmen und Genres in der Filmdatenbank genauso.

6. Wie weit ist es? Entfernungen

Jede Stadt hat in latitude und longitude ihre Lage auf der Erde, Breite und Länge in Grad. Daraus rechnet MySQL die Entfernung zweier Städte aus. POINT(x, y) macht aus zwei Zahlen einen Punkt, und auf der Erde kommt die Länge zuerst, wie auf einer Karte x vor y. ST_Distance_Sphere gibt die Entfernung zweier Punkte auf einer Kugel, so groß wie die Erde, in Metern: die Luftlinie.

Für zwei Städte braucht die Abfrage die Tabelle city zweimal, wie country bei den Nachbarn. CROSS JOIN setzt jede Zeile der einen mit jeder Zeile der anderen zusammen, und WHERE sucht das eine Paar heraus:

SELECT a.name, b.name,
    ST_Distance_Sphere(POINT(a.longitude, a.latitude), POINT(b.longitude, b.latitude)) / 1000 AS km
FROM city AS a
CROSS JOIN city AS b
WHERE a.name = 'Berlin' AND b.name = 'Paris';

Berlin und Paris liegen 876 Kilometer auseinander. Für die Städte in der Nähe von Berlin ist a Berlin und b jede andere Stadt; ON b.id <> a.id lässt das Paar von Berlin mit sich selbst weg. Sortiert nach der Entfernung stehen die nächsten oben:

SELECT b.name, ROUND(ST_Distance_Sphere(POINT(a.longitude, a.latitude), POINT(b.longitude, b.latitude)) / 1000) AS km
FROM city AS a
JOIN city AS b ON b.id <> a.id
WHERE a.name = 'Berlin'
ORDER BY km
LIMIT 5;

Braucht das eine besondere Datenbank?

Für diese Abfragen rechnet MySQL die Entfernung von Berlin zu jeder der 4.748 Städte einmal aus und sortiert. Das dauert ein paar Millisekunden. Eine Karten-App hat aber Millionen Orte, Haltestellen, Restaurants, Ladesäulen, und Tausende fragen in jeder Sekunde: »Was ist in meiner Nähe?« Jedes Mal alle Orte durchzurechnen, wäre zu langsam.

Dafür bekommt die Lage eine eigene Spalte vom Typ POINT und einen SPATIAL INDEX. Der teilt die Karte in Rechtecke ein, große, die kleinere enthalten, und die wieder kleinere (ein R-Baum). Für eine Frage nach der Nähe sieht die Datenbank nur in den Rechtecken nach, die nah genug liegen, und lässt den Rest der Welt aus. Dieselbe Idee steckt in Datenstrukturen wie dem k-d-Baum, der den Raum abwechselnd längs und quer teilt. Für unsere paar Tausend Städte lohnt sich das nicht: Gerechnet ist hier schneller als nachgeschlagen.

7. Zusammenfassung

Im Handbuch: