Wie verwendet man die SQL PARTITION BY-Klausel mit OVER?

Das Herzstück jedes Aufrufs einer Fensterfunktion ist eine OVER-Klausel, die definiert, wie die Fenster der Datensätze aufgebaut sind. Innerhalb der OVER-Klausel kann es eine optionale PARTITION BY-Subklausel geben, die die Kriterien zur Identifizierung der in jedes Fenster aufzunehmenden Datensätze definiert. Lesen Sie weiter und machen Sie einen wichtigen Schritt zur Erweiterung Ihrer SQL-Kenntnisse!

Was ist die PARTITION BY-Klausel in SQL?

Der SQL-Ausdruck PARTITION BY ist eine Unterklausel der Klausel OVER, die in fast allen Aufrufen von Fensterfunktionen wie AVG(), MAX() und RANK() verwendet wird. Wie viele Leser wahrscheinlich wissen, arbeiten Fensterfunktionen mit Fensterrahmen, d. h. mit Gruppen von Zeilen, die für jeden Datensatz im Abfrageergebnis unterschiedlich sein können. Hier kommt die Unterklausel SQL PARTITION BY ins Spiel: Sie wird verwendet, um zu definieren, welche Datensätze Teil des Fensterrahmens sein sollen, der mit jedem Datensatz des Ergebnisses verbunden ist.

Dieser Artikel erklärt die SQL PARTITION BY und ihre Verwendung anhand von Beispielen. Da er eng mit den Fensterfunktionen zusammenhängt, sollten Sie zunächst einige Artikel über Fensterfunktionen lesen, z. B. "SQL Window Function Example With Explanations", wo Sie viele Beispiele finden. Wenn Sie mehr über Fensterfunktionen erfahren möchten, gibt es auch einen interessanten Artikel mit vielen Verweisen auf andere Artikel über Fensterfunktionen.

Das erste, worauf Sie achten sollten, ist die Syntax. Hier sehen Sie, wie Sie die SQL-Klausel PARTITION BY verwenden:

SELECT
    ,
     OVER(PARTITION BY  [ORDER BY ])
FROM table;

Schauen wir uns nun ein Beispiel an, das eine PARTITION BY Klausel verwendet. Wir werden die folgende Tabelle verwenden, die car_list_prices:

Für jedes Auto wollen wir die Marke, das Modell, den Preis, den Durchschnittspreis über alle Autos und den Durchschnittspreis über denselben Autotyp (um eine bessere Vorstellung davon zu bekommen, wie der Preis eines bestimmten Autos im Vergleich zu anderen Autos ist) erhalten. Hier ist die Abfrage:

SELECT
    car_make,
    car_model,
    car_price,
    AVG(car_price) OVER() AS "overall average price",
    AVG(car_price) OVER (PARTITION BY car_type) AS "car type average price"
FROM car_list_prices

Das Ergebnis der Abfrage ist das folgende:

Die obige Abfrage verwendet zwei Fensterfunktionen. Die erste wird verwendet, um den Durchschnittspreis für alle Fahrzeuge in der Preisliste zu berechnen. Sie verwendet die Fensterfunktion AVG() mit einer leeren OVER Klausel, wie im folgenden Ausdruck zu sehen ist:

AVG(car_price) OVER() AS "overall average price"

Die zweite Fensterfunktion wird verwendet, um den Durchschnittspreis für ein bestimmtes car_type wie Standard, Premium, Sport usw. zu berechnen. Hier verwenden wir eine OVER Klausel mit einer PARTITION BY Subklausel, wie in diesem Ausdruck zu sehen ist:

AVG(car_price) OVER (PARTITION BY car_type) AS "car type average price"

Die Fensterfunktionen sind ziemlich mächtig, nicht wahr? Wenn Sie mehr lernen möchten, indem Sie gut vorbereitete Übungen machen, empfehle ich Ihnen den Kurs Fensterfunktionenzu empfehlen, in dem Sie die Verwendung von Fensterfunktionen in SQL-Datenbanken kennenlernen und sich damit vertraut machen können.

