Was ist die OVER()-Klausel in SQL?

Fensterfunktionen sind eine der leistungsfähigsten Ressourcen von SQL, werden aber vom durchschnittlichen SQL-Entwickler nicht häufig verwendet. In diesem Artikel wird erklärt, wie Sie verschiedene Arten von Fensterrahmen mit Hilfe der OVER Klausel definieren können.

Die OVER Klausel ist für SQL-Fensterfunktionen unerlässlich. Wie Aggregationsfunktionen führen Fensterfunktionen Berechnungen auf der Grundlage einer Reihe von Datensätzen durch - z. B. die Ermittlung des Durchschnittsgehalts einer Gruppe von Mitarbeitern.

In manchen Fällen können Aggregatfunktionen nicht verwendet werden, da sie alle einzelnen Datensätze zu einer Gruppe zusammenfassen; dies macht es unmöglich, sich auf bestimmte Werte zu beziehen (z. B. auf das Gehalt eines Mitarbeiters aus der Gruppe). In diesen Fällen sind Fensterfunktionen vorzuziehen, da sie die Zeilen nicht kollabieren; Sie können sich sowohl auf einen Spaltenwert auf Zeilenebene als auch auf den aggregierten Wert beziehen.

Es gibt noch andere Szenarien, in denen Fensterfunktionen nützlich sind. So kann es beispielsweise erforderlich sein, eine einzelne Spalte zu berechnen und eine Berechnung auf der Grundlage einer Reihe von Zeilen durchzuführen. Ein Beispiel aus der Praxis ist die Berechnung der Differenz zwischen dem durchschnittlichen Abteilungsgehalt und dem Gehalt der einzelnen Mitarbeiter in der Abteilung.

Bei der Verwendung von Fensterfunktionen ist die Definition der Datensatzgruppe, in der die Funktion berechnet werden soll, von entscheidender Bedeutung. Dieser Satz von Datensätzen wird als Fensterrahmen bezeichnet; wir definieren ihn mit der SQL-Klausel OVER.

In diesem Artikel werden wir SQL-Abfragen anhand der Datenbank eines kleinen Luxusuhrenherstellers demonstrieren. Das Unternehmen speichert seine Verkaufsinformationen in einer Tabelle namens sales:

Der Fensterrahmen ist ein Satz von Zeilen, der von der aktuellen Zeile abhängt; der Satz von Zeilen kann sich also für jede von der Abfrage verarbeitete Zeile ändern. Wir definieren Fensterrahmen mit der Klausel OVER. Die Syntax lautet:

  OVER ([PARTITION BY columns] [ORDER BY columns])

Die Unterklausel PARTITION BY definiert die Kriterien, die die Datensätze erfüllen müssen, um Teil des Fensterrahmens zu sein. Mit anderen Worten: PARTITION BY definiert die Gruppen, in die die Zeilen eingeteilt werden; dies wird in unserer nächsten Beispielabfrage deutlicher werden. Schließlich definiert die ORDER BY Klausel die Reihenfolge der Datensätze im Fensterrahmen.

Schauen wir uns die SQL OVER Klausel in Aktion an. Hier ist eine einfache Abfrage, die die Gesamtmenge der verkauften Einheiten für jeden Artikel zurückgibt.

SELECT sale_day, sale_time, 
       branch, article, quantity, revenue,
       SUM(quantity) OVER (PARTITION BY article) AS total_units_sold
FROM   sales

Diese Abfrage zeigt alle Datensätze der Tabelle sales mit einer neuen Spalte, die die Gesamtzahl der verkauften Einheiten für den betreffenden Artikel anzeigt. Wir können die Menge der verkauften Einheiten mit der Aggregationsfunktion SUM ermitteln, aber dann könnten wir nicht die einzelnen Datensätze anzeigen.

In dieser Abfrage gibt die OVER PARTITION BY article Subklausel an, dass der Fensterrahmen durch die Werte in der article Spalte bestimmt wird; alle Datensätze mit dem gleichen article Wert werden in einer Gruppe zusammengefasst. Unten sehen Sie das Ergebnis dieser Abfrage:

Die Spalte total_units_sold des Berichts wurde durch den Ausdruck ermittelt:

SUM(quantity) OVER (PARTITION BY article) total_units_sold

Den Lesern, die sich eingehender mit dem Thema befassen möchten, empfehle ich die folgenden beiden Artikel: Was ist der Unterschied zwischen GROUP BY und PARTITION BY und Fensterfunktionen in SQL Server: Teil Eins: Die OVER()-Klausel

Die SQL OVER-Klausel in Aktion

Angenommen, wir wollen für jeden Artikel die Gesamtmenge dieses Artikels, die in jedem Monat des Jahres 2021 verkauft wurde, mit der Gesamtmenge dieses Artikels im gesamten Jahr vergleichen. Zu diesem Zweck erstellen wir einen einfachen Bericht mit den Spalten article, month, units_sold_month und units_sold_year. Die Abfrage lautet:

SELECT DISTINCT article,
       EXTRACT('month' FROM sale_day) AS month,
       SUM(quantity) OVER (PARTITION BY article, sale_month) AS units_sold_month,
       SUM(quantity) OVER (PARTITION BY article) AS units_sold_year
FROM  sales 
WHERE EXTRACT('YEAR' FROM sale_day) = 2021
ORDER BY article, month

Die Ergebnisse der Abfrage sind:

Hier haben wir die Gesamtzahl der verkauften Einheiten anhand von zwei verschiedenen Gruppierungsgranularitäten berechnet: Monat und Jahr. Die erste Klausel OVER...

OVER (PARTITION BY article, sale_month) 

... ermöglicht es uns, die Anzahl der verkauften Einheiten jedes Artikels in einem Monat zu erhalten. Die zweite Klausel OVER...

OVER (PARTITION BY article)

... ermöglicht es uns, die Gesamtzahl der verkauften Einheiten eines bestimmten Artikels im gesamten Jahr zu berechnen.

In der nächsten Abfrage fügen wir einfach die Spalte month_percentage hinzu, um den prozentualen Anteil eines bestimmten Monats an der Gesamtzahl des Jahres anzuzeigen. Wir können dies mit der folgenden Abfrage berechnen:

SELECT DISTINCT article,
       EXTRACT('month' FROM sale_day) as month,
       SUM(quantity) OVER (PARTITION BY article, sale_month) AS units_sold_month,
       SUM(quantity) OVER (PARTITION BY article) AS units_sold_year,
       ( ( SUM(quantity) OVER (PARTITION BY article, sale_month)::decimal /
           SUM(quantity) OVER (PARTITION BY article)::decimal ) * 100
       ) AS month_percentage
FROM sales 
WHERE extract('YEAR' FROM sale_day) = 2021
ORDER BY article, month

Beachten Sie, dass wir in der vorherigen Abfrage keine unterschiedlichen OVER Klauseln verwendet haben; wir haben einfach dieselben Fensterfunktionen wiederverwendet und einen Prozentsatz berechnet. Die Ergebnisse sind unten zu sehen:

Wir werden nun einen anderen Bericht erstellen, der sich mit der Leistung der verschiedenen Zweige befasst. Wir möchten die Spalten branch und month anzeigen. Wir brauchen auch Berechnungen, um die:

• Gesamtumsatz für diesen Monat.
• Umsatz gruppiert nach Filiale und Monat.
• Monatlicher Durchschnittsumsatz der Filiale.
• Differenz zwischen dem Umsatz der einzelnen Filialen und dem monatlichen Durchschnittsumsatz.

SELECT DISTINCT branch,
       EXTRACT('month' FROM sale_day) AS month,
       SUM(revenue) OVER (PARTITION BY sale_month) AS total_revenue_month,
       SUM(revenue) OVER (PARTITION BY branch, sale_month) AS branch_revenue_month,
      
 -- Next column is the branch average revenue in the current month
	 ( 
		  SUM(revenue) OVER (PARTITION BY sale_month)::decimal 
		   / 
		  (SELECT COUNT(DISTINCT branch) FROM sales)::decimal 
	 ) AS average_month_branch,
 
 -- Next column is the difference between branch revenue and average branch revenue

        SUM(revenue) OVER (PARTITION BY branch, sale_month) -
	 ( 
		  SUM(revenue) OVER (PARTITION BY sale_month)::decimal 
		   / 
		  (SELECT COUNT(DISTINCT branch) FROM sales)::decimal 
	 ) AS gap_branch_average

FROM sales 
WHERE extract('YEAR' from sale_day) = 2021
ORDER BY branch, month

Wiederum haben wir nur zwei OVER Klauseln verwendet, aber wir haben verschiedene arithmetische Ausdrücke benutzt, um bestimmte Werte zu erhalten. Wir verwenden ...

SUM(revenue) OVER (PARTITION BY sale_month) 

... verwendet, um den Gesamtumsatz des Monats zu berechnen, aber wir haben ihn auch in einem arithmetischen Ausdruck verwendet, um den monatlichen Durchschnittsumsatz der Filialen zu erhalten.

Wir haben ...

SUM(revenue) OVER (PARTITION BY branch, sale_month) 

..., um den monatlichen Umsatz der Filiale und die Differenz zwischen dem monatlichen Umsatz dieser Filiale und dem Durchschnitt zu berechnen.

Die nächste Tabelle ist das Ergebnis der Abfrage. Beachten Sie, dass die Spalte gap_branch_average positive oder negative Zahlen enthalten kann. Eine negative Zahl bedeutet, dass der Monatsumsatz dieser Filiale unter dem Durchschnittsumsatz lag.

Für weitere Informationen über Fensterfunktionen in SQL empfehle ich Beispiel für SQL Fensterfunktionen mit erklärungen , ein Artikel für Einsteiger über Fensterfunktionen. Für fortgeschrittene Leser: How to Rank Rows Within a Partition in SQL zeigt, wie Sie mit der Fensterfunktion RANK() Rangfolgen in Ihren Berichten erstellen können.

Die OVER-Klausel und Analytical Fensterfunktionen

In den vorherigen Abfragen haben wir Fensterfunktionen verwendet, um monatliche Zahlen (Umsatz bzw. verkaufte Einheiten) mit jährlichen Zahlen zu vergleichen. In diesem Abschnitt werden wir geordnete Fensterrahmen verwenden, die es uns ermöglichen, einen Datensatz im Rahmen auf der Grundlage seiner Position auszuwählen. Wir können zum Beispiel den ersten Datensatz im Fensterrahmen, den Datensatz vor dem aktuellen Datensatz oder den Datensatz nach dem aktuellen Datensatz auswählen. Diese analytischen Fensterfunktionen bieten SQL eine große Ausdruckskraft.

In der folgenden Abfrage wollen wir den Umsatzanstieg/-rückgang für dieselbe Branche in zwei zusammenhängenden Monaten anzeigen. Dazu müssen wir die Differenz zwischen dem Umsatz des aktuellen Monats und dem Umsatz des Vormonats berechnen. Dazu ist die analytische Fensterfunktion LAG() erforderlich, die einen Spaltenwert aus einer Zeile vor der aktuellen Zeile ermitteln kann.

WITH branch_month_sales AS (
	SELECT    DISTINCT
		    branch,
		    EXTRACT('MONTH' FROM sale_day) AS month,
		    SUM(revenue) OVER (PARTITION BY branch, sale_month ) AS revenue
	FROM sales
)
SELECT branch, 
	month,
	revenue AS revenue_current_month,
	LAG(revenue) OVER (PARTITION BY branch ORDER BY month) AS revenue_prev_month,
	revenue - LAG(revenue) OVER (PARTITION BY branch ORDER BY month) AS revenue_delta
FROM branch_month_sales
ORDER BY branch, month

In dieser Abfrage wurde ein gemeinsamer Tabellenausdruck (CTE) mit der Bezeichnung branch_month_sales verwendet, um die Gesamteinnahmen der einzelnen Zweige und Monate zu speichern. Dann haben wir eine zweite Abfrage geschrieben, die die Fensterfunktion LAG() verwendet, um den Umsatz des Vormonats zu erhalten (unter Verwendung der Informationen aus branch_month_sales). Beachten Sie, dass der Fensterrahmen nach Monat geordnet ist.

Hier sind die Ergebnisse:

In allen in diesem Artikel gezeigten Abfragen haben wir nur einige wenige Fensterfunktionen verwendet. Es gibt noch eine ganze Reihe anderer Fensterfunktionen in SQL. Hier ist eine Liste der einzelnen Funktionen:

Erfahren Sie mehr über die SQL OVER-Klausel

In diesem Artikel haben wir uns mit der OVER Klausel und den Unterklauseln PARTITION BY und ORDER BY beschäftigt.

Wenn Sie mehr über Fensterfunktionen erfahren möchten, möchte ich Ihnen zwei Artikel ans Herz legen. Der erste ist 8 Best SQL Window Function Articles, der Sie auf weitere großartige Artikel hinweist. Der zweite ist ein Spickzettel für Fensterfunktionen, der Syntax, Beispiele und Bilder enthält; es ist mein Lieblingsartikel über Fensterfunktionen.

Für diejenigen, die tiefer einsteigen wollen, empfehle ich unseren interaktiven Fensterfunktionen SQL-Kurs. Wenn Sie Ihre SQL-Kenntnisse im Allgemeinen verbessern möchten, sollten Sie den Kurs SQL von A bis Z ausprobieren. Er bietet einen umfassenden Überblick über alles, was Sie wissen müssen, um effektiv mit SQL zu arbeiten.