PARTITION BY in SQL: Einführung in Fensterfunktionen und Anwendungsbeispiele

Die PARTITION BY Klausel in SQL ist Teil der Fensterfunktionen, die es ermöglichen, eine Ergebnismenge in separate „Fenster“ oder „Partitionen“ zu unterteilen und dann eine Aggregatfunktion wie SUM(), AVG() oder ROW_NUMBER() auf jede dieser Partitionen anzuwenden.

Beispiel 1: Einfache Verwendung von `PARTITION BY`

Nehmen wir an, Sie haben eine Tabelle mitarbeiter mit den Spalten id, name, abteilung und gehalt.

SELECT id, name, abteilung, gehalt,
AVG(gehalt) OVER(PARTITION BY abteilung) AS durchschnittliches_gehalt
FROM mitarbeiter;

Erklärung:

Die Abfrage gibt die Spalten id, name, abteilung und gehalt für jeden Mitarbeiter zurück.
Die AVG(gehalt) OVER(PARTITION BY abteilung) Klausel berechnet das durchschnittliche Gehalt für jede Abteilung und gibt diesen Wert für jeden Mitarbeiter in der entsprechenden Abteilung zurück.

Beispiel 2: Verwendung von `PARTITION BY` mit `ROW_NUMBER()`

SELECT id, name, abteilung, gehalt,
ROW_NUMBER() OVER(PARTITION BY abteilung ORDER BY gehalt DESC) AS rank_in_abteilung
FROM mitarbeiter;

Erklärung:

Hier wird für jeden Mitarbeiter eine Rangfolge in seiner Abteilung basierend auf dem Gehalt erstellt.
Der Mitarbeiter mit dem höchsten Gehalt in seiner Abteilung bekommt den Rang 1, der nächste den Rang 2 usw.

Wo und wie man `PARTITION BY` einsetzt:

Durchschnittliche Verkaufszahlen pro Region:

Wenn Sie die durchschnittlichen Verkaufszahlen pro Region in einer verkauf Tabelle haben möchten, können Sie die PARTITION BY Klausel verwenden, um die Daten nach Region zu unterteilen.

SELECT datum, region, verkaufszahl,
AVG(verkaufszahl) OVER(PARTITION BY region) AS durchschnittliche_verkaufszahl
FROM verkauf;

Berechnung kumulativer Summen:

Nehmen Sie an, Sie möchten wissen, wie sich der Umsatz in einem Monat kumulativ summiert. Hier kann PARTITION BY in Kombination mit der SUM() Funktion hilfreich sein.

SELECT datum, SUM(umsatz) OVER(PARTITION BY MONTH(datum) ORDER BY datum) AS kumulativer_umsatz
FROM verkauf;

Herausfinden von Höchst-/Tiefstwerten:

Möchten Sie das höchste Gehalt in jeder Abteilung herausfinden? Dies kann mit MAX() in Kombination mit PARTITION BY erreicht werden.

SELECT abteilung, MAX(gehalt) OVER(PARTITION BY abteilung) AS max_gehalt
FROM mitarbeiter;

Die PARTITION BY Klausel in SQL bietet eine mächtige Möglichkeit, Daten in sinnvolle Untergruppen zu unterteilen und Aggregat- oder Fensterfunktionen auf diese Untergruppen anzuwenden. Egal, ob Sie Durchschnittswerte berechnen, Rangfolgen erstellen oder kumulative Summen bilden möchten, PARTITION BY kann in vielen Szenarien eine effiziente Lösung bieten.

Fensterfunktionen, zu denen auch `PARTITION BY` gehört ist Teil des SQL:2003 Standards

Das bedeutet, dass die meisten modernen relationalen Datenbanksysteme (RDBMS) diese Funktionalität in irgendeiner Form unterstützen.

Dennoch gibt es einige Unterschiede und Besonderheiten in der Implementierung und den unterstützten Funktionen in verschiedenen RDBMS:

MS SQL Server: Unterstützt Fensterfunktionen und PARTITION BY ab Version 2005. Die Implementierung ist ziemlich standardkonform, und die oben gegebenen Beispiele sollten in einem MS SQL Server ohne Änderungen funktionieren.
Oracle: Oracle unterstützt Fensterfunktionen seit Version 8i. Die Syntax und Verwendung sind ähnlich standardkonform, mit zusätzlichen Funktionen und Features, die in späteren Versionen hinzugefügt wurden.
IBM DB2: Auch DB2 unterstützt Fensterfunktionen und ist in der Implementierung weitgehend standardkonform. Einige zusätzliche Funktionen oder Unterschiede können jedoch je nach DB2-Version und -Plattform vorhanden sein.
Teradata: Teradata unterstützt Fensterfunktionen und ist in seiner Implementierung dem SQL-Standard sehr ähnlich. Auch hier sollten die meisten in diesem Artikel gegebenen Beispiele ohne größere Änderungen funktionieren.

Trotz der generellen Standardkonformität kann es immer kleinere Unterschiede oder zusätzliche Features in der Implementierung von Fensterfunktionen in verschiedenen RDBMS geben. Es ist daher empfehlenswert, vor der Anwendung immer die spezifische Dokumentation oder Referenz des jeweiligen Datenbanksystems zu konsultieren.