Gruppieren von SQL-Abfragen kennen wir von Aggregatfunktionen
wie SUM() oder AVG(). Mit einem GROUP BY geben wir an, dass die Summe oder der
Mittelwert pro einer bestimmten
Spalte berechnet werden soll. Wollen wir aber auch Spalten ausgeben, die nicht
vom GROUP BY erfasst werden, wird es spannend. Dafür gibt es aber auch eine
einfache Lösung: OVER (PARTITION BY …).
„Normale“ Aggregatfunktionen
Aggregatfunktionen erlauben uns, Summen und Mittelwerte zu
berechnen, zu zählen oder den größten oder kleinsten Wert auszugeben.
Die am häufigsten verwendeten Aggregatfunktionen (aggregate
functions) sind AVG(), COUNT(), MAX(), MIN() und SUM().
Dabei können wir uns einen einzelnen Wert ausgeben lassen,
zum Beispiel einen Mittelwert, oder mehrere Werte, nämlich den Mittelwert pro einer bestimmten Spalte – oder noch
weitere Untergruppierungen (Partitionen) erstellen.
Einen einzelnen Wert ausgeben
Sehen wir uns das anhand der Northwind Beispieldatenbank an: Hier gibt es eine Tabelle Orders
(Bestellungen); darin scheinen die Frachtkosten (Freight) auf.
Wir können einen Mittelwert aller Frachtkosten berechnen:
Dann bekommen wir genau den einen Wert zurück…
… oder, viel wahrscheinlicher: wir können die mittleren
Frachtkosten pro … ausgeben.
Mittelwert pro…
Dazu verwenden wir GROUP BY. Wir gruppieren den Mittelwert zum
Beispiel pro Land, in das geliefert
wird (ShipCountry), oder pro Frächter (ShipVia), oder PRO Kunde (CustomerId).
Da es in der Northwind
Datenbank 3 Frächter gibt, bekommen wir hiermit 3 Treffer zurück und den
jeweiligen mittleren Frachtkostenwert pro Frächter (ShipVia).
Aggregatfunktionen und mehr als zwei Spalten
Wenn wir aber mehr als diese beiden zusammengehörigen Spalten
ausgeben wollen, wird es spannend.
In manchen Fällen kann es eventuell Sinn machen; wir wollen
zum Beispiel die mittleren Frachtkosten pro Kunde und Frächter:
Plötzlich haben wir statt der drei Ausgabezeilen 239; 91
Kunden, von denen 89 schon etwas bestellt haben und somit Frachtkosten
generiert haben, und manche Kunden, die nicht von allen drei Frachtunternehmen
beliefert worden sind.
Dadurch bekommen wir auch Mehrfachausgaben; die CustomerId steht so oft da, wie dieser Kunde
von unterschiedlichen Frächtern beliefert wurde, und die Frächter (ShipVia) werden bei jedem Kunden, den sie
beliefert haben, aufgeführt.
Auch Mehrfachausgaben mit Aggregatsfunktion und GROUP BY
können Sinn machen. Im Beispiel mit den Frächtern und Kunden könnten wir hier vergleichen,
welcher Frächter (ShipVia) im Schnitt
zu welchem Kunden (CustomerId) am
günstigsten liefert.
Wenn Mehrfachausgaben bei Aggregatsfunktionen zum Problem werden
Was aber, wenn wir mehr Informationen in der Ausgabetabelle
wollen? Beispielsweise möchten wir auch noch die OrderId (Bestellnummer) ausgeben:
Dadurch bekommen wir 830 Zeilen zurück – also alle Bestellungen aus der Orders-Tabelle. Die Datenmenge wäre noch
längst kein Problem – aber jetzt haben wir keinen Mittelwert mehr, denn die
durchschnittlichen Frachtkosten pro Bestellung sind einfach die Frachtkosten.
Wir bekommen also das gleiche Ergebnis, wie wenn wir gar kein AVG() abgefragt
hätten.
Untergruppierungen – OVER PARTITION BY
Wenn wir trotzdem einen Mittelwert ausgegeben haben wollen,
können wir das mit Hilfe einer Untergruppierung lösen.
Dabei verzichten wir auf das GROUP BY und verwenden
stattdessen direkt nach der Aggregatfunktion ein OVER:
Im OVER erstellen wir eine Partition, eine Untergruppierung,
in der wir jetzt angeben, pro welcher
Spalte wir die mittleren Frachtkosten berechnet haben wollen.
Wir erhalten wieder 830 Ausgabezeilen (so viele, wie
Bestellungen, OrderIds vorhanden
sind). Mit OVER PARTITION BY erhalten wir aber so auch die mittleren
Frachtkosten pro Frächter (ShipVia).
Für unser Beispiel bedeutet das, dass in jeder Ausgabezeile
auch der mittlere Frachtkostenwert pro Frächter (ShipVia) dabeisteht. Da es insgesamt nur drei Frächter gibt, sind
das jetzt drei verschiedene Werte, die sich entsprechend oft wiederholen.
Wollen wir einen mittleren Frachtkostenwert pro Kunde und Frächter,
können wir auch das im OVER PARTITION BY angeben:
Somit haben wir wieder ein ähnliches Ergebnis wie im Beispiel
weiter oben mit GROUP BY, nur, dass wir hier eben auch weitere Spalten angeben
können (OrderId), die jetzt nicht
mehr an die Untergruppierung im PARTITION BY gekoppelt sind.
In Zeile 1 und 6 beispielsweise entspricht der mittlere
Frachtkostenwert (AvgFreight) wieder
genau dem Frachtkostenwert (Freight) –
diesmal ist das kein Fehler; Kunde ALFKI wurde eben nur einmal von Frächter #2
und einmal von Frächter #3 beliefert. Überall, wo ein Frächter einen Kunden
mehr als einmal beliefert hat, steht in der Spalte AvgFreight nun der mittlere Frachtkostenwert dieses Kunden (CustomerId) beim entsprechenden Frächter
(ShipVia).
Anwendungsfälle
OVER PARTITION BY ist überall dort praktisch, wo Mehrfachausgaben
eines Wertes erlaubt sind, mehr Spalten als nur die Wert pro Spalte X in der Ausgabe gefragt sind und Aggregatfunktionen
zum Einsatz kommen.
So lässt sich zum Beispiel der Gehaltsdurchschnitt pro
Abteilung ausgeben, oder der Notendurchschnitt pro Klasse in einem größeren
Kontext.
Auch für mehrere Aggregatfunktionen in einem SELECT Statement
ist OVER PARTITION BY empfehlenswert.
So könnten wir in unserem Beispiel etwa die mittleren
Frachtkosten pro Kunde und Frächter, die Frachtkostensumme pro Kunde und den
höchsten Frachtkostenwert pro Kunde und Frächter, sowie beliebig viele andere
Spalten aus der Orders-Tabelle ohne
GROUP BY ausgeben:
Ergebnis:
Ich hoffe, Ihr habt ein wenig Appetit bekommen, Euch weiter
mit SQL auseinanderzusetzen, und vielleicht sehen wir uns ja einmal in einem
unserer Kurse zum Thema SQL!