Aggregatfunktionen in SQL

Berichte aus einem Datensatz zu erstellen, ist eine Kernkompetenz, wenn du mit Daten arbeitest. Am Ende willst du kritische Business-Fragen mit den dir vorliegenden Daten beantworten. Oft erscheinen diese Antworten als Diagramme in Reports. Manchmal werden aber auch tabellarische Auswertungen gebraucht. In beiden Fällen musst du die Daten häufig mit einfachen Berechnungen zusammenfassen. In SQL kannst du Daten mit Aggregatfunktionen zusammenfassen/aggregieren. Mit diesen Funktionen beantwortest du Fragen wie:

• Wie hoch ist der Maximalwert von some_column_from_the_table? oder
• Welche Minimalwerte hat some_column_from_the_table in Bezug auf another_column_from_the_table?

und viele weitere.

Legen wir los und führen erste Aggregationen durch.

Hinweis: Um diesem Tutorial zu folgen, solltest du einfache Abfragen in PostgreSQL schreiben können (das ist das verwendete RDBMS). Dieses Tutorial frischt dein Wissen auf.

Einrichtung der Datenbank

Richte zunächst eine PostgreSQL-Datenbank ein und spiele dieses Backup ein, das die Tabelle enthält, die du hier verwenden wirst. Wenn du lernen willst, wie man ein Backup in PostgreSQL wiederherstellt, folge dem ersten Abschnitt aus diesem Tutorial.

Wenn das Backup erfolgreich wiederhergestellt wurde, solltest du eine Tabelle namens international_debt in der Datenbank sehen (falls nicht vorhanden, musst du zuvor eine Datenbank anlegen). Schauen wir uns schnell die ersten Zeilen der Tabelle an (eine einfache SELECT-Abfrage reicht dafür aus):

Die Tabelle enthält Informationen zu den Schuldenstatistiken verschiedener Länder weltweit für das aktuelle Jahr in unterschiedlichen Kategorien (siehe die Spalten indicator_name und indicator_code). Die Spalte debt zeigt die Höhe der Schulden (in USD), die ein bestimmtes Land in einer spezifischen Kategorie hat. Die Daten stammen aus der Volkswirtschaft und werden häufig genutzt, um die wirtschaftliche Lage von Ländern zu analysieren. Erhoben wurden sie von der World Bank.

Da die Datenbank nun steht, führen wir ein paar einfache Abfragen aus, um die Daten besser kennenzulernen. Öffne pgAdmin und leg los.

Einfache Informationen, große Wirkung

Auf der Abbildung oben siehst du, dass es für ein Land viele Einträge gibt, allerdings für unterschiedliche Kategorien. Daraus ergibt sich schnell die Frage:

Welche Länder sind überhaupt in der Tabelle enthalten?

Wenn du einfach eine SELECT-Abfrage auf die Spalte country_name ausführst, bekommst du wegen der Duplikate keine sinnvolle Antwort. Nutze das Schlüsselwort DISTINCT, um das zu lösen.

select distinct country_name from international_debt;
Bereitgestellt von

Die Ausgabe sollte in etwa so aussehen:

Damit hast du eine valide Antwort auf die Frage. Eine letzte Frage, bevor wir zu Aggregatfunktionen übergehen:

Wie viele verschiedene Schuldenindikatoren gibt es in der Tabelle?

Die Abfrage dazu ist der vorherigen sehr ähnlich. Du musst nur den Spaltennamen austauschen. Mach daraus eine kleine Übung. Das Ergebnis sollte in etwa so aussehen:

Aggregatfunktionen

Starten wir mit einer Abfrage, die eine Aggregatfunktion nutzt, und steigern uns dann Schritt für Schritt. Dabei lernst du Syntax und typische Muster kennen, die du beim Einsatz von Aggregatfunktionen in SQL brauchst.

select sum(debt) from international_debt;
Bereitgestellt von

Und das Ergebnis:

Mit der Aggregatfunktion SUM() berechnest du die Summe über eine Spalte mit numerischen Werten. In der obigen Abfrage ermittelst du die gesamte ausstehende Schuldenlast aller in der Tabelle aufgeführten Länder.

Wichtig: SUM() berücksichtigt NULL-Werte bei der Summenbildung nicht. Als Nächstes klären wir die Frage:

Wie hoch ist der maximale Schuldenbetrag?

Hier hilft dir die Aggregatfunktion MAX():

select max(debt) from international_debt;
Bereitgestellt von

Die Antwort:

Wie SUM() ignoriert auch MAX() NULL-Einträge. Entsprechend gibt es auch MIN(). Teile mir gern im Comments-Bereich mit, wie hoch der Minimalwert der Spalte debt ist. Es ist zudem sinnvoll zu prüfen, ob es ungültige Einträge in debt gibt, um die bisherigen Ergebnisse abzusichern.

Hinweis: In den Beispielen werden die Funktionen kleingeschrieben verwendet.

Wenn du diese Abfrage ausführst: select * from international_debt where debt is null;, solltest du eine leere Ergebnismenge erhalten. Ermitteln wir nun die Gesamtzahl unterschiedlicher Länder in der Tabelle.

select count(distinct(country_name)) from international_debt;
Bereitgestellt von

Du siehst, es sind insgesamt 124 unterschiedliche Länder in der Tabelle vorhanden. Achte genau auf die Verschachtelung der Funktionen in der Abfrage. Ja, das ist erlaubt: Du kannst mehrere Aggregatfunktionen logisch miteinander kombinieren.

Angenommen, du willst nun den Durchschnittswert der Spalte debt sehen. Die Funktion dafür ist AVG():

select avg(debt) from international_debt;

Du erhältst den Wert 1306633214.966397971 (USD). Es ist sinnvoll, solche Ergebnisse mit aussagekräftigen Spaltennamen zu versehen. Wie du oben siehst, setzt PostgreSQL standardmäßig den Funktionsnamen als Spaltenname im Resultset. Gib den Spalten daher lieber passende Aliasse, zum Beispiel so:

select avg(debt) as Average_Debt_By_A_Country from international_debt;
Bereitgestellt von

Das Ergebnis lässt sich so deutlich besser lesen:

Gehen wir nun einen Schritt weiter. Um Fragen wie What are the minimum values of some_column_from_the_table with respect to another_column_from_the_table? zu beantworten, musst du eine Aggregatfunktion mit einer GROUP BY-Klausel kombinieren. So geht’s.

Aggregatfunktionen + GROUP BY + mehr

Angenommen, du willst einen Report erstellen, der zu jedem country_name die Summe der Schulden anzeigt. So könnte das aussehen:

Solche Reports sind in der Praxis sehr verbreitet. Welche Abfrage brauchst du dafür? Du musst SUM() auf debt anwenden und gleichzeitig den country_name mit der Schuldsumme ausgeben. Führe folgende Abfrage aus:

select country_name, sum(debt) from international_debt;
Bereitgestellt von

Erhältst du nicht folgenden Fehler?

ERROR: column "international_debt.country_name" must appear in the GROUP BY clause or be used in an aggregate function

Was bedeutet das? Wenn du eine Aggregatfunktion (wie SUM()) zusammen mit einer nicht aggregierten Spalte wie country_name nutzt, musst du diese nicht aggregierte Spalte in die GROUP BY-Klausel aufnehmen. Die korrekte Abfrage lautet:

select country_name, sum(debt) as total_debt from international_debt group by country_name;
Bereitgestellt von

Und das Ergebnis passt:

Hinweis auf das Alias in der Abfrage.

Nun willst du den Report nach total_debt absteigend sortieren. Erinnerst du dich an ORDER BY? Ja, auch das lässt sich mit Aggregatfunktionen kombinieren:

select country_name, sum(debt) as total_debt from international_debt
group by country_name order by total_debt desc;
Bereitgestellt von

Das Ergebnis ist nun absteigend sortiert:

Wichtig: Achte darauf, welche Spalte du in ORDER BY verwendest.

Noch eine wichtige Frage:

Wie hoch sind die maximalen Schulden in den verschiedenen Kategorien (absteigend sortiert)?

Dafür brauchst du MAX(). Die Abfrage dazu solltest du nun problemlos schreiben können.

select indicator_code, max(debt) as maximum_debt from international_debt
group by indicator_code order by maximum_debt desc;
Bereitgestellt von

Und du erhältst einen übersichtlichen Report:

Du kannst die Anzahl der Zeilen in solchen Reports auch begrenzen. Wenn du zum Beispiel nur die Top 5 Einträge möchtest, nutze die LIMIT-Klausel.

select indicator_code, max(debt) as maximum_debt from international_debt
group by indicator_code order by maximum_debt desc
limit 5;
Bereitgestellt von

Zum Abschluss dieses Tutorials soll der Report um Ländernamen ergänzt werden. Wie geht das? So könnte die Abfrage aussehen:

select country_name, indicator_code, max(debt) as maximum_debt from international_debt
group by country_name, indicator_code order by maximum_debt desc;
Bereitgestellt von

Noch ein guter Report:

In der Abfrage oben hast du die Spalte country_name in der SELECT-Liste ergänzt und sie auch in GROUP BY aufgenommen. Dieses Muster kannst du beliebig erweitern.

Die Reihenfolge von GROUP BY, ORDER BY und LIMIT ist beim Erstellen solcher Reports entscheidend. Vertauschst du sie, bekommst du Fehler. Probiere es aus:

select country_name, sum(debt) as total_debt from international_debt
order by total_debt desc group by country_name;
Bereitgestellt von

Und du erhältst:

ERROR:  syntax error at or near "group"
LINE 1: ... from international_debt order by total_debt desc group by c...Bereitgestellt von

In der Abfrage steht ORDER BY vor GROUP BY – das ist nicht zulässig. Das gilt übrigens auch ohne Aggregatfunktionen. Die korrekte Reihenfolge lautet: GROUP BY -> ORDER BY -> LIMIT. Merke dir das unbedingt.

Wie geht’s weiter?

Glückwunsch! Du hast dieses Tutorial abgeschlossen. Du hast die wichtigsten Aggregatfunktionen in PostgreSQL kennengelernt und gesehen, wie du damit aussagekräftige Reports erstellst. Das sind Schlüsselkompetenzen für Data Scientists. Wenn du deine SQL-Kompetenzen systematisch auf das nächste Level bringen willst, empfehlen wir dir diese DataCamp-Kurse:

• Joining Data in SQL
• Intermediate SQL
• SQL for Exploratory Data Analysis