Lernen Sie die OVER()-Klausel in SQL mit 7 Beispielen

Lernen Sie, wie Sie die OVER()-Klausel in Ihren SQL-Abfragen verwenden können, und Sie werden in Ihren SQL-Kenntnissen einen Schritt nach vorne machen.

Die meisten Datenanalysten und SQL-Entwickler sind mit den Klauseln SELECT, FROM, WHERE, GROUP BY und ORDER BY gut vertraut. Diese Klauseln stellen jedoch nur eine begrenzte Teilmenge der SQL-Sprache dar. Es gibt noch andere Klauseln, wie OVER(), die es uns ermöglichen, flexible SQL-Berichte zu erstellen und der SQL-Sprache eine enorme Ausdruckskraft zu verleihen.

In diesem Artikel werden wir die OVER() Klausel anhand einiger Beispiele für ihre Verwendung erklären. Fangen wir an!

Die OVER()-Klausel in SQL

SQL ist relativ leicht zu erlernen; jeder kann lernen, wie man einfache Abfragen in SQL erstellt. Tatsächlich kann der durchschnittliche IT-Profi oder SQL-Entwickler die grundlegenden Klauseln SELECT, FROM, WHERE, GROUP BY und ORDER BY mit Leichtigkeit handhaben. Diese Klauseln gab es schon in SQL-92, das 30 Jahre alt ist! Die SQL-Sprache ist jedoch noch nicht am Ende; seit 1992 sind viele neue Elemente hinzugekommen. Eines davon ist die OVER()-Klausel, die es uns ermöglicht, Fensterfunktionen in SQL-Abfragen zu verwenden.

In SQL sind Fensterfunktionen ähnlich wie GROUP BY, da sie eine Gruppe von Zeilen bearbeiten. Allerdings basieren Window-Funktionen auf einem Datenfenster oder einer Gruppe von Zeilen, die mit der aktuellen Zeile zusammenhängen. Im Gegensatz zu GROUP BY werden bei Fensterfunktionen die Zeilen nicht komprimiert; die Details der einzelnen Zeilen bleiben intakt.

Für diejenigen, die sich eingehender mit OVER() und Fensterfunktionen in SQL beschäftigen möchten, empfehle ich unseren Online-KursFensterfunktionen . Er enthält eine Fülle von Beispielen für die Verwendung verschiedener Fensterfunktionen.

Ok, kommen wir zurück zur OVER()-Funktion. Schauen wir uns zunächst unsere Daten an.

Einführung in die Daten

Unsere Beispielabfragen basieren auf den folgenden Daten.

Nehmen wir an, wir haben ein Meeresfrüchte-Restaurant an der Mittelmeerküste. Wir haben auch eine Datenbanktabelle, in der wir den Verlauf der Aktivitäten des Restaurants speichern. Unsere Datenbank hat eine Tabelle namens restaurant_activity mit diesen Spalten:

• table_number
• waiter_name
• start_date
• start_time
• served_time
• end_time
• total_diners
• amount_payment
• total_tips.

Unten sehen Sie ein Beispiel für die Daten:

Restaurant_activity

Das alles sollte selbsterklärend sein, aber lassen Sie uns kurz ein paar Spalten durchgehen. start_date und start_time sind das Datum und die Uhrzeit, zu der die Kunden unter table_number eine Mahlzeit bestellt haben; served_time ist der Zeitpunkt, zu dem die Mahlzeit serviert wurde, und end_time ist der Zeitpunkt, zu dem die Kunden ihre Rechnung verlangt haben. amount_payment ist der Preis der Mahlzeit, ohne Trinkgeld (das als total_tips gespeichert wird).

Beispielabfrage Nr. 1: Eine einfache Anwendung von OVER()

Beginnen wir mit der OVER() Klausel, um den prozentualen Anteil jeder Bestellung am gesamten Tagesumsatz auf 5/5/2022 zu berechnen. Wir können die Gesamteinnahmen des Restaurants an diesem Tag (ohne Trinkgeld) mit OVER() in Kombination mit der Fensterfunktion SUM() berechnen. Die Klausel OVER() ist immer mit einer Fensterfunktion verbunden; Fensterfunktionen berechnen einen Wert auf der Grundlage einer durch die Klausel OVER() definierten Gruppe von Datensätzen. Die Abfrage lautet:

SELECT 
  start_date AS date,
  SUM(amount_payment) OVER () AS daily_revenue,
  amount_payment AS total_order,
  (amount_payment / SUM(amount_payment) OVER ())*100 AS order_percentage
FROM   restaurant_activity
WHERE  start_date = ’5/5/2022’

Die vorherige Abfrage berechnet daily_revenue für 5/5/2022, indem jeder Zahlungsbetrag für diesen Tag addiert wird. Die Klausel OVER() ist leer, was bedeutet, dass das Fenster der Datensätze, die zur Berechnung der Funktion SUM() verwendet werden, der gesamte Satz von Datensätzen ist, der von der Abfrage zurückgegeben wird. Mit anderen Worten, das Fenster besteht aus allen Datensätzen für dieses Datum.

Bei der Berechnung der Spalte order_percentage dividieren wir den einzelnen Bestellbetrag durch den gesamten Tagesumsatz, um den Prozentsatz zu erhalten; dies ist ein zentraler Punkt in der Abfrage, da wir Spalten auf Zeilenebene mit den Ergebnissen der Fensterfunktion in einem Ausdruck kombinieren.

Zu verstehen, welche Datensätze Teil des Fensters sind, ist von zentraler Bedeutung für das Verständnis der Funktionsweise von Fensterfunktionen; wir werden später in diesem Artikel auf diesen Punkt zurückkommen. Schauen wir uns jetzt erst einmal die Ergebnisse an:

Bevor wir uns mit komplexeren OVER() Beispielen beschäftigen, möchte ich Ihnen unser Fensterfunktionen Übungsset empfehlen. Wenn Sie den Umgang mit OVER() wirklich lernen wollen, sind diese 100 interaktiven Übungen genau das Richtige für Sie.

Beispielabfrage Nr. 2: Verwendung der Unterklausel PARTITION BY

In diesem Beispiel verwenden wir die Subklausel PARTITION BY, die mit OVER() zusammenarbeitet, um Datenfenster zu definieren.

Nehmen wir an, wir wollen einen Bericht ähnlich dem vorherigen, aber erweitert auf jeden Tag im Mai 2022. Für jeden Tag möchten wir den gesamten Tagesumsatz, den individuellen Auftragswert für alle Aufträge und den prozentualen Anteil der einzelnen Aufträge am Tagesumsatz sehen. Hier ist die Abfrage:

SELECT start_date AS date,
  		SUM(amount_payment) OVER (PARTITION BY start_date) AS daily_revenue,
  		amount_payment AS total_order,
  		(amount_payment / SUM(amount_payment) OVER (PARTITION BY start_date)) * 100 AS
    order_percentage
FROM restaurant_activity
WHERE start_date BETWEEN ’5/1/2022’ AND ’5/31/2022’ 

Wir haben bereits erwähnt, dass Fensterfunktionen auf der Grundlage eines Fensters von Datensätzen (oder einer Gruppe von Datensätzen) arbeiten, die sich auf den aktuellen Datensatz beziehen. Die vorherige Abfrage verwendet die Unterklausel PARTITION BY start_date, um zu definieren, welche Datensätze in jedes Fenster gehören. In unserem Beispiel sind alle Datensätze mit demselben start_date Wert wie die aktuelle Zeile Teil des Fensters. Da wir nur zwei eindeutige Werte für start_date haben, haben wir nur zwei verschiedene Fenster. Sie werden im folgenden Ergebnis in grün und rot dargestellt:

Beispielabfrage Nr. 3: Verwendung der ORDER BY Subklausel

Lassen Sie uns nun die ORDER BY Subklausel einführen, die es Ihnen ermöglicht, Zeilen in eine bestimmte Reihenfolge innerhalb des Fensters zu bringen.

Nehmen wir an, wir möchten einen einfachen Bericht mit den fünf teuersten Bestellungen zusammen mit dem Kellner, dem Datum und der Tischnummer erstellen. Die Abfrage lautet:

WITH ranking AS (
	SELECT 
table_number, 
amount_payment, 
waiter_name, 
start_date, 
	       RANK() OVER (ORDER BY amount_payment DESC) AS position
	FROM restaurant_activity
)
SELECT 
  amount_payment, 
  waiter_name, 
  start_date, 
  table_number, 
  position
FROM ranking
WHERE position <= 5
ORDER BY position

Hier haben wir eine CTE (oder Common Table Expression) verwendet, um die Rangfolge zu erstellen. In der CTE wurde die Position jeder Bestellung mit Hilfe der Fensterfunktion RANK() in Kombination mit der folgenden OVER() Klausel berechnet:

RANK() OVER (ORDER BY amount_payment DESC)

Die obige Klausel definiert ein Fenster von Datensätzen, das durch alle Datensätze in der Tabelle gebildet wird, so dass alle Bestellungen des Restaurants enthalten sind. Dieses Fenster wird von amount_payment in absteigender Reihenfolge sortiert: Der größte Betrag ist der erste Datensatz im Fenster usw. Die Funktion RANK() gibt die Position der aktuellen Zeile im geordneten Fenster zurück und speichert diesen Wert in der Spalte Position der CTE.

Nachdem wir die CTE für die Rangfolge erstellt haben, verwendet der Rest der Abfrage ranking wie jede andere Tabelle. Wir filtern die Datensätze, um nur die ersten 5 Ränge zu erhalten, und ordnen die Ergebnisse dann nach position. Dies ist das Ergebnis:

An dieser Stelle möchte ich Ihnen den Artikel SQL Fensterfunktionen vs. GROUP BY: What's the Difference? Er erklärt die Unterschiede zwischen der GROUP BY Klausel und den Fensterfunktionen anhand mehrerer SQL-Abfragebeispiele. Ein weiterer interessanter Artikel mit zusätzlichen Details ist Was ist die OVER()-Klausel in SQL?

Beispielabfrage Nr. 4: Berechnung der tabellenfreien Zeit mit OVER() und LAG()

Window-Funktionen bieten viele Möglichkeiten, um komplexe Berechnungen zu vereinfachen. Eine davon ist die Funktion LAG(), die einen Wert aus einer beliebigen Spalte der vorherigen Zeile in Bezug auf die aktuelle Zeile des Fensters zurückgibt.

Der Besitzer des Restaurants möchte wissen, wie lange die Tische frei sind, z. B. die Zeit, die zwischen den Gästen vergeht. Dazu kann ein Bericht mit den Spalten table_number, date, free_start, free_end und free_time_duration erstellt werden.

Um die freie Zeit zu berechnen, müssen wir auf zwei Zeilen zugreifen. Wir benötigen die end_time der vorherigen Belegung und die start_time der nächsten Belegung; dann können wir die verstrichene Zeit zwischen den beiden Belegungen berechnen. Hier kommt die Fensterfunktion LAG() ins Spiel, denn LAG() ermöglicht den Zugriff auf eine beliebige Spalte im vorherigen Datensatz. Hier ist die Abfrage, die wir verwenden würden:

    SELECT 
start_date AS date,
table_number,
-- ending time of the previous occupation 
COALESCE(LAG(end_time) OVER (PARTITION BY start_date, table_number 
                             ORDER BY start_time),'11:00') AS start_free_time,
-- starting time of current occupation
start_time AS end_free_time,
-- calculating the free time when the table was unoccupied
start_time - 
COALESCE(LAG(end_time) OVER (PARTITION BY start_date, table_number 
                             ORDER BY start_time),'11:00') AS free_time_duration
    FROM   restaurant_activity

Der Schlüssel in der vorherigen Abfrage ist die Fensterfunktion LAG(). Wir haben sie verwendet, um die end_time des vorherigen Berufs zu erhalten. Die Klausel ...

OVER (PARTITION BY start_date, table_number ORDER BY start_time)

... definiert ein Fenster (oder eine Reihe von Zeilen) für jedes eindeutige Paar von <start_date, table_number>, und jedes dieser Fenster ist nach start_time geordnet. Dann gibt LAG(end_time) die Endzeit der vorherigen Belegung der Tabelle zurück.

Sie haben wahrscheinlich bemerkt, dass wir LAG() zweimal verwendet haben. Das erste Mal wird verwendet, um die Anfangszeit des freien Zeitraums zu erhalten, und das zweite Mal, um die Dauer der freien Zeit mit Hilfe des folgenden Ausdrucks zu berechnen:

start_time - 
coalesce(LAG(end_time) OVER (PARTITION BY start_date, table_number 
                             ORDER BY start_time),'11:00')

Die Ergebnisse der Abfrage sind:

Bevor wir zum nächsten Abschnitt übergehen, empfehle ich die folgenden Artikel für weitere Details:

• SQL Window Function Beispiel mit Erklärungen
• Wie man Zeilen in SQL ordnet: Eine vollständige Anleitung
• Die LAG-Funktion und die LEAD-Funktion in SQL
• Wie man die Länge einer Reihe mit SQL berechnet

Beispielabfrage #5: Berechnen von Rangfolgen mit der OVER()-Klausel

In diesem Abschnitt werden wir eine Geschäftssituation behandeln, in der die SQL OVER() Klausel angewendet werden kann, um eine Rangliste zu erstellen, z. B. die 10 besten Verkäufer oder die 5 meistverkauften Produkte. Sie können die OVER() Klausel in Kombination mit der RANK() Fensterfunktion verwenden, um diese Art von Bericht zu erhalten. Sehen wir uns eine Beispielabfrage an, die die beiden höchsten Trinkgelder des Tages und den Kellner, der sie erhalten hat, zurückgibt:

SELECT  *
FROM (
  		SELECT 	waiter_name,
  			start_date AS date,
			total_tips AS tip_amount,
			RANK() OVER(PARTITION BY start_date ORDER BY total_tips DESC) AS ranking
  	  	FROM restaurant_activity
     	     ) AS ranking_table
WHERE ranking <= 2;

Wir haben eine Unterabfrage in der FROM Klausel verwendet, um eine temporäre "Tabelle" namens ranking_table zu erstellen. Sie verwendet die Spalte ranking, um die Position des Trinkgelds in der täglichen Trinkgeldrangliste zu speichern. Die Position in der Rangliste wird mit der Fensterfunktion RANK() berechnet. Andere Spalten in der temporären Tabelle sind waiter_name, date und tip_amount.

In der äußeren Abfrage filtern wir nur die Tipps mit den Rängen 1 und 2. Das Ergebnis der Abfrage ist unten dargestellt:

Beispielabfrage Nr. 6: Berechnen von Unterschieden zu einem früheren Zeitraum

Eine weitere interessante Möglichkeit ist die Anzeige der Wertedifferenz zwischen dem vorherigen und dem aktuellen Zeitraum. Die OVER() Klausel kombiniert mit Fensterfunktionen wie LEAD() und LAG() werden verwendet, um diese Art von Bericht zu erstellen. Weitere Hintergrundinformationen finden Sie unter Wie man die Differenz zwischen zwei Zeilen in SQL berechnet.

Nehmen wir an, wir möchten die Tageseinnahmen der einzelnen Kellner zusammen mit der Differenz zum Vortag anzeigen. In derselben Zeile möchten wir auch die Differenz in Prozent anzeigen. Hier ist die Abfrage:

SELECT	
  waiter_name,
  date,
  today_revenue,
  -- revenue variation ----------------------------------------------
  LAG(today_revenue) OVER (
    PARTITION BY waiter_name ORDER BY date) yesterday_revenue,
  today_revenue - LAG(today_revenue) OVER (
    PARTITION BY waiter_name ORDER BY date) AS revenue_variation,
  -- -----------------------------------------------------------------
  -- revenue variation percentage ------------------------------------------------
  round((today_revenue - 
   LAG(today_revenue) OVER (PARTITION BY waiter_name ORDER BY date)) /
   LAG(today_revenue) OVER (PARTITION BY waiter_name ORDER BY date),2) * 100 
AS revenue_variation_percentage
  -- -----------------------------------------------------------------------------		
FROM (
  SELECT  DISTINCT 
    start_date::date AS "date",
    waiter_name,
    SUM(total_tips) OVER ( 
      PARTITION BY waiter_name, start_date::date ORDER BY start_date)
AS today_revenue
  FROM 	restaurant_activity
) AS daily_revenue_per_waiter;

Wir haben eine Unterabfrage in der FROM -Klausel mit dem Namen daily_revenue_per_waiter erstellt, die waiter_name, date und den Gesamtumsatz dieses Kellners an diesem Tag enthält. In der äußeren Abfrage verwenden wir die Fensterfunktion LAG(), um die Einnahmen des Vortages zu erhalten, dann erhalten wir die Abweichung der Einnahmen zwischen gestern und heute und die prozentuale Abweichung. Das Ergebnis ist:

Beispielabfrage Nr. 7: Gleitender Durchschnitt

Gleitende Durchschnitte sind eine Metrik, die häufig verwendet wird, um kurzfristige Schwankungen auszugleichen. Mehr dazu erfahren Sie in dem Artikel Was ein gleitender Durchschnitt ist und wie man ihn in SQL berechnet; sehen wir uns an, wie wir einen solchen mit der OVER() Klausel erstellen können.

Nehmen wir an, der Restaurantbesitzer möchte wissen, wie viel er im Durchschnitt pro Person an dem zuletzt bedienten Tisch bezahlt hat. Er verwendet diesen Durchschnittswert als Kennzahl, um zu wissen, wie viel Geld die Gäste zahlen, und um einige Werbeaktionen oder Rabatte zu aktivieren. Wir können diesen Durchschnittswert leicht mit dem Ausdruck amount_payment/total_diners berechnen. Der Eigentümer hat jedoch festgestellt, dass diese Kennzahl starken Schwankungen unterliegt, weshalb er beschlossen hat, den durchschnittlichen Betrag zu verwenden, der pro Person an den letzten 3 und 6 bedienten Tischen bezahlt wurde. Die Abfrage zur Berechnung dieses Berichts lautet:

SELECT start_date AS "date",
start_time AS "time",
table_number,
amount_payment AS total_amount,
total_diners,
       	ROUND(amount_payment/total_diners,2) AS diner_avg,
ROUND(AVG(amount_payment/total_diners) 
     OVER (ORDER BY start_date, start_time ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),2) 
     AS diner_moving_avg_last_3_tables_served,
	ROUND(AVG(amount_payment/total_diners) 
     OVER (ORDER BY start_date, start_time ROWS BETWEEN 5 PRECEDING AND CURRENT ROW),2) 
     AS diner_moving_avg_last_6_tables_served
FROM   restaurant_activity

Die vorherige Abfrage berechnet 3 verschiedene Durchschnittswerte. Der erste ist ein einfacher Durchschnitt auf der Grundlage des Ausdrucks:

ROUND(amount_payment/total_diners,2)

Der zweite Durchschnitt ist der gleitende Durchschnitt für die letzten 3 bedienten Tische; der dritte Durchschnitt ist genau derselbe, aber für die letzten 6 bedienten Tische:

ROUND(AVG(amount_payment/total_diners) 
     OVER (ORDER BY start_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),2)
…
OVER (ORDER BY start_date, start_time ROWS BETWEEN 5 PRECEDING AND CURRENT ROW),2) 

Der Ausdruck "ROWS BETWEEN 2 PRECEDING AND CURRENT ROW" gibt an, dass der Durchschnitt auf der Grundlage von 3 Zeilen berechnet wird: der aktuellen Zeile und den beiden unmittelbar vorangehenden Zeilen. Das Fenster ist nach der Anfangszeit des Tisches geordnet. Hier ist das Ergebnis:

Andere geschäftliche Verwendungszwecke der OVER() -Klausel umfassen die Berechnung laufender Summen (nützlich in allen Arten von Finanzanalyseszenarien) und die Berechnung der Länge einer Datenreihe.

Sind Sie bereit, die SQL-Klausel OVER() zu üben?

Wir haben mehrere Abfragen mit der SQL-Klausel OVER() demonstriert. Da OVER() in Kombination mit einer Fensterfunktion verwendet werden muss, haben wir auch einige dieser Funktionen behandelt: SUM(), AVG(), LAG() und RANK().

Wenn Sie Ihr neues Wissen über die SQL-Klausel OVER() in die Praxis umsetzen möchten, empfehle ich Ihnen unseren interaktiven KursFensterfunktionen , gefolgt von unserem Fensterfunktionen Practice Set. Sie können über den Kurs im Artikel SQL-Kurs des Monats - Fensterfunktionen lesen. Sie können auch eine Kopie unseres kostenlosen SQL Fensterfunktionen Cheat Sheets erhalten, das Ihnen beim Lernen hilft. Entwickeln Sie Ihre Fähigkeiten und steigern Sie Ihr Vermögen!