Partitionen in Window Functions

Im vorigen Artikel haben wir bereits kurz erwähnt, was Partitionen sind und wie man sie in Window Functions verwendet. Zeit, sie genauer zu betrachten 🤓.

Was ist eine Partition?

Partitionen sind Teilmengen von Zeilen, die für eine Window Function auf Basis einer oder mehrerer Spalten gebildet werden.

Sie dienen dazu, Daten zu segmentieren und erlauben so eine genauere Analyse sowie Berechnungen wie Aggregation oder Ranking innerhalb jeder Gruppe.

Wenn wir zum Beispiel eine Tabelle mit Mietpreisen nach dem Unterkunftstyp partitionieren, können wir in einer separaten Spalte etwa den Durchschnittspreis pro Unterkunftstyp berechnen.

Partitionen in SQL anwenden

Um eine Partition zusammen mit einer Window Function zu verwenden, hältst du dich an folgende Syntax:

MySQL 8.1
SELECT <window_function>(<tabellen_feld>)
OVER (
    PARTITION BY <spalten_zum_partitionieren>
)

Beispiel

Schauen wir uns die Verwendung einer Partition mit einer Window Function an einem einfachen Beispiel an.

Wir betrachten die Tabelle Rooms, konkret die Felder home_type und price:

MySQL 8.1
SELECT home_type, price FROM Rooms;

Wir sehen, dass alle Mietangebote in 3 Kategorien aufgeteilt sind: „Private room“, „Entire home/apt“ und „Shared room“.

Jede Kategorie hat ihren eigenen Preisrahmen. Um den Durchschnittspreis innerhalb einer Kategorie zu ermitteln und ihn mit dem aktuellen Preis zu vergleichen, kommen Window Functions wie gerufen.

Dazu ergänzen wir unsere Ergebnistabelle um eine weitere Spalte avg_price, die den Durchschnittspreis pro Kategorie berechnet. Das sieht so aus:

MySQL 8.1
SELECT
    home_type, price,
    AVG(price) OVER (PARTITION BY home_type) AS avg_price
FROM Rooms

Was passiert in der hinzugefügten Zeile?

• PARTITION BY home_type teilt alle Datensätze in verschiedene Partitionen auf Basis der eindeutigen Werte der Spalte home_type auf.
• Anschließend berechnet AVG(price) für jeden Datensatz den Durchschnittspreis (price) innerhalb seiner Partition (home_type).

Das Ergebnis dieses Teils der Query ist die Spalte avg_price, in der für jeden Datensatz der Durchschnittspreis seiner Unterkunftskategorie (home_type) steht.

Partitionen über mehrere Spalten

Die Partitionierung kann auch über mehrere Spalten erfolgen. Das ermöglicht komplexere und genauere Segmente für die Analyse.

Für unsere Tabelle Rooms können wir zum Beispiel Partitionen auf Basis von 2 Spalten bilden: der Unterkunftskategorie home_type und der Verfügbarkeit eines Fernsehers has_tv.

Beispiel-Query mit Partitionierung nach zwei Spalten:

MySQL 8.1
SELECT
    home_type, has_tv, price,
    AVG(price) OVER (PARTITION BY home_type, has_tv) AS avg_price
    FROM Rooms

Hier bildet PARTITION BY home_type, has_tv eindeutige Partitionen für jede Kombination aus home_type und has_tv und erlaubt es, den Durchschnittspreis innerhalb einer Kategorie für Unterkünfte mit bzw. ohne Fernseher zu berechnen.