• Was sind Aggregatfunktionen?
• Was sind Fensterfunktionen?
• Ausführungsreihenfolge

Was sind Aggregatfunktionen?

Aggregatfunktionen verarbeiten mehrere Werte, um im Anschluss einen einzelnen Wert zurückzugeben. Beispiele von Aggregatfunktionen in SQL sind:

• AVG() – Gibt den Durchschnittswert der ausgewählten Spalte zurück.
• SUM() – Errechnet die Summe einer Spalte.
• MAX() – Gibt den Maximalwert zurück.
• MIN() – Gibt den Minimalwert zurück.
• COUNT() – Gibt die Anzahl an Werten zurück.

Zusammen mit GROUP BY kann dann die Ergebnismenge gruppiert werden. Gehen wir dafür von der folgenden Beispiel-Tabelle aus:

Tabelle selbst erstellen:

CREATE TABLE IF NOT EXISTS `playerTable` (
  `id` int(6) unsigned NOT NULL,
  `date` date NOT NULL,
  `player` varchar(200) NOT NULL,
  `score` int(6) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `playerTable` (`id`, `date`, `player`, `score`) VALUES
  ('1', '2023-01-01', 'Max', 28),
  ('2', '2023-01-01', 'Tom', 35),
  ('3', '2023-01-02', 'Max', 12),
  ('4', '2023-01-02', 'Tom', 24),
  ('5', '2023-01-02', 'Tom', 33),
  ('6', '2023-01-03', 'Max', 35),
  ('7', '2023-01-03', 'Max', 32),
  ('8', '2023-01-03', 'Tom', 18),
  ('9', '2023-01-04', 'Tom', 25),
  ('10', '2023-01-05', 'Max', 39);

CREATE TABLE playerTable (
  id int PRIMARY KEY,
  date date NOT NULL,
  player varchar(200) NOT NULL,
  score int NOT NULL
);

INSERT INTO playerTable (id, date, player, score) VALUES
  ('1', '2023-01-01', 'Max', 28),
  ('2', '2023-01-01', 'Tom', 35),
  ('3', '2023-01-02', 'Max', 12),
  ('4', '2023-01-02', 'Tom', 24),
  ('5', '2023-01-02', 'Tom', 33),
  ('6', '2023-01-03', 'Max', 35),
  ('7', '2023-01-03', 'Max', 32),
  ('8', '2023-01-03', 'Tom', 18),
  ('9', '2023-01-04', 'Tom', 25),
  ('10', '2023-01-05', 'Max', 39);

Wenn wir nun den durchschnittlichen Score je Spieler und Datum berechnen möchten, können wir das wie folgt tun:

select
  date,
  player,
  avg(score) as avg_score
from
  playerTable
group by
  date,
  player

Als Ergebnis bekommen wir:

Wir sehen, dass wir den durchschnittlichen Score je Spieler und Datum zurückbekommen. Was aber, wenn wir neben dem durchschnittlichen Score auch den einzelnen Score eines jeden Spielers und Tages in der Ergebnis-Tabelle haben wollen?

Man könnte jetzt meinen, dass wir einfach zu unserer Query die entsprechende Spalte hinzufügen können wie hier:

select
  date,
  player,
  score,
  avg(score) as avg_score
from
  playerTable
group by
  date,
  player

Wir würden dann folgendes Ergebnis bekommen:

Leider ist das nicht richtig. Wieso? Sehen wir uns die IDs 4 und 5 aus unserer Original-Tabelle an, dann sehen wir, dass Tom an einem Tag (2023-01-02) zwei Scores hatte: 24 und 33. In unserer Ergebnis-Tabelle sehen wir jedoch an diesem Tag nur einmal Tom mit dem Score 24. Der andere einzelne Score von 33 fehlt.

Der Grund ist wie SQL hier die Statements ausführt. Die Reihenfolge ist:

1. FROM: Zuerst wird die Tabelle gewählt.
2. GROUP BY: Dann folgt die Gruppierung. Sprich, einzelne Zeilen werden hier schon zusammengeführt.
3. SELECT: Erst dann folgt der Select. Es werden Daten aus der schon gruppierten Tabelle gewählt – und genau hier fehlen dann die einzelnen Werte schon.

SQL holt sich dann nur noch den ersten Wert aus der gruppierten Tabelle. Für Tom ist das die Zeile mit dem Score 24. Um unser Ziel zu erreichen, müssen wir anders vorgehen.

Was sind Fensterfunktionen?

Fensterfunktionen sind ähnlich wie die Aggregation, die man in der GROUP BY-Klausel durchführt (siehe Beispiel zur Aggregationsfunktion von oben). Der Große Unterschied zur Aggregationsfunktion ist jedoch, dass die Zeilen nicht zu einer einzigen Zeile gruppiert werden. Bei einer Fensterfunktion bleibt jede einzelne Zeile erhalten. Eine Fensterfunktion kann daher über mehrere Tabellenzeilen hinweg eine Berechnung, die in Beziehung zur aktuellen Zeile steht, durchführen. Oder andersrum: Für jede Zeile kann ein Abfrageergebnis dargestellt werden.

Hier ein visuelles Beispiel zur Verdeutlichung des Unterschiedes:

• Links kann man sehen, wie die GROUP BY-Klausel mehrere Zeilen zu einer Zeile gruppiert. Für die orange markierten Spalten haben wir in diesem Beispiel eine Summe berechnet. Die gruppierte Summe ist dann grün dargestellt.
• Rechts kann man sehen, dass die Fensterfunktion für jede Zeile ein Aggregationsergebnis zurückgeben kann, was die GROUP BY-Klausel nicht schafft (siehe Beispiel von oben).

Um den Unterschied mit einigen Daten darzustellen, nehmen wir ein sehr einfaches Beispiel. Siehe dir dazu die nachfolgende Grafik an.

• Rechts oben siehst du das Ergebnis der GROUP BY-Klausel. Hier wird die Tabelle auf drei Zeilen gruppiert.
• Rechts unten siehst du das Ergebnis mit einer Fensterfunktion. Dabei werden die Original-Zeilen beibehalten und zusätzlich kommt eine neue Spalte hinzu, die über die Original-Tabelle hinweg eine Kalkulation durchführt.

Wenn wir nun auf unser Beispiel von Anfang zurückkommen und neben dem durchschnittlichen Score auch den einzelnen Score eines jeden Spielers und Tages in der Ergebnis-Tabelle haben wollen, dann können wir das mit einer Fensterfunktion wie folgt erreichen:

select
  date,
  player,
  score,
  avg(score) over (partition by date, player) as avg_score
from
  playerTable

Hier nun unser korrektes Ergebnis:

An der Syntax der Fensterfunktion kann man sehen, dass diese anfangs wie die Aggregatfunktion beginnt. Zunächst wird eine der verschiedenen Fensterfunktionen genutzt (in diesem Beispiel eine Aggregatfunktion) inkl. der Spalte auf die die Fensterfunktion angewendet werden soll. “over” ist dann das Keyword über das mitgeteilt wird, dass eine Fensterfunktion angewendet wird. In Klammern folgt dann “partition by”. Dieser Ausdruck unterteilt die Zeilen in Partitionen (dazu gleich mehr). Auf diese Zeilen wird dann die Fensterfunktion angewendet. Hier müssen wir auch die Spalten spezifizieren nach denen wir partitionieren möchten.

Optional gäbe es noch die Möglichkeit ein “order by” für die Sortierung zu setzen und ein “rows”, um Zeilen einzuschränken.

In unserem Beispiel haben wir als Fensterfunktion die Aggregatfunktion “avg()” angewendet. Es können jedoch nicht nur Aggregatfunktionen genutzt werden. Bei Fensterfunktionen unterscheidet man zwischen zwei Arten:

• Aggregierte Fensterfunktionen
• Integrierte Fensterfunktionen

Mit aggregierten Fensterfunktionen lassen sich Aggregationen wie der Durschnitt, Maximalwerte, Maximalwerte, etc. berechnen. Beispiele sind:

• AVG()
• MAX()
• MIN()
• SUM()
• COUNT()

Die integrierten Fensterfunktionen werden eingesetzt, um Zeilen innerhalb der Partition zu ordnen oder auch zu vergleichen. Beispiele von integrierten Fensterfunktionen sind:

• RANK()
• ROW_NUMBER()
• DENSE_RANK()
• PERCENT_RANK()
• NTILE()
• LAG()
• LEAD()
• FIRST_VALUE()
• LAST_VALUE()
• NTH_VALUE()

Partitionen bei Fensterfunktionen

Um das Prinzip der Partitionen bei Fensterfunktionen besser zu verstehen, gibt es an dieser Stelle ein Beispiel. Gehen wir von folgender Tabelle aus:

Tabelle selbst erstellen:

CREATE TABLE p (
  id int PRIMARY KEY,
  name varchar(200) NOT NULL,
  ort varchar(200) NOT NULL,
  jahr int NOT NULL,
  kosten int NOT NULL,
  gueltig boolean NOT NULL
);

INSERT INTO p (id, name, ort, jahr, kosten, gueltig) VALUES
  ('1', 'max', 'berlin', 2022, 25, true),
  ('2', 'tom', 'berlin', 2022, 15, true),
  ('3', 'lisa', 'berlin', 2022, 55, true),
  ('4', 'max', 'hamburg', 2022, 95, false),
  ('5', 'tom', 'hamburg', 2022, 32, true),
  ('6', 'lisa', 'hamburg', 2022, 25, false),
  ('7', 'stefan', 'hamburg', 2022, 30, true),
  ('8', 'tom', 'berlin', 2021, 19, true),
  ('9', 'lisa', 'berlin', 2021, 68, true),
  ('10', 'stefan', 'hamburg', 2021, 45, false),
  ('11', 'max', 'hamburg', 2021, 86, true),
  ('12', 'tom', 'hamburg', 2021, 25, false),
  ('13', 'lisa', 'hamburg', 2021, 12, true)

Auf Basis dieser Tabelle möchten wir nun folgendes abfragen:

• Den Namen der Person
• Den Ort
• Das Jahr
• Die Kosten
• Die durchschnittlichen Kosten je Ort
• Die durchschnittlichen Kosten je Ort und Jahr

Das Ergebnis bekommen wir mit folgender Abfrage:

select
  name,
  ort,
  jahr,
  kosten,
  avg(kosten) over (partition by ort) as avg_kosten_ort,
  avg(kosten) over (partition by ort, jahr) as avg_kosten_ort_jahr
from
  p

Hier nochmal das Ergebnis:

Um die zwei Spalten “avg_kosten_ort” und “avg_kosten_ort_jahr” zu verstehen, müssen wir uns ansehen wie dabei eine Fensterfunktion vorgeht. Beginnen wir mit “avg_kosten_ort”. In unserer Tabelle können wir sehen, dass wir zwei einzigartige Orte haben: “berlin” und “hamburg”. Und für diese zwei Orte erstellt die Fensterfunktion nun ein “Fenster”. Dieses Fenster geben wir mit folgender Anweisung an:

...over (partition by ort)...

Visuell gesehen nimmt sich also die Fensterfunktion einmal alle Einträge von “berlin” und einmal alle Einträge von “hamburg” aus der Tabelle:

Nun wird für jedes dieser Fenster mit…

avg(kosten)...

…ein Durchschnitt berechnet:

Für die Berechnung von “avg_kosten_ort_jahr” geht SQL genau so vor, jedoch haben wir es hier mit 4 Fenstern zu tun, weil wir zwei Orte und zwei Jahre haben:

• berlin 2021
• berlin 2022
• hamburg 2021
• hamburg 2022

Sprich, SQL erstellt zunächst über die Anweisung…

...over (partition by ort, jahr)...

…folgende Fenster:

Und nun wird wieder für jedes dieser Fenster mit…

avg(kosten)...

…ein Durchschnitt berechnet:

Ausführungsreihenfolge

Basierend auf der Ausführungsreihenfolge der SQL-Operationen sollte man berücksichtigen, dass Fensterfunktionen bspw. nicht in der HAVING-Klausel genutzt werden können. Behalte dabei folgende Reihenfolge im Hinterkopf:

1. FROM und JOIN
2. WHERE
3. GROUP BY
4. Aggregatfunktion
5. HAVING
6. Fensterfunktion
7. SELECT
8. DISTINCT
9. UNION, INTERSECT, EXCEPT
10. ORDER BY
11. OFFSET
12. LIMIT, FETCH, TOP

Aufgrund der Logik dieser Reihenfolge sind Fensterfunktionen nur im SELECT und ORDER BY erlaubt. Aggregatfunktionen können hingegen noch zusätzlich im HAVING genutzt werden.