Swipe um das Menü anzuzeigen

Ein kurzer Überblick über die wichtigsten Typen von Window-Funktionen, die in SQL verwendet werden.

Aggregatfunktionen

Dies sind die Standard-Aggregatfunktionen (AVG, SUM, MAX, MIN, COUNT), die im Kontext eines Fensters verwendet werden. Wir haben diesen Typ von Window-Funktion bereits im vorherigen Kapitel verwendet.

Ranking-Funktionen

Ranking-Funktionen in SQL sind eine Art von Window-Funktion, mit der jeder Zeile innerhalb einer Partition eines Ergebnismenge ein Rang zugewiesen werden kann. Diese Funktionen sind äußerst nützlich für geordnete Berechnungen und Analysen.

RANK(): weist jeder unterschiedlichen Zeile innerhalb der Partition basierend auf der ORDER BY-Klausel einen eindeutigen Rang zu. Zeilen mit gleichen Werten erhalten denselben Rang, wobei Lücken in der Rangfolge entstehen;
DENSE_RANK(): ähnlich wie RANK(), jedoch ohne Lücken in der Rangfolge;
NTILE(n): teilt die Zeilen in einer geordneten Partition in n Gruppen und weist jeder Zeile eine Gruppennummer zu.

Beispiel

Wir ordnen die Verkäufe basierend auf dem Amount für jede ProductID in aufsteigender Reihenfolge mithilfe der Funktion DENSE_RANK():


              12345678
            
SELECT 
    sales_id,
    product_id,
    sales_date,
    amount,
    DENSE_RANK() OVER (PARTITION BY product_id ORDER BY amount DESC) AS dense_rank_amount
FROM 
    Sales;

Die Ergebnistabelle enthält alle Informationen aus der Haupttabelle und eine zusätzliche Spalte, die den Rang jedes Verkaufs für das jeweilige Produkt angibt.

Wertvergleichsfunktionen

Wertvergleichs-Window-Funktionen in SQL werden verwendet, um Werte der aktuellen Zeile mit Werten anderer Zeilen innerhalb derselben Partition zu vergleichen.
Diese Funktionen sind besonders nützlich für Aufgaben, bei denen Trends analysiert, Berechnungen auf Basis benachbarter Zeilen durchgeführt oder bestimmte Zeilenwerte innerhalb eines definierten Fensters abgerufen werden. Es gibt mehrere Wertvergleichsfunktionen in SQL:

LAG() : ruft den Wert aus einer vorherigen Zeile im Ergebnismenge ab, ohne dass ein Self-Join erforderlich ist;
LEAD(): ruft den Wert aus einer nachfolgenden Zeile im Ergebnismenge ab, ohne dass ein Self-Join erforderlich ist;
FIRST_VALUE(): gibt den Wert der ersten Zeile im Fensterrahmen zurück;
LAST_VALUE(): gibt den Wert der letzten Zeile im Fensterrahmen zurück.

Beispiel

Wir verwenden die Wertvergleichs-Window-Funktion LAG(), um die Änderung des Verkaufsbetrags gegenüber dem vorherigen Verkauf für jedes Produkt zu berechnen:


              1234567891011
            
SELECT
    sales_id,
    product_id,
    sales_date,
    amount,
    LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sales_date) AS previous_amount,
    amount - LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sales_date) AS amount_change
FROM
    Sales
ORDER BY
    product_id, sales_date;

Abfragebeschreibung

Hauptbestandteile der Abfrage:

SELECT-Klausel: Gibt die Spalten an, die aus der Tabelle abgerufen werden sollen.
- Spaltennamen: Enthält Spalten wie SalesID, ProductID, SalesDate und Amount, um die relevanten Daten für jeden Verkauf anzuzeigen.
- Window-Funktion: Verwendet die Funktion LAG(), um den Wert der vorherigen Zeile für eine bestimmte Spalte innerhalb einer Partition abzurufen. Eine zusätzliche berechnete Spalte zeigt die Differenz zwischen dem aktuellen und dem vorherigen Wert an.

Syntax der Window-Funktion:

LAG(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY order_column)
- column_name: Die Spalte, aus der der vorherige Wert abgerufen werden soll.
- offset: Die Anzahl der Zeilen zurück von der aktuellen Zeile, von der der Wert abgerufen werden soll (Standard ist 1).
- default_value: Der Wert, der zurückgegeben wird, wenn der Offset außerhalb der Partition liegt (optional).
- PARTITION BY partition_column: Teilt das Ergebnis in Partitionen, auf die die Window-Funktion angewendet wird, sodass die Funktion innerhalb jeder Partition separat arbeitet.
- ORDER BY order_column: Gibt die Reihenfolge der Zeilen innerhalb jeder Partition an, sodass die Window-Funktion die Zeilen in einer sinnvollen Reihenfolge verarbeitet.

Dadurch können wir einfach Informationen über Verkaufsdifferenzen für jedes einzelne Produkt extrahieren, ohne Unterabfragen oder gespeicherte Prozeduren zu verwenden.
Wir können die Differenzen auch für alle Verkäufe ohne Partitionierung mit der folgenden Abfrage berechnen:


              123456789
            
SELECT
    sales_id,
    product_id,
    sales_date,
    amount,
    LAG(amount, 1) OVER (ORDER BY sales_date) AS previous_amount,
    amount - LAG(amount, 1) OVER (ORDER BY sales_date) AS amount_change
FROM
    Sales;

Hier siehst du, dass wir die PARTITION BY-Klausel im OVER-Block nicht verwendet haben. Das bedeutet, dass wir die vorherigen Werte nicht nur für ein bestimmtes Produkt, sondern für alle Verkäufe in der Tabelle erhalten möchten.

War alles klar?

Danke für Ihr Feedback!

Abschnitt 3. Kapitel 3

Fragen Sie AI

Fragen Sie alles oder probieren Sie eine der vorgeschlagenen Fragen, um unser Gespräch zu beginnen

Arten von Window-Funktionen

Ein kurzer Überblick über die wichtigsten Typen von Window-Funktionen, die in SQL verwendet werden.

Aggregatfunktionen

Ranking-Funktionen

RANK(): weist jeder unterschiedlichen Zeile innerhalb der Partition basierend auf der ORDER BY-Klausel einen eindeutigen Rang zu. Zeilen mit gleichen Werten erhalten denselben Rang, wobei Lücken in der Rangfolge entstehen;
DENSE_RANK(): ähnlich wie RANK(), jedoch ohne Lücken in der Rangfolge;
NTILE(n): teilt die Zeilen in einer geordneten Partition in n Gruppen und weist jeder Zeile eine Gruppennummer zu.

Beispiel

Wir ordnen die Verkäufe basierend auf dem Amount für jede ProductID in aufsteigender Reihenfolge mithilfe der Funktion DENSE_RANK():


              12345678
            
SELECT 
    sales_id,
    product_id,
    sales_date,
    amount,
    DENSE_RANK() OVER (PARTITION BY product_id ORDER BY amount DESC) AS dense_rank_amount
FROM 
    Sales;

Die Ergebnistabelle enthält alle Informationen aus der Haupttabelle und eine zusätzliche Spalte, die den Rang jedes Verkaufs für das jeweilige Produkt angibt.

Wertvergleichsfunktionen

LAG() : ruft den Wert aus einer vorherigen Zeile im Ergebnismenge ab, ohne dass ein Self-Join erforderlich ist;
LEAD(): ruft den Wert aus einer nachfolgenden Zeile im Ergebnismenge ab, ohne dass ein Self-Join erforderlich ist;
FIRST_VALUE(): gibt den Wert der ersten Zeile im Fensterrahmen zurück;
LAST_VALUE(): gibt den Wert der letzten Zeile im Fensterrahmen zurück.

Beispiel

Wir verwenden die Wertvergleichs-Window-Funktion LAG(), um die Änderung des Verkaufsbetrags gegenüber dem vorherigen Verkauf für jedes Produkt zu berechnen:


              1234567891011
            
SELECT
    sales_id,
    product_id,
    sales_date,
    amount,
    LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sales_date) AS previous_amount,
    amount - LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sales_date) AS amount_change
FROM
    Sales
ORDER BY
    product_id, sales_date;

Abfragebeschreibung

Hauptbestandteile der Abfrage:

SELECT-Klausel: Gibt die Spalten an, die aus der Tabelle abgerufen werden sollen.
- Spaltennamen: Enthält Spalten wie SalesID, ProductID, SalesDate und Amount, um die relevanten Daten für jeden Verkauf anzuzeigen.
- Window-Funktion: Verwendet die Funktion LAG(), um den Wert der vorherigen Zeile für eine bestimmte Spalte innerhalb einer Partition abzurufen. Eine zusätzliche berechnete Spalte zeigt die Differenz zwischen dem aktuellen und dem vorherigen Wert an.

Syntax der Window-Funktion:

LAG(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY order_column)
- column_name: Die Spalte, aus der der vorherige Wert abgerufen werden soll.
- offset: Die Anzahl der Zeilen zurück von der aktuellen Zeile, von der der Wert abgerufen werden soll (Standard ist 1).
- default_value: Der Wert, der zurückgegeben wird, wenn der Offset außerhalb der Partition liegt (optional).
- PARTITION BY partition_column: Teilt das Ergebnis in Partitionen, auf die die Window-Funktion angewendet wird, sodass die Funktion innerhalb jeder Partition separat arbeitet.
- ORDER BY order_column: Gibt die Reihenfolge der Zeilen innerhalb jeder Partition an, sodass die Window-Funktion die Zeilen in einer sinnvollen Reihenfolge verarbeitet.


              123456789
            
SELECT
    sales_id,
    product_id,
    sales_date,
    amount,
    LAG(amount, 1) OVER (ORDER BY sales_date) AS previous_amount,
    amount - LAG(amount, 1) OVER (ORDER BY sales_date) AS amount_change
FROM
    Sales;

War alles klar?

Danke für Ihr Feedback!

Abschnitt 3. Kapitel 3