Die ORDER BY-Klausel in SQL-Fensterfunktionen

In diesem Artikel erhalten Sie einen Überblick über ORDER BY in Fensterfunktionen. Sie erfahren, wie es sich von PARTITION BY und dem regulären ORDER BY unterscheidet, begleitet von Beispielen und Erklärungen.

Wenn Sie Berichte erstellen möchten, die über einfache Aggregationen hinausgehen, benötigen Sie SQL-Fensterfunktionen. Mit Fensterfunktionen können Sie Ranglisten erstellen, laufende Summen und gleitende Durchschnitte berechnen und die Differenz zwischen Zeilen ermitteln. Um Fensterfunktionen effektiv zu nutzen, müssen Sie die Rolle der ORDER BY Klausel verstehen. Sie verändert nicht nur das Verhalten von Fensterfunktionen, sondern einige Fensterfunktionen laufen ohne ORDER BY gar nicht.

Dieser Artikel richtet sich an diejenigen, die bereits ein allgemeines Verständnis von Window-Funktionen haben. Wenn Sie mit ihnen nicht vertraut sind, empfehle ich Ihnen dringend, an unserem Fensterfunktionen Kurs. Er vermittelt Ihnen umfassende Kenntnisse über Fensterfunktionen, einschließlich Ranking-Funktionen, Analysefunktionen und die Klauseln ORDER BY und PARTITION BY. Der Kurs enthält 218 Codierungsaufgaben, die darauf warten, gelöst zu werden; weitere finden Sie im Fensterfunktionen Practice Set.

Auch wenn Sie SQL-Fensterfunktionen kennen, sollten Sie unser Fensterfunktionen Cheat Sheet zum schnellen Nachschlagen bereithalten.

Was sind Fensterfunktionen?

Fensterfunktionen in SQL führen Operationen auf einem Fensterrahmen aus, der aus der aktuellen Zeile und den mit ihr verbundenen Zeilen besteht. Im Gegensatz zu Aggregatfunktionen werden bei Fensterfunktionen keine einzelnen Zeilen komprimiert; stattdessen wird jeder Zeile eine Spalte mit dem Funktionsergebnis hinzugefügt. Das bedeutet, dass Fensterfunktionen es Ihnen ermöglichen, einzelne und aggregierte Daten gleichzeitig zu sehen.

Syntax

Die Syntax der SQL-Fensterfunktionen ist im Folgenden dargestellt:

window_function OVER ([PARTITION BY column_name] [ORDER BY column_name ASC|DESC])

Hier eine kurze Beschreibung der einzelnen Teile der Syntax:

• window_function: Die Fensterfunktion, die Sie verwenden möchten.
• OVER(): Eine obligatorische Klausel für die Erstellung einer Fensterfunktion.
• PARTITION BY: Eine optionale Klausel, die die Daten partitioniert (unterteilt).
• ORDER BY: Eine optionale Klausel, die die Daten innerhalb des Fensterrahmens sortiert.

Was ist die ORDER BY-Klausel in Fensterfunktionen?

ORDER BY Die ORDER BY-Klausel (zusammen mit PARTITION BY) ist ein grundlegender Bestandteil vieler Fensterfunktionen. ORDER BY in der Fensterfunktion sortiert die Zeilen innerhalb des Fensterrahmens. Sie definiert die Reihenfolge, in der die Berechnungen der Fensterfunktion durchgeführt werden.

ORDER BY kann Daten innerhalb eines Fensters aufsteigend (A bis Z, 1 bis 10) oder absteigend (Z bis A, 10 bis 1) sortieren. Sie können Textdaten in alphabetischer oder umgekehrt alphabetischer Reihenfolge, numerische Daten vom niedrigsten zum höchsten Wert (oder umgekehrt) und Datums-/Zeitdaten vom ältesten zum neuesten Wert (oder vom neuesten zum ältesten) sortieren.

Beispiel: ORDER BY mit einer Ranking Window Funktion

Schauen wir uns ein Beispiel dafür an, wie ORDER BY in Fensterfunktionen die Abfrageausführung beeinflusst.

Die folgende Abfrage ordnet die Daten in der Tabelle product_sales nach Umsätzen in absteigender Reihenfolge - d.h. vom höchsten zum niedrigsten Umsatz.

SELECT 
  id,
  date,
  sales,
  product_name,
  DENSE_RANK() OVER(ORDER BY sales DESC) AS ranking
FROM product_sales; 

DENSE_RANK() ist eine der Ranking-Fensterfunktionen. Sie ordnet den gesamten Datensatz von den höchsten bis zu den niedrigsten Umsätzen, die in ORDER BY angegeben sind.

Die ursprüngliche product_sales Tabelle sieht wie folgt aus:

Daher ordnet der obige Code die Zeilen im Datensatz absteigend: vom höchsten zum niedrigsten Umsatz.

Dies ist die Ausgabe der Abfrage, wobei die Ränge in einer separaten Spalte angezeigt werden:

Die Rangfolge würde sich deutlich ändern, wenn Sie in ORDER BY DESC durch ASC ersetzen würden, wie unten gezeigt:

SELECT 
  id,
  date,
  sales,
  product_name,
  DENSE_RANK() OVER(ORDER BY sales ASC) AS ranking
FROM product_sales; 

Jetzt wird die Rangfolge vom niedrigsten zum höchsten Umsatz durchgeführt:

ORDER BY und PARTITION BY in Fensterfunktionen

Wir wissen, was ORDER BY in den Fensterfunktionen macht. Was ist mit PARTITION BY? Hierbei handelt es sich um eine optionale Klausel, mit der die Daten auf der Grundlage einer oder mehrerer Kategorien in Teilmengen aufgeteilt werden. Dies geschieht durch die Angabe von Spalten in der PARTITION BY -Klausel wie folgt: PARTITION BY product_name. Wie das funktioniert, werde ich im folgenden Abschnitt zeigen.

Ohne PARTITION BY sortiert ORDER BY die Daten in der gesamten Ergebnismenge. Wenn Sie die Klausel jedoch zusammen mit PARTITION BY verwenden, werden die Daten in jeder Partition separat sortiert.

Beispiel: Verwendung von ORDER BY mit PARTITION BY in einer Fensterfunktion

Bei Verwendung mit PARTITION BY sortiert ORDER BY die Daten innerhalb jeder Partition.

Die Verwendung von ORDER BY mit der Fensterfunktion SUM() ergibt eine kumulative Summe (d. h. die Summe des Wertes der aktuellen Zeile plus aller Zeilen vor ihr in der Partition). Der folgende Code berechnet beispielsweise die kumulative Summe der Verkäufe nach Produktnamen (wie in PARTITION BY angegeben) vom ältesten bis zum neuesten Datum (wie in ORDER BY angegeben).

SELECT 
  id,
  date,
  sales,
  product_name,
  SUM(sales) OVER(PARTITION BY product_name ORDER BY date ASC) AS cumulative_sum
FROM product_sales; 

In der Abfrage wird der Datensatz zunächst in Partitionen nach Produktnamen aufgeteilt. Dann werden die Werte innerhalb jeder Partition nach dem ältesten bis zum neuesten Datum sortiert, das in ORDER BY angegeben ist. Dann berechnet die Funktion SUM() window die kumulative Gesamtsumme, indem die Verkäufe des aktuellen Datums mit den Verkäufen aller vorherigen Verkäufe innerhalb der Partition addiert werden.

Hier ist die Ausgabe, die jede Zeile und den kumulierten Umsatz für jedes Produkt zeigt:

Wenn wir ORDER BY aus dem Code entfernen, wie unten gezeigt ...

SELECT 
  id,
  date,
  sales,
  product_name,
  SUM(sales) OVER(PARTITION BY product_name) AS cumulative_sum
FROM product_sales; 

... wird der Code die Gesamtsumme der Verkäufe für jedes Produkt ausgeben. Mit anderen Worten, wenn Sie ORDER BY einfach aus einer Fensterfunktion weglassen, verlieren Sie die Möglichkeit, kumulativ zu berechnen. Ohne ORDER BY bilden alle Zeilen in der Partition den Fensterrahmen.

ORDER BY in Fensterfunktionen vs. Reguläres ORDER BY

Wenn ich regulär sage, meine ich den Standard ORDER BY am Ende der Abfrage. Wie unterscheidet es sich von ORDER BY in einer Fensterfunktion?

Ein reguläres ORDER BY sortiert eine Abfrageausgabe, während ORDER BY in Fensterfunktionen ein Datenfenster oder eine Datenpartition sortiert.

Beispiel: ORDER BY in Fensterfunktionen und reguläres ORDER BY

Diese Abfrage verwendet die Fensterfunktion DENSE_RANK() mit PARTITION BY und ORDER BY, um die Verkaufsdaten für jedes Produkt zu ordnen:

SELECT 
  id,
  date,
  sales,
  product_name,
  DENSE_RANK() OVER(PARTITION BY product_name ORDER BY sales DESC) AS day_rank
FROM product_sales;

Sie können sehen, dass die Gesamtausgabe nicht nach dem höchsten bis zum niedrigsten Umsatz sortiert ist. Die absteigende Sortierung der Daten wird nur innerhalb jeder Partition angewendet.

Wenn Sie möchten, dass Ihre Ausgabe absteigend nach Verkäufen sortiert wird, müssen Sie das explizit tun, indem Sie ORDER BY am Ende der Abfrage hinzufügen:

SELECT 
  id,
  date,
  sales,
  product_name,
  DENSE_RANK() OVER(PARTITION BY product_name ORDER BY sales DESC) AS sales_rank_by_product
FROM product_sales
ORDER BY sales DESC;

Jetzt erhalten Sie die Ausgabe nach Umsatz und Datum sortiert. Sie können sehen, wie der Verkaufsrang eines Datums im Vergleich zu den Verkäufen desselben Produkts an anderen Daten ist.

Fensterfunktionen Das erfordert ORDER BY

Wie Sie im vorherigen Beispiel gesehen haben, können Fensterfunktionen auch ohne ORDER BY funktionieren. ORDER BY wird im Allgemeinen als optionale Klausel in Fensterfunktionen betrachtet.

Aber auch wenn ORDER BY in Fensterfunktionen als optionale Klausel betrachtet wird, erfordern einige Fensterfunktionen ORDER BY, um überhaupt zu funktionieren. In diesen Fällen wird ORDER BY obligatorisch; diese Fensterfunktionen benötigen eine Sortierreihenfolge, um korrekt zu funktionieren. Diese Fensterfunktionen sind:

• RANK()
• DENSE_RANK()
• NTILE()
• LEAD()
• LAG()

Standard-Fensterrahmen mit und ohne ORDER BY

Das Verhalten einer Fensterfunktion ändert sich je nachdem, ob sie mit oder ohne ORDER BY geschrieben wurde. Genauer gesagt, hat das Vorhandensein oder Fehlen von ORDER BY Auswirkungen auf den Standard-Fensterrahmen.

Wenn ORDER BYnicht vorhanden ist, umfasst der Standardfensterrahmen die aktuelle Zeile und alle davor und danach liegenden Zeilen. Mit anderen Worten: Alle Zeilen der Partition werden einbezogen. Wir haben dies am Beispiel der kumulativen Summe gesehen: Ohne ORDER BY wird der Fensterrahmen standardmäßig auf die gesamte Partition angewendet und die Summe wird zur Gesamtsumme.

Wenn ORDER BY vorhanden ist, schließt der Fensterrahmen den aktuellen und alle vorhergehenden Werte ein. Wir haben dies am Beispiel der kumulativen Summe gesehen: Mit ORDER BY schließt der Fensterrahmen alle Zeilen vor der aktuellen Zeile und die aktuelle Zeile ein.

Wenn Ihnen der Standard-Fensterrahmen nicht gefällt, können Sie ihn mit den Schlüsselwörtern ROWS und RANGE explizit festlegen.

Verwendung von ORDER BY in Fensterfunktionen

Da haben Sie es: ORDER BY - manchmal obligatorisch, manchmal optional - ist die Klausel, die Fensterfunktionen ausmacht und gelegentlich auch zerstört.

Aber alles Wissen über ORDER BY in Fensterfunktionen ist nichts wert, wenn Sie es nicht in Ihren Abfragen verwenden können. Testen Sie also, was Sie hier gelernt haben, indem Sie die Programmieraufgaben in unserem Kurs Window-Funktionen und dem Fensterfunktionen Practice Set lösen.

Für noch mehr Übungen, lösen Sie diese 11 Fensterfunktionsübungen und beantworten Sie die 10 wichtigsten Fensterfunktions-Interviewfragen. Viel Spaß beim Lernen!