Vertiefung mit der SQL-Klausel PARTITION BY

Die GROUP BY Klausel gruppiert eine Reihe von Datensätzen anhand von Kriterien. Dadurch können wir eine Funktion (z. B. AVG() oder MAX()) auf Gruppen von Datensätzen anwenden, um ein Ergebnis pro Gruppe zu erhalten.

Ein Beispiel: Sie möchten den Durchschnittspreis und den Höchstpreis für jede Marke ermitteln. Verwenden Sie die folgende Abfrage:

SELECT car_make,
       AVG(car_price) AS average_price,
       MAX(car_price) AS top_price
FROM   car_list_prices
GROUP BY car_make

Hier ist das Ergebnis dieser Abfrage:

Im Vergleich zu Fensterfunktionen fasst GROUP BY einzelne Datensätze zu einer Gruppe zusammen. Folglich können Sie sich nicht auf ein einzelnes Datensatzfeld beziehen, sondern nur auf die Spalten in der GROUP BY Klausel.

Angenommen, Sie möchten einen Bericht erstellen, der das Modell, den Preis und den Durchschnittspreis der Marke enthält. Dies ist mit GROUP BY nicht möglich, da die einzelnen Datensätze jedes Modells aufgrund der Klausel GROUP BY car_make zusammengeklappt werden. Für so etwas müssen Sie Fensterfunktionen verwenden, wie im folgenden Beispiel zu sehen ist:

SELECT car_make,
       car_model,
       car_price,
       AVG(car_price) OVER (PARTITION BY car_make) AS average_make
FROM   car_list_prices

Das Ergebnis dieser Abfrage ist das folgende:

Wer tiefer einsteigen möchte, dem empfehle ich den Artikel "What Is the Difference Between a GROUP BY and a PARTITION BY?" mit vielen Beispielen unter Verwendung von Aggregat- und Fensterfunktionen.

Zusätzlich zur Klausel PARTITION BY gibt es eine weitere Klausel namens ORDER BY, die die Reihenfolge der Datensätze innerhalb des Fensterrahmens festlegt. Für einige Fensterfunktionen ist eine ORDER BY erforderlich. Die Fensterfunktionen LEAD() und LAG() benötigen beispielsweise ein geordnetes Datensatzfenster, da sie auf den vorangehenden oder den nächsten Datensatz des aktuellen Datensatzes zugreifen.

Ein Fensterrahmen besteht aus mehreren Zeilen, die durch die Kriterien in der Klausel PARTITION BY definiert sind. Wir können jedoch auch Grenzen für den Fensterrahmen angeben, wie in der folgenden Abbildung zu sehen ist:

Die unteren und oberen Grenzen in der OVER Klausel können sein:

• UNBOUNDED PRECEDING
• n PRECEDING
• CURRENT ROW
• n FOLLOWING
• UNBOUNDED FOLLOWING

Wenn in einer OVER -Klausel keine Begrenzung angegeben wird, wird der Fensterrahmen auf der Grundlage einiger Standardgrenzwerte erstellt. Diese hängen von der Syntax ab, die für den Aufruf der Fensterfunktion verwendet wird. Die folgende Tabelle zeigt die Standardbegrenzungen des Fensterrahmens.

Es gibt einen ausführlichen Artikel mit dem Titel "SQL Fensterfunktionen Cheat Sheet", in dem Sie viele Details zur Syntax und Beispiele zu den verschiedenen Begrenzungen des Fensterrahmens finden können.

Die SQL PARTITION BY-Klausel in Aktion

In diesem Abschnitt zeigen wir einige Beispiele für die SQL-Klausel PARTITION BY. Sie basieren alle auf der Tabelle paris_london_flights, die von einer Fluggesellschaft verwendet wird, um die Geschäftsergebnisse dieser Strecke für die Jahre 2018 und 2019 zu analysieren. Hier ist eine Teilmenge der Daten:

Beispiel 1

Die erste Abfrage erzeugt einen Bericht, der die flight_number, aircraft_model mit der Anzahl der beförderten Passagiere und den Gesamteinnahmen enthält. Die Abfrage lautet wie folgt:

SELECT DISTINCT
       flight_number,
       aircraft_model,
	SUM(num_of_passengers) OVER (PARTITION BY flight_number, aircraft_model)
                                                            AS total_passengers,
	SUM(total_revenue) OVER (PARTITION BY flight_number, aircraft_model)
                                                            AS total_revenue
FROM paris_london_flights
ORDER BY flight_number, aircraft_model;

Da die Gesamtzahl der beförderten Passagiere und die Gesamteinnahmen für jede mögliche Kombination von flight_number und aircraft_model generiert werden, verwenden wir die folgende PARTITION BY Klausel, um eine Gruppe von Datensätzen mit derselben Flugnummer und demselben Flugzeugmodell zu erzeugen:

OVER (PARTITION BY flight_number, aircraft_model)

Dann wenden wir für jeden Satz von Datensätzen die Fensterfunktionen SUM(num_of_passengers) und SUM(total_revenue) an, um die in der nächsten Ergebnismenge gezeigten Metriken total_passengers und total_revenue zu erhalten.

Beispiel 2

In der nächsten Abfrage zeigen wir, wie sich das Geschäft entwickelt, indem wir die Metriken eines Monats mit denen des Vormonats vergleichen. Wir erstellen einen Bericht mit Hilfe von Fensterfunktionen, um die monatlichen Veränderungen bei den Fahrgästen und den Einnahmen zu zeigen.

WITH year_month_data AS (
  SELECT DISTINCT
	   EXTRACT(YEAR FROM scheduled_departure) AS year,
	   EXTRACT(MONTH FROM scheduled_departure) AS month,
	   SUM(number_of_passengers)
              OVER (PARTITION BY EXTRACT(YEAR FROM scheduled_departure),
                                  EXTRACT(MONTH FROM scheduled_departure)
                   ) AS passengers
   FROM  paris_london_flights
  ORDER BY 1, 2
)
SELECT  year,
        month,
	 passengers,
	 LAG(passengers) OVER (ORDER BY year, month) passengers_previous_month,
	 passengers - LAG(passengers) OVER (ORDER BY year, month) AS passengers_delta
FROM year_month_data;

In der obigen Abfrage verwenden wir eine WITH -Klausel, um eine CTE zu generieren (CTE steht für Common Table Expressions und ist eine Art von Abfrage zur Generierung einer virtuellen Tabelle, die im Rest der Abfrage verwendet werden kann). Wir füllen die Daten in eine virtuelle Tabelle namens year_month_data, die 3 Spalten hat: year, month und passengers mit der Gesamtzahl der beförderten Passagiere im Monat.

Dann erzeugt die zweite Abfrage (die die CTE year_month_data als Eingabe verwendet) das Ergebnis der Abfrage. Die Spalte Passagiere enthält die Gesamtzahl der beförderten Passagiere, die mit dem aktuellen Datensatz verbunden sind. Mit der Fensterfunktion LAG(passenger) erhalten wir den Wert der Spalte Passagiere des vorherigen Datensatzes zum aktuellen Datensatz. Wir ORDER BY year and month:

LAG(passengers) OVER (ORDER BY year, month)
passengers_previous_month

Er erhält die Anzahl der Fahrgäste aus dem vorherigen Datensatz, der dem Vormonat entspricht. Anschließend wird die Zahl der Fahrgäste für den aktuellen und den vorangegangenen Monat angezeigt. In der letzten Spalte wird schließlich die Differenz zwischen beiden Werten berechnet, um die monatliche Veränderung der Fahrgastzahlen zu erhalten.

Beispiel 3

In unserem letzten Beispiel betrachten wir die Flugverspätungen. Wir wollen verschiedene Durchschnittswerte für Verspätungen erhalten, um die Gründe für die Verspätungen zu erklären.

Wir verwenden eine CTE, um eine Spalte namens month_delay mit der durchschnittlichen Verspätung für jeden Monat zu berechnen und das Flugzeugmodell zu erhalten. In der Hauptabfrage erhalten wir dann die verschiedenen Durchschnittswerte wie unten dargestellt:

WITH paris_london_delays AS (
  SELECT DISTINCT
	   aircraft_model,
	   EXTRACT(YEAR FROM scheduled_departure) AS year,
	   EXTRACT(MONTH FROM scheduled_departure) AS month,
	   AVG(real_departure - scheduled_departure) AS month_delay
   FROM  paris_london_flights
   GROUP BY 1, 2, 3
)
SELECT  DISTINCT
     aircraft_model,
     year,
     month,
     month_delay AS monthly_avg_delay,
     AVG(month_delay) OVER (PARTITION BY aircraft_model, year) AS year_avg_delay,
     AVG(month_delay) OVER (PARTITION BY year) AS year_avg_delay_all_models,
     AVG(month_delay) OVER (PARTITION BY aircraft_model, year 
                               ORDER BY month
                               ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
                            ) AS rolling_average_last_4_months

FROM paris_london_delays
ORDER BY 1,2,3

Diese Abfrage berechnet mehrere Durchschnittswerte. Der erste ist der Durchschnitt pro Flugzeugmodell und Jahr, was sehr eindeutig ist. Der zweite ist der Durchschnitt pro Jahr über alle Flugzeugmodelle. Beachten Sie, dass wir nur die Spalte Jahr in der PARTITION BY Klausel verwenden. Der dritte und letzte Durchschnitt ist der gleitende Durchschnitt, bei dem wir die letzten drei Monate und den aktuellen Monat (d. h. die Zeile) verwenden, um den Durchschnitt mit dem folgenden Ausdruck zu berechnen:

AVG(month_delay) OVER (PARTITION BY aircraft_model, year
                               ORDER BY month
                               ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
                           ) AS rolling_average_last_4_months

Die Klausel ROWS BETWEEN 3 PRECEDING AND CURRENT ROW in PARTITION BY schränkt die Anzahl der Zeilen (d. h. Monate) ein, die in den Durchschnitt einbezogen werden: die letzten 3 Monate und der aktuelle Monat. Ein Teilergebnis dieser Abfrage sehen Sie unten:

Der Artikel "Die RANGE-Klausel in SQL Fensterfunktionen: 5 praktische Beispiele" erklärt, wie man eine Teilmenge von Zeilen im Fensterrahmen mit Hilfe von RANGE anstelle von ROWS definiert, mit mehreren Beispielen. Ein weiterer interessanter Artikel ist "Common SQL Fensterfunktionen: Using Partitions With Ranking Functions", in dem die PARTITION BY-Klausel im Detail behandelt wird.

Die Leistungsfähigkeit von Fensterfunktionen und der SQL PARTITION BY

Fensterfunktionen sind eine sehr mächtige Ressource der SQL-Sprache, und die SQL PARTITION BY Klausel spielt eine zentrale Rolle bei ihrer Verwendung. In diesem Artikel haben wir uns mit der Funktionsweise dieser Klausel beschäftigt und mehrere Beispiele mit unterschiedlichen Syntaxen gezeigt.

Bevor ich schließe, schlage ich einen Fortgeschrittenes SQL Kurs, in dem Sie über die Grundlagen hinausgehen und ein SQL-Meister werden können. Wenn Sie mehr über die OVER-Klausel lesen möchten, gibt es einen vollständigen Artikel zum Thema: "How to Define a Window Frame in SQL Fensterfunktionen." Verbessern Sie Ihre Fähigkeiten und erweitern Sie Ihr Vermögen!