Daten zusammenfassen mit Aggregatsfunktionen

Lernziele

• Sie können Daten mit Aggregatsfunktionen wie COUNT, AVG, SUM, MIN und MAX zusammenfassen und auswerten.
• Sie können Datensätze anhand bestimmter Kriterien mit GROUP BY gruppieren.
• Sie verstehen den Unterschied zwischen WHERE und HAVING und können letzteres nutzen, um Gruppen zu filtern.

Im letzten Kapitel haben Sie gelernt, einzelne Zeilen aus der Datenbank abzufragen. Oft möchten Sie aber nicht einzelne Datensätze sehen, sondern zusammengefasste Informationen: Wie viele Songs gibt es? Wie hoch ist die durchschnittliche Tanzbarkeit? Welches Genre hat die meiste Energie? Genau dafür gibt es Aggregatsfunktionen.

Was sind Aggregatsfunktionen?

Aggregatsfunktionen fassen mehrere Zeilen zu einem einzigen Ergebnis zusammen. Statt einzelne Songs zu sehen, erhalten Sie z.B. die Gesamtzahl, den Durchschnitt oder den höchsten Wert.

Die wichtigsten Aggregatsfunktionen

COUNT – Zeilen zählen

Mit COUNT(*) können Sie die Anzahl der Zeilen in einer Tabelle zählen.

Alle Songs zählen

Mit WHERE kombinieren

Aggregatsfunktionen lassen sich mit WHERE kombinieren, um nur bestimmte Zeilen zu zählen:

AVG, MIN, MAX – Durchschnitt und Extremwerte

Durchschnittliche Tanzbarkeit

Spalten umbenennen mit AS

Mit AS können Sie einer berechneten Spalte einen eigenen Namen geben. Das macht die Ergebnisse lesbarer. Ohne AS würde die Spalte einfach ROUND(AVG(danceability), 3) heissen.

Runden mit ROUND

ROUND(wert, 3) rundet auf 3 Nachkommastellen. Das macht die Ergebnisse übersichtlicher.

Kürzester und längster Song

Mehrere Aggregatsfunktionen kombinieren

Sie können mehrere Aggregatsfunktionen in einer Abfrage verwenden:

SUM – Werte summieren

Mit SUM können Sie Werte aufsummieren:

Ergebnis

Über 7'200 Stunden Musik – das sind etwa 300 Tage nonstop!

GROUP BY – nach Gruppen zusammenfassen

Die wahre Stärke von Aggregatsfunktionen zeigt sich erst mit GROUP BY. Damit können Sie Daten nach einer Spalte gruppieren und für jede Gruppe separate Ergebnisse berechnen.

Grundsyntax

SELECT spalte, AGGREGATSFUNKTION(andere_spalte)
FROM tabelle
GROUP BY spalte;

Beispiel: Songs pro Genre zählen

Gleichmässige Verteilung

Sie werden feststellen, dass jedes Genre genau 1'000 Songs enthält. Der Datensatz wurde absichtlich so zusammengestellt. In der Realität wären die Zahlen natürlich unterschiedlich.

Welches Genre ist am tanzbarsten?

Jetzt können wir die grosse Frage beantworten:

Die tanzbarsten Genres

An der Spitze stehen Kids-Musik, Chicago House und Reggaeton – alles Genres mit starkem, regelmässigem Beat. Am anderen Ende der Skala finden Sie Sleep, Ambient und New-Age-Musik mit sehr tiefer Tanzbarkeit.

Welches Genre ist am traurigsten?

Der Wert valence misst die Fröhlichkeit eines Songs (0.0 = traurig, 1.0 = fröhlich):

Welches Genre hat die meiste Energie?

Death Metal an der Spitze

Wenig überraschend: Death Metal, Grindcore und Metalcore sind die energiereichsten Genres. Am anderen Ende stehen Sleep und Ambient.

HAVING – Gruppen filtern

Mit HAVING können Sie Gruppen nach dem Aggregieren filtern. Der Unterschied zu WHERE:

• WHERE filtert einzelne Zeilen vor dem Gruppieren
• HAVING filtert Gruppen nach dem Gruppieren

Grundsyntax

SELECT spalte, AGGREGATSFUNKTION(andere_spalte)
FROM tabelle
GROUP BY spalte
HAVING bedingung;

Beispiel: Nur populäre Genres

WHERE und HAVING kombinieren

Sie können WHERE und HAVING in derselben Abfrage verwenden:

Reihenfolge der Klauseln

SQL-Abfragen folgen immer dieser Reihenfolge:

1. SELECT – Was soll angezeigt werden?
2. FROM – Aus welcher Tabelle?
3. WHERE – Welche Zeilen filtern? (vor Gruppierung)
4. GROUP BY – Wonach gruppieren?
5. HAVING – Welche Gruppen filtern? (nach Gruppierung)
6. ORDER BY – Wie sortieren?
7. LIMIT – Wie viele Ergebnisse?

COUNT(DISTINCT) – Einzigartige Werte zählen

Mit COUNT(DISTINCT spalte) zählen Sie nur die unterschiedlichen Werte einer Spalte:

Vorsicht bei «Künstler zählen»

Die Zahl der «verschiedenen Künstler» ist ungenau, weil Kollaborationen als ein Eintrag gezählt werden. Ingrid Michaelson;ZAYN wird als ein Künstler gezählt, nicht als zwei. Auch hier zeigt sich wieder die Schwäche der flachen Tabellenstruktur.

Aufgaben

Aufgabe 1: Songs zählen

Wie viele Songs im Genre rock gibt es in der Datenbank?

Aufgabe 2: Durchschnittliches Tempo

Berechnen Sie das durchschnittliche Tempo (tempo) aller Songs. Runden Sie auf eine Nachkommastelle.

Aufgabe 3: Künstler mit den meisten Songs

Finden Sie die 10 Künstler mit den meisten Songs in der Datenbank. Zeigen Sie den Künstlernamen und die Anzahl an.

Aufgabe 4: Schnellstes Genre

Welche 10 Genres haben das höchste durchschnittliche Tempo? Zeigen Sie Genre und durchschnittliches Tempo (gerundet auf 1 Nachkommastelle) an.

Aufgabe 5: Fröhliche Genres finden

Welche Genres haben eine durchschnittliche Fröhlichkeit (valence) von mehr als 0.6? Zeigen Sie Genre und durchschnittliche Valence (gerundet auf 3 Nachkommastellen) an, sortiert nach Valence absteigend.

Aufgabe 6: Genre-Profil erstellen

Erstellen Sie ein «Profil» des Genres classical: Zeigen Sie die durchschnittliche Energie, Tanzbarkeit, Akustik (acousticness), Instrumentalität (instrumentalness) und Fröhlichkeit an. Runden Sie alle Werte auf 3 Nachkommastellen.

Aufgabe 7: Genres vergleichen

Vergleichen Sie die durchschnittliche Energie und Tanzbarkeit der Genres pop, rock, classical und hip-hop. Zeigen Sie Genre, durchschnittliche Energie und Tanzbarkeit an (gerundet auf 3 Stellen), sortiert nach Energie absteigend.

Hinweis

Mit WHERE spalte IN ('wert1', 'wert2', 'wert3') können Sie nach mehreren Werten gleichzeitig filtern.

Zusammenfassung

Was Sie gelernt haben

• Mit COUNT, AVG, SUM, MIN, MAX fassen Sie Daten zusammen
• Mit GROUP BY gruppieren Sie Daten und berechnen Aggregate pro Gruppe
• Mit HAVING filtern Sie Gruppen nach dem Aggregieren
• Mit COUNT(DISTINCT) zählen Sie einzigartige Werte
• Mit ROUND runden Sie Ergebnisse auf eine bestimmte Anzahl Nachkommastellen
• Mit AS geben Sie berechneten Spalten lesbare Namen
• Die Reihenfolge ist immer: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT