Verwenden der Fensterfunktionen

Fensterfunktionen werden auf Fenster angewendet, bei denen es sich um Gruppen von Zeilen handelt, die in Beziehung zueinander stehen (z. B. nach Datum oder nach Ort). Unter diesem Thema wird beschrieben, wie Sie die verschiedenen von Snowflake unterstützten Fenstertypen verwenden.

  • Allgemeine Fensterfunktionen

  • Fensterfunktionen, die den Rang berechnen (z. B. höchster, zweithöchster usw.)

  • Fensterfunktionen, die kumulative Fensterrahmen und gleitende Fensterrahmen unterstützen

Dieses Dokument richtet sich an Leser, die im Umgang mit Fensterfunktionen noch unsicher sind. Leser, die diese Funktionen bereits beherrschen, finden das Referenzmaterial möglicherweise ausreichend:

Unter diesem Thema:

Fensterkonzepte

Ein Fenster ist eine Gruppe von Zeilen. Ein Fenster kann 0, 1 oder mehrere Zeilen enthalten. Der Einfachheit halber sagen wir jedoch normalerweise nur, dass ein Fenster „mehrere Zeilen“ enthält.

Alle Zeilen in einem Fenster sind in irgendeiner Weise verknüpft, z. B. nach Standort (z. B. alle aus derselben Stadt) oder nach Zeit (z. B. alle aus demselben Geschäftsjahr).

Eine Funktion, die ein Fenster verwendet, ist eine Fensterfunktion.

Als Fensterfunktionen kategorisierte Funktionen helfen bei der Beantwortung anderer Arten von Fragen als Skalarfunktionen.

  • Eine Abfrage mit einer Skalarfunktion beantwortet Fragen zu einer einzelnen Zeile, wobei nur die Daten in dieser Zeile verwendet werden.

  • Eine Abfrage mit einer Fensterfunktion beantwortet Fragen zur Beziehung einer Zeile zu anderen Zeilen im selben Fenster.

Angenommen, Sie verwalten die Filiale einer Kette von fünf Filialen. Um den Gewinn Ihres Geschäfts zu berechnen, müssen bei der Berechnung nur Informationen zu Ihrem Geschäft berücksichtigt werden, z. B. Umsatz und Kosten des Geschäfts. Sie würden für diese Abfrage eine Skalarfunktion verwenden.

Um den Gewinn Ihres Geschäfts im Vergleich zu anderen Geschäften zu berechnen, müssen bei der Berechnung nicht nur Informationen zu Ihrem Geschäft, sondern auch zu anderen Geschäften berücksichtigt werden. Für diese Abfrage würden Sie eine Fensterfunktion verwenden.

Sie können sich eine Fensterfunktion wie zwei Argumente vorstellen: Das erste Argument ist die Spalte oder der Ausdruck, die bzw. der für die Berechnung verwendet werden soll, z. B. Umsatz oder Gewinn. Das zweite Argument definiert das Fenster (d. h. die Gruppe von Zeilen, die für den Vergleich verwendet werden). Das Fenster enthält sowohl die aktuelle Zeile (Ihr Geschäft) als auch die anderen Zeilen, mit denen es verglichen werden soll (andere Geschäfte derselben Kette).

Um den Prozentsatz Ihres Geschäfts am Gewinn der gesamten Kette zu berechnen, dividieren Sie den Gewinn Ihres Geschäfts durch den Gesamtgewinn aller Geschäfte (Filialprofit/Kettenprofit).

Unter diesem Thema wird erläutert, wie Sie den Prozentsatz berechnen, den Ihre Filiale zum Unternehmensgewinn beiträgt – mit und ohne Fensterfunktion.

Das folgende Beispiel zeigt eine Möglichkeit, den Prozentsatz Ihres Geschäfts am Gewinn der Kette ohne Verwendung einer Fensterfunktion zu berechnen.

Beginnen Sie, indem Sie die Tabelle erstellen, die Daten laden und den Gewinn jedes Geschäfts berechnen.

CREATE TRANSIENT TABLE store_sales (
    branch_ID    INTEGER,
    city        VARCHAR,
    gross_sales NUMERIC(9, 2),
    gross_costs NUMERIC(9, 2),
    net_profit  NUMERIC(9, 2)
    );

INSERT INTO store_sales (branch_ID, city, gross_sales, gross_costs)
    VALUES
    (1, 'Vancouver', 110000, 100000),
    (2, 'Vancouver', 140000, 125000),
    (3, 'Montreal', 150000, 140000),
    (4, 'Montreal', 155000, 146000);

UPDATE store_sales SET net_profit = gross_sales - gross_costs;

Zeigen Sie nun den Prozentsatz jedes Geschäfts am Gesamtgewinn der Kette an:

SELECT branch_ID,
       net_profit AS store_profit,
       (SELECT SUM(net_profit) FROM store_sales) AS chain_profit,
       store_profit / chain_profit * 100 AS store_percentage_of_chain_profit
     FROM store_sales
     ORDER BY branch_ID;
+-----------+--------------+--------------+----------------------------------+
| BRANCH_ID | STORE_PROFIT | CHAIN_PROFIT | STORE_PERCENTAGE_OF_CHAIN_PROFIT |
|-----------+--------------+--------------+----------------------------------|
|         1 |     10000.00 |     44000.00 |                      22.72727300 |
|         2 |     15000.00 |     44000.00 |                      34.09090900 |
|         3 |     10000.00 |     44000.00 |                      22.72727300 |
|         4 |      9000.00 |     44000.00 |                      20.45454500 |
+-----------+--------------+--------------+----------------------------------+

Wenn Sie einen detaillierteren Bericht wünschen, in dem der Gewinn jedes Geschäfts als Prozentsatz aller Geschäfte in dieser Stadt angezeigt wird, verwenden Sie die folgende Abfrage:

SELECT branch_ID,
       net_profit AS store_profit,
       (SELECT SUM(net_profit) FROM store_sales AS s2 WHERE s2.city = s1.city) AS city_profit,
       store_profit / city_profit * 100 AS store_percentage_of_city_profit
    FROM store_sales AS s1
    ORDER BY branch_ID;
+-----------+--------------+-------------+---------------------------------+
| BRANCH_ID | STORE_PROFIT | CITY_PROFIT | STORE_PERCENTAGE_OF_CITY_PROFIT |
|-----------+--------------+-------------+---------------------------------|
|         1 |     10000.00 |    25000.00 |                     40.00000000 |
|         2 |     15000.00 |    25000.00 |                     60.00000000 |
|         3 |     10000.00 |    19000.00 |                     52.63157900 |
|         4 |      9000.00 |    19000.00 |                     47.36842100 |
+-----------+--------------+-------------+---------------------------------+

Es wäre gut, eine Funktion zu haben, die in etwa das Gleiche tut und den Gewinn Ihres Geschäfts durch die Summe des Gewinns aller Geschäfte (oder durch die Summe des Gewinns einer bestimmten Gruppe von Geschäften, zum Beispiel aller Geschäfte in derselben Stadt) dividiert. Eine solche Funktion könnte zwei Argumente haben, von denen eines die zu berechnende Spalte angibt und das andere angibt, mit welchen Zeilen verglichen werden soll. Die zweite Spalte könnte einer WHERE-Klausel ähneln. Sie können diese Funktion auf ähnliche Weise wie folgt verwenden (dies ist Pseudocode, keine gültige SQL-Syntax):

SELECT branch_ID,
       PERCENTAGE(net_profit, <where_condition>)
    FROM store_sales;

Diese Funktion würde den Gewinn der aktuellen Zeile (des aktuellen Geschäfts) durch die Summe der Gewinne aller Geschäfte dividieren, die der <WHERE-Bedingung> entsprechen.

Wenn beispielsweise der Prozentsatz des Gewinns für jedes Geschäft in jeder Stadt berechnet werden soll, sieht der Pseudocode folgendermaßen aus:

SELECT branch_ID,
       PERCENTAGE(net_profit, 'city')
     FROM store_sales;

SQL unterstützt nicht die oben gezeigte Syntax, aber das Konzept einer Fensterfunktion, die ein Ergebnis zurückgibt, das sowohl auf der aktuellen Zeile als auch auf einer definierten Gruppe von Zeilen basiert.

Snowflake hat keine Funktion mit dem Namen PERCENTAGE, aber eine Funktion mit dem Namen RATIO_TO_REPORT, die den Wert in der aktuellen Zeile durch die Summe der Werte in allen Zeilen eines Fensters dividiert. Hier ist das Äquivalent der vorhergehenden Abfrage.

SELECT branch_ID,
       city,
       100 * RATIO_TO_REPORT(net_profit) OVER (PARTITION BY city)
    FROM store_sales AS s1
    ORDER BY city, branch_ID;
+-----------+-----------+------------------------------------------------------------+
| BRANCH_ID | CITY      | 100 * RATIO_TO_REPORT(NET_PROFIT) OVER (PARTITION BY CITY) |
|-----------+-----------+------------------------------------------------------------|
|         3 | Montreal  |                                                52.63157900 |
|         4 | Montreal  |                                                47.36842100 |
|         1 | Vancouver |                                                40.00000000 |
|         2 | Vancouver |                                                60.00000000 |
+-----------+-----------+------------------------------------------------------------+

Die OVER()-Klausel definiert die für die Berechnung verwendete Zeilengruppe. Sie übernimmt dieselbe Funktion wie das hypothetische zweite Argument (die <WHERE-Bedingung) in der PERCENTAGE-Funktion oben.

Mit der Unterklausel PARTITION BY können wir dieses Fenster in Unterfenster aufteilen, in diesem Fall eines pro Stadt. (Wenn die Anweisung nicht explizit eine PARTITION BY-Klausel verwendet, wird die Fensterfunktion für die gesamte Eingabe als ein einzelnes Fenster ausgeführt.)

Wenn Sie den prozentualen Gewinn bezogen auf die gesamte Kette und nicht nur die Geschäfte in einer bestimmten Stadt anzeigen möchten, lassen Sie die PARTITION BY-Klausel weg:

SELECT branch_ID,
       100 * RATIO_TO_REPORT(net_profit) OVER ()
    FROM store_sales AS s1
    ORDER BY branch_ID;
+-----------+-------------------------------------------+
| BRANCH_ID | 100 * RATIO_TO_REPORT(NET_PROFIT) OVER () |
|-----------+-------------------------------------------|
|         1 |                               22.72727300 |
|         2 |                               34.09090900 |
|         3 |                               22.72727300 |
|         4 |                               20.45454500 |
+-----------+-------------------------------------------+

Reihenfolge-abhängige Fensterfunktionen

Das Ergebnis der vorherigen Abfrage hängt nicht von der Reihenfolge der Zeilen ab, die in der Unterklausel PARTITION BY ausgewählt wurden. Der Prozentsatz Ihres Geschäfts an Unternehmensgewinnen hängt nicht von der Reihenfolge ab, in der die Abfrage die Daten der anderen Geschäfte liest.

Einige Abfragen sind jedoch Reihenfolge-abhängig. Es gibt zwei Arten von Reihenfolge-abhängigen Fensterfunktionen:

  • Rangbezogene Fensterfunktionen

  • Fensterrahmenfunktionen

Einige Funktionen sind sowohl rangbezogene Funktionen als auch Fensterrahmenfunktionen.

Für Reihenfolge-abhängige Funktionen wird die Unterklausel ORDER BY der Klausel OVER verwendet. Beispiel:

select order_sensitive_function(column_name) over ( [partition by <col2>] order by <col3> )
...

Die ORDER BY-Klausel kann mit ASC (aufsteigend) oder DESC (absteigend) verwendet werden. Der Standardwert ist aufsteigend.

(Die Unterklausel ORDER BY der Klausel OVER unterscheidet sich von der Klausel ORDER BY, die die endgültige Ausgabe einer Abfrage sortiert.)

Fensterrahmenfunktionen

Ein Fensterrahmen enthält eine Teilmenge der Zeilen eines Fensters.

Eine Fensterrahmenfunktion verwendet einen Fensterrahmen, um beispielsweise einen gleitenden Durchschnitt zu berechnen.

Snowflake unterstützt zwei Arten von Fensterrahmen:

  • Kumulativ

  • Gleitend

Kumulative Fensterrahmen

Ein kumulatives Fenster ist ein Fenster mit variabler Breite, das an einem festen Punkt beginnt und mit jeder neuen Zeile wächst. Beispielsweise wird „Bisheriger Umsatz in diesem Monat“ unter Verwendung eines kumulativen Fensters berechnet, das am ersten Tag des Monats beginnt und durch Hinzufügen der Daten jedes neuen Tages erweitert wird.

+--------+-------+--------------+
| Day of | Sales | Sales So Far |
| Month  | Today | This Month   |
|--------+-------+--------------+
|      1 |    10 |           10 |
|      2 |    10 |           20 |
|      3 |    10 |           30 |
|    ... |   ... |          ... |
+--------+-------+--------------+

Häufig wird ein kumulatives Fenster in regelmäßigen Abständen von 0 aus neu gestartet. Wenn in der obigen Tabelle beispielsweise die Verkäufe für Februar angezeigt werden, wird der 1. März zum Startpunkt des Fensterrahmens, und die „Verkäufe im Monat bis jetzt“ werden auf 0 zurückgesetzt und beginnend ab 1. März wieder aufaddiert.

Gleitende Fensterrahmen

Sie können sich einen gleitenden Fensterrahmen vorstellen, indem Sie überlegen, was Sie sehen, wenn Sie aus dem Seitenfenster eines Autos schauen, während das Auto parallel zu einem Zaun fährt. Wenn die Zaunpfosten gleichmäßig verteilt sind und die Breite des Fensters ein ganzzahliges Vielfaches des Abstands zwischen den Pfosten beträgt, bleibt die Anzahl der angezeigten Zaunpfosten konstant. Wenn Sie jedoch am Zaun entlangfahren, verschwinden „alte“ Zaunpfosten aus Ihrem Blickfeld, während „neue“ Zaunpfosten ins Blickfeld geraten, sodass Sie im Lauf der Zeit nicht dieselben Zaunpfosten sehen, aber immer die gleiche Anzahl.

Ein gleitender Fensterrahmen ist ein Rahmen mit fester Breite, der entlang der Fensterzeilen „gleitet“ und Ihnen jedes Mal einen anderen Fensterausschnitt anzeigt. Wie wenn das Auto an den Zaunpfosten entlangfährt, bewegt sich der Fensterrahmen entlang der Daten, wobei alte Zeilen aus dem Rahmen verschwinden und neue Zeilen angezeigt werden, aber die Breite des Rahmens (und damit die Anzahl der Zeilen im Rahmen) immer gleich ist.

Gleitende Fenster werden häufig zur Berechnung von gleitenden Durchschnitten verwendet. Ein gleitender Durchschnitt ist ein Durchschnitt, der auf der Grundlage eines Intervalls fester Größe (z. B. Anzahl der Tage) berechnet wird. Der Durchschnitt gleitet, weil sich die tatsächlichen Werte im Intervall mit der Zeit (oder einem anderen Faktor) ändern, wenn das Fenster verschoben wird, obwohl die Größe des Intervalls konstant ist.

Beispielsweise analysieren Börsenanalysten häufig Aktien, die teilweise auf dem gleitenden 13-Wochen-Durchschnitt des Aktienkurses basieren. Der heutige gleitende Durchschnittspreis ist der Durchschnittspreis am Ende des heutigen Tages und der Preis am Ende jedes Tages in den letzten 13 Wochen. Wenn Aktien an 5 Tagen der Woche gehandelt werden und in den letzten 13 Wochen keine Feiertage verzeichnet wurden, ist der gleitende Durchschnitt der Durchschnittspreis an jedem der letzten 65 Handelstage (einschließlich des heutigen).

Das folgende Beispiel zeigt, was mit einem gleitenden 13-Wochen-Durchschnitt (91 Tage) eines Aktienkurses am letzten Juni-Tag und an den ersten Juli-Tagen passiert:

  • Am 30. Juni gibt die Funktion den Durchschnittspreis für den Zeitraum 1. April bis 30. Juni (einschließlich) zurück.

  • Am 1. Juli gibt die Funktion den Durchschnittspreis für den Zeitraum 2. April bis 1. Juli (einschließlich) zurück.

  • Am 2. Juli gibt die Funktion den Durchschnittspreis für den Zeitraum 3. April bis 2. Juli (einschließlich) zurück.

  • usw.

Bemerkung

Obwohl Schiebefenster eine feste Breite haben, verfügt die Datenquelle beim erstmaligen Anwenden eines Fensters auf eine neue Datenquelle möglicherweise nicht über genügend Daten, um das Fenster auszufüllen. Wenn zum Beispiel die Aufzeichnung erst am 1. April begonnen hätte, dann enthielte das gleitende Fenster vom 1. April bis zum 29. Juni Daten von weniger als 91 Tagen. Die Fensterbreite bleibt erst nach dem Füllen des Fensters konstant.

Im folgenden Beispiel wird ein kleines gleitendes Fenster (3 Tage) für die ersten 7 Tage im Monat verwendet. Dies zeigt, wie der Fensterrahmen über das Fenster gleitet und immer die 3 neuesten Werte zur Berechnung der Gesamtsumme innerhalb des Fensters enthält. Diese Abbildung berücksichtigt, dass das Fenster zu Beginn des Zeitraums möglicherweise nicht voll ist:

3-day sliding window frame in 7-day window

Wie Sie der entsprechenden Tabelle entnehmen können, enthält die letzte Spalte die Umsatzsumme der letzten drei Tage. So weist beispielsweise Tag 4 einen Spaltenwert von 36 auf, was die Summe der Umsätze von Tage 2, 3 und 4 (11 + 12 + 13) ist:

+--------+-------+---------------+
| Day of | Sales | Most Recent   |
| Month  | Today | 3 Days' Sales |
|--------+-------+---------------+
|      1 |    10 |            10 |
|      2 |    11 |            21 |
|      3 |    12 |            33 |
|      4 |    13 |            36 |
|      5 |    14 |            39 |
|    ... |   ... |           ... |
+--------+-------+---------------+

Weitere Informationen zu Reihenfolge-abhängigen Fensterfunktionen

Die PARTITION BY- und ORDER BY-Klauseln sind unabhängig. Sie können die ORDER BY-Klausel auch ohne PARTITION BY-Klausel verwenden. Verwenden Sie die folgende Abfrage, um Ihr Geschäft mit allen anderen Geschäften der Kette zu vergleichen und nicht nur mit anderen Filialen in Ihrer Stadt:

SELECT
    branch_ID,
    net_profit,
    RANK() OVER (ORDER BY net_profit DESC) AS sales_rank
  FROM store_sales

Bemerkung

Eine ORDER BY-Unterklausel in einer OVER-Klausel steuert nur die Reihenfolge, in der die Fensterfunktion die Zeilen verarbeitet. Die Klausel steuert nicht die Ausgabe der gesamten Abfrage. Für die Steuerung der Ausgabereihenfolge der Abfrage ist weiterhin eine äußere ORDER BY-Klausel auf oberster Ebene der Abfrage erforderlich. Diese ORDER BY-Klauseln sind unabhängig und schließen sich nicht gegenseitig aus. In der folgenden Abfrage wird die erste ORDER BY-Klausel verwendet, um die Verarbeitung durch die Fensterfunktion zu steuern, und die zweite ORDER BY-Klausel, um die Reihenfolge der Ausgabe der gesamten Abfrage zu steuern:

SELECT
    branch_ID,
    net_profit,
    RANK() OVER (ORDER BY net_profit DESC) AS sales_rank
  FROM store_sales
  ORDER BY branch_ID;

Einige Fensterfunktionen sind Reihenfolge-abhängig, andere nicht.

  • Einige Fensterfunktionen erfordern eine ORDER BY-Klausel.

  • Einige Fensterfunktionen verbieten eine ORDER BY-Klausel.

  • Einige Fensterfunktionen verwenden eine ORDER BY-Klausel, wenn eine vorhanden ist, benötigen diese jedoch nicht.

  • Einige Fensterfunktionen behandeln eine ORDER BY-Klausel als implizite kumulative Fensterrahmenklausel. (Weitere Informationen zu impliziten Fensterrahmen finden Sie unter Hinweise zur Nutzung von Fensterrahmen.)

Die folgende Übersicht zeigt die Beziehung zwischen Fensterfunktionen, Fensterrahmenfunktionen und rangbezogenen Funktionen:

                      Window Functions

  Order-insensitive Functions      Order-sensitive Functions
+-----------------------------+--------------------------------------------------------------------+
|                             | Rank-related Functions                                             |
|                             +--------------------------------------------------------------------+
|                             | Functions that can be both Rank-related and Window Frame functions |
|                             +--------------------------------------------------------------------+
|                             | Window Frame Functions                                             |
+-----------------------------+--------------------------------------------------------------------+

Wie oben gezeigt:

  • Einige Fensterfunktionen sind nicht Reihenfolge-abhängig.

  • Bestimmte Fensterfunktionen sind Reihenfolge-abhängig.

  • Die Reihenfolge-abhängigen Fensterfunktionen sind in zwei Kategorien unterteilt:

    • Rangbezogene Funktionen.

    • Fensterrahmenfunktionen

  • Einige Reihenfolge-abhängige Funktionen sind sowohl rangbezogene Funktionen als auch Fensterrahmenfunktionen.

Fensterfunktionen vs. Aggregatfunktionen

Viele Fensterfunktionen und Aggregatfunktionen haben denselben Namen. So gibt es zum Beispiel eine Fensterfunktion SUM() und eine Aggregatfunktion SUM().

Beachten Sie Folgendes, um zwischen der Verwendung der beiden zu unterscheiden:

  • Bei einer Aggregatfunktion besteht die Eingabe aus mehreren Zeilen und die Ausgabe aus der genau einer Zeile.

  • Für eine Fensterfunktion gibt es zwei Eingaben, ein Zeilenfenster und eine einzelne Zeile in diesem Fenster, und die Ausgabe ist eine Zeile pro Eingabezeile.

Eine Aggregatfunktion gibt Auskunft über alle Zeilen zusammen, wie z. B. SUM, gibt jedoch nichts über die einzelnen Zeilen zurück.

Eine Fensterfunktion informiert über die aktuelle Zeile im Verhältnis zu allen anderen Zeilen im Fenster.

Die folgenden SQL-Anweisungen zeigen den Unterschied zwischen der Verwendung der Aggregatfunktion SUM(), die genau ein Zeile für die gesamte Gruppe von Eingabezeilen zurückgibt, und der Verwendung der Fensterfunktion SUM(), die eine Zeile für jede Zeile in der Gruppe der Eingabezeilen zurückgibt:

SELECT SUM(net_profit) 
    FROM store_sales;
+-----------------+
| SUM(NET_PROFIT) |
|-----------------|
|        44000.00 |
+-----------------+
SELECT branch_ID, SUM(net_profit) OVER ()
    FROM store_sales
    ORDER BY branch_id;
+-----------+-------------------------+
| BRANCH_ID | SUM(NET_PROFIT) OVER () |
|-----------+-------------------------|
|         1 |                44000.00 |
|         2 |                44000.00 |
|         3 |                44000.00 |
|         4 |                44000.00 |
+-----------+-------------------------+

Aufrufen einer Fensterfunktion

In diesem Abschnitt finden Sie weitere Informationen zur Syntax des Aufrufs von Fensterfunktionen.

Fenster und Fensterrahmen werden mit einer OVER-Klausel angegeben:

... OVER ( [ PARTITION BY <expr1> ]
           [ ORDER BY <expr2> [ { cumulativeFrame | slidingFrame } ] ]
         ) ...

Die Syntax von Fensterfunktionen wird unter Fensterfunktionen ausführlicher behandelt. Unter diesem Thema geht es um die Teilmenge der Syntax, die von Fensterfunktionen benötigt wird, insbesondere:

  • Die Unterklausel PARTITION BY unterteilt die Daten in Fenster. Wenn Sie beispielsweise die laufenden monatlichen Umsatzsummen für mehr als einen Monat berechnen möchten, können Sie die Daten nach Monaten partitionieren. Auf diese Weise können Sie eine laufende Umsatzsumme für Januar, eine weitere laufende Umsatzsumme für Februar usw. berechnen.

  • Die Unterklausel ORDER BY ist in erster Linie für analytische Fensterfunktionen und für gleitende und kumulative Fensterrahmenfunktionen gedacht. Sie bestimmt die Reihenfolge der Zeilen in jedem Fenster.

Umfassendes Beispiel

Das folgende Beispiel verwendet ein Verkaufsszenario, um die vielen der oben beschriebenen Konzepte zu veranschaulichen:

Angenommen, Sie möchten einen Finanzbericht erstellen, der verschiedene Werte auf Basis der Umsätze der letzten Woche anzeigt:

  • Tägliche Verkäufe

  • Rangliste innerhalb der Woche (d. h. Umsätze von höchstem zu niedrigstem für die Woche)

    Hierfür wird eine rangbezogene Fensterfunktion (RANK) verwendet.

  • Bisherige Umsätze dieser Woche (d. h. die „aktuelle Summe“ für alle Tage ab Wochenbeginn bis einschließlich des aktuellen Tages)

    Dafür wird eine Fensterfunktion (SUM) mit einem kumulativen Fensterrahmen verwendet.

  • Gesamtumsatz der Woche

    Hier wird SUM als einfache Fensterfunktion verwendet.

  • 3-tägiger gleitender Durchschnitt (d. h. der Durchschnitt für den aktuellen Tag und die beiden vorherigen Tage)

    Hierfür wird AVG als Fensterfunktion mit einem gleitenden Fensterrahmen verwendet.

Der Bericht könnte ungefähr wie folgt aussehen:

+--------+-------+------+--------------+-------------+--------------+
| Day of | Sales | Rank | Sales So Far | Total Sales | 3-Day Moving |
| Week   | Today |      | This Week    | This Week   | Average      |
|--------+-------+------+--------------+-------------|--------------+
|      1 |    10 |    4 |           10 |          84 |         10.0 |
|      2 |    14 |    3 |           24 |          84 |         12.0 |
|      3 |     6 |    5 |           30 |          84 |         10.0 |
|      4 |     6 |    5 |           36 |          84 |          9.0 |
|      5 |    14 |    3 |           50 |          84 |         10.0 |
|      6 |    16 |    2 |           66 |          84 |         11.0 |
|      7 |    18 |    1 |           84 |          84 |         12.0 |
+--------+-------+------+--------------+-------------+--------------+

Die SQL für diese Abfrage ist etwas komplex. Anstatt sie als einzelne Abfrage anzuzeigen, wird die SQL für die einzelnen Spalten aufgeschlüsselt.

In einem realen Szenario hätten Sie wahrscheinlich Daten aus mehreren Jahren. Um die Summen und Durchschnittswerte für eine bestimmte Datenwoche zu berechnen, müssten wir daher ein Fenster für eine Woche oder einen Filter verwenden, der in etwa wie folgt aussieht:

... WHERE date >= start_of_relevant_week and date <= end_of_relevant_week ...

In diesem Beispiel gehen wir jedoch davon aus, dass die Tabelle nur die Daten der letzten Woche enthält.

CREATE TABLE store_sales_2 (
    day INTEGER,
    sales_today INTEGER
    );
+-------------------------------------------+
| status                                    |
|-------------------------------------------|
| Table STORE_SALES_2 successfully created. |
+-------------------------------------------+
INSERT INTO store_sales_2 (day, sales_today) VALUES
    (1, 10),
    (2, 14),
    (3,  6),
    (4,  6),
    (5, 14),
    (6, 16),
    (7, 18);
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       7 |
+-------------------------+

Berechnen des Umsatzrankings

Die Spalte Rank wird mithilfe der Funktion RANK berechnet:

SELECT day, 
       sales_today, 
       RANK()
           OVER (ORDER BY sales_today DESC) AS Rank
    FROM store_sales_2
    ORDER BY day;
+-----+-------------+------+
| DAY | SALES_TODAY | RANK |
|-----+-------------+------|
|   1 |          10 |    5 |
|   2 |          14 |    3 |
|   3 |           6 |    6 |
|   4 |           6 |    6 |
|   5 |          14 |    3 |
|   6 |          16 |    2 |
|   7 |          18 |    1 |
+-----+-------------+------+

Beachten Sie, dass der Zeitraum 7 Tage umfasst, es jedoch nur 5 verschiedene Ränge gibt (1, 2, 3, 5, 6). Es gab zwei Gleichstände (für den 3. und den 6. Platz), daher gibt es keine Zeilen mit den Rängen 4 oder 7.

Berechnen der bisherigen Umsätze dieser Woche

Die Spalte Sales So Far This Week wird unter Verwendung von SUM als Fensterfunktion mit einem kumulativen Fensterrahmen berechnet:

SELECT day, 
       sales_today, 
       SUM(sales_today)
           OVER (ORDER BY day
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
               AS "SALES SO FAR THIS WEEK"
    FROM store_sales_2
    ORDER BY day;
+-----+-------------+------------------------+
| DAY | SALES_TODAY | SALES SO FAR THIS WEEK |
|-----+-------------+------------------------|
|   1 |          10 |                     10 |
|   2 |          14 |                     24 |
|   3 |           6 |                     30 |
|   4 |           6 |                     36 |
|   5 |          14 |                     50 |
|   6 |          16 |                     66 |
|   7 |          18 |                     84 |
+-----+-------------+------------------------+

Diese Abfrage ordnet die Zeilen nach Datum und berechnet dann für jedes Datum die Umsatzsumme vom Beginn des Fensters bis zum aktuellen Datum (einschließlich).

Berechnen des Gesamtumsatzes dieser Woche

Die Spalte Total Sales This Week wird mit SUM als einfache Fensterfunktion berechnet.

SELECT day, 
       sales_today, 
       SUM(sales_today)
           OVER ()
               AS total_sales
    FROM store_sales_2
    ORDER BY day;
+-----+-------------+-------------+
| DAY | SALES_TODAY | TOTAL_SALES |
|-----+-------------+-------------|
|   1 |          10 |          84 |
|   2 |          14 |          84 |
|   3 |           6 |          84 |
|   4 |           6 |          84 |
|   5 |          14 |          84 |
|   6 |          16 |          84 |
|   7 |          18 |          84 |
+-----+-------------+-------------+

Berechnen eines gleitenden 3-Tage-Durchschnitts

Die Spalte 3-Day Moving Average wird unter Verwendung von AVG als Fensterfunktion mit einem gleitenden Fensterrahmen berechnet:

SELECT day, 
       sales_today, 
       AVG(sales_today)
           OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
               AS "3-DAY MOVING AVERAGE"
    FROM store_sales_2
    ORDER BY day;
+-----+-------------+----------------------+
| DAY | SALES_TODAY | 3-DAY MOVING AVERAGE |
|-----+-------------+----------------------|
|   1 |          10 |               10.000 |
|   2 |          14 |               12.000 |
|   3 |           6 |               10.000 |
|   4 |           6 |                8.666 |
|   5 |          14 |                8.666 |
|   6 |          16 |               12.000 |
|   7 |          18 |               16.000 |
+-----+-------------+----------------------+

Der Unterschied zwischen diesem gleitenden Fensterrahmen und dem zuvor beschriebenen kumulativen Fensterrahmen ist lediglich der Anfangspunkt:

  • In einem gleitenden Fensterrahmen verschiebt sich der Startpunkt nach rechts.

  • In einem kumulativen Fensterrahmen ist der Startpunkt fest, und der Rahmen wird mit jeder weiteren Zeile größer.

Alle Funktionsbeispiele zusammenfügen

Die endgültige Version unserer Abfrage enthält Folgendes:

  • Rang

  • Kumulativer Fensterrahmen

  • Einfache Fensterfunktion

  • Gleitende Fensterfunktion

    SELECT day, 
           sales_today, 
           RANK()
               OVER (ORDER BY sales_today DESC) AS Rank,
           SUM(sales_today)
               OVER (ORDER BY day
                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
                   AS "SALES SO FAR THIS WEEK",
           SUM(sales_today)
               OVER ()
                   AS total_sales,
           AVG(sales_today)
               OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
                   AS "3-DAY MOVING AVERAGE"
        FROM store_sales_2
        ORDER BY day;
    +-----+-------------+------+------------------------+-------------+----------------------+
    | DAY | SALES_TODAY | RANK | SALES SO FAR THIS WEEK | TOTAL_SALES | 3-DAY MOVING AVERAGE |
    |-----+-------------+------+------------------------+-------------+----------------------|
    |   1 |          10 |    5 |                     10 |          84 |               10.000 |
    |   2 |          14 |    3 |                     24 |          84 |               12.000 |
    |   3 |           6 |    6 |                     30 |          84 |               10.000 |
    |   4 |           6 |    6 |                     36 |          84 |                8.666 |
    |   5 |          14 |    3 |                     50 |          84 |                8.666 |
    |   6 |          16 |    2 |                     66 |          84 |               12.000 |
    |   7 |          18 |    1 |                     84 |          84 |               16.000 |
    +-----+-------------+------+------------------------+-------------+----------------------+
    

Zusätzliche Beispiele

Dieser Abschnitt enthält detailliertere Beispiele für die Verwendung der Windowing-Unterklauseln in Fensterfunktionen und zeigt, wie diese Unterklauseln zusammenarbeiten.

In diesen Beispielen werden folgende Tabelle und Daten verwendet:

CREATE TABLE sales (sales_date DATE, quantity INTEGER);

INSERT INTO sales (sales_date, quantity) VALUES
    ('2018-01-01', 1),
    ('2018-01-02', 3),
    ('2018-01-03', 5),
    ('2018-02-01', 2)
    ;

Bemerkung

Viele dieser Beispiele enthalten zwei ORDER BY-Klauseln, eine für die Fensterklausel und eine, um das Resultset in die am besten lesbare Reihenfolge zu bringen. Unter diesem Thema bedeuten Verweise auf die ORDER BY-Klausel normalerweise Verweise auf die Klausel im Fenster.

Fenster mit PARTITION BY-Klausel

Die Unterklausel PARTITION BY der Windowing-Klausel unterteilt die Daten in verschiedene Teilmengen basierend auf dem Wert des Eingabeausdrucks. Die SELECT-Anweisung wird auf jede Teilmenge angewendet, und die Ausgabe enthält Zeilen für jede Teilmenge.

Beachten Sie, dass dies der Verwendung der GROUP BY-Klausel ähnelt, jedoch nicht mit ihr identisch ist.

Das nächste Beispiel zeigt die monatlich verkaufte Menge und teilt die Daten mit der PARTITION BY-Klausel in einmonatige Teilmengen auf:

SELECT MONTH(sales_date) AS MONTH_NUM, 
       SUM(quantity) OVER (PARTITION BY MONTH(sales_date)) AS QUANTITY
    FROM sales
    ORDER BY sales_date;
+-----------+----------+
| MONTH_NUM | QUANTITY |
|-----------+----------|
|         1 |        9 |
|         1 |        9 |
|         1 |        9 |
|         2 |        2 |
+-----------+----------+

Wie Sie sehen, sind die ersten drei Zeilen Duplikate. Es gab drei Eingabezeilen für Monat #1, und die Fensterfunktion generiert eine Ausgabezeile für jede Eingabezeile. Daher gibt es drei Ausgabezeilen für Monat #1 in der Ausgabe. Die Funktion SUM wird nicht als kumulative oder gleitende Funktion verwendet. Sie wird auf das gesamte Fenster angewendet und gibt jedes Mal den gleichen Wert für das gesamte Fenster zurück. Daher gibt die Funktion wie oben dargestellt doppelte Werte zurück.

Sie können die Duplikate mit dem Schlüsselwort DISTINCT reduzieren:

SELECT DISTINCT MONTH(sales_date) AS MONTH_NUM, 
       SUM(quantity) OVER (PARTITION BY MONTH(sales_date))
    FROM sales
    ORDER BY MONTH_NUM;
+-----------+-----------------------------------------------------+
| MONTH_NUM | SUM(QUANTITY) OVER (PARTITION BY MONTH(SALES_DATE)) |
|-----------+-----------------------------------------------------|
|         1 |                                                   9 |
|         2 |                                                   2 |
+-----------+-----------------------------------------------------+

In diesem speziellen Fall können Sie eine GROUP BY-Klausel anstelle einer Windowing-Klausel verwenden. Beispiel:

SELECT MONTH(sales_date) AS MONTH_NUM, 
       SUM(quantity)
    FROM sales
    GROUP BY MONTH_NUM
    ORDER BY MONTH_NUM;
+-----------+---------------+
| MONTH_NUM | SUM(QUANTITY) |
|-----------+---------------|
|         1 |             9 |
|         2 |             2 |
+-----------+---------------+

GROUP BY ist jedoch nicht so flexibel wie Windowing. Außerdem können Sie GROUP BY in komplexeren Abfragen nicht immer durch eine Fensterklausel ersetzen.

Fenster mit ORDER BY-Klausel

Die ORDER BY-Klausel steuert die Reihenfolge der Daten in jedem Fenster (und jeder Partition, wenn es mehr als eine Partition gibt). Dies ist nützlich, wenn Sie im Laufe der Zeit, in der immer wieder neue Zeilen hinzugefügt werden, eine „aktuelle Summe“ anzeigen möchten.

Eine laufende Summe kann entweder vom Anfang des Fensters bis (inklusive) zur aktuellen Zeile berechnet werden oder von der aktuellen Zeile bis zum Ende des Fensters.

Eine Abfrage kann ein „gleitendes“ Fenster verwenden, bei dem es sich um ein Fenster mit fester Breite handelt, das N angegebene Zeilen relativ zur aktuellen Zeile verarbeitet (beispielsweise die 10 neuesten Zeilen, einschließlich der aktuellen Zeile).

Nachfolgend werden sowohl kumulative als auch gleitende Fenster erläutert.

Beispiel für kumulative Fensterrahmen

In einem „kumulativen“ Fensterrahmen werden die Werte vom Anfang des Fensters bis zur aktuellen Zeile (oder von der aktuellen Zeile bis zum Ende des Fensters) berechnet:

SELECT MONTH(sales_date) AS MONTH_NUM, 
       quantity, 
       SUM(quantity) OVER (ORDER BY MONTH(sales_date)
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
           AS CUMULATIVE_SUM_QUANTITY
    FROM sales
    ORDER BY sales_date;

Das Abfrageergebnis enthält zusätzliche Kommentare, aus denen hervorgeht, wie die Spalte CUMULATIVE_SUM_QUANTITY berechnet wurde:

+-----------+----------+-------------------------+
| MONTH_NUM | QUANTITY | CUMULATIVE_SUM_QUANTITY |
|-----------+----------+-------------------------|
|         1 |        1 |                       1 |  -- sum = 1
|         1 |        3 |                       4 |  -- sum = 1 + 3
|         1 |        5 |                       9 |  -- sum = 1 + 3 + 5
|         2 |        2 |                      11 |  -- sum = 1 + 3 + 5 + 2
+-----------+----------+-------------------------+

Beispiele für gleitende Fensterrahmen

In der Finanzwelt untersuchen Analysten häufig „gleitende Durchschnittswerte“.

Beispielsweise sehen Sie möglicherweise ein Diagramm, in dem die X-Achse die Zeit ist und auf der Y-Achse der Durchschnittspreis einer Aktie in den letzten 13 Wochen angezeigt wird (d. h. der „gleitende 13-Wochen-Durchschnitt“). In einem Diagramm mit dem gleitenden 13-Wochen-Durchschnitt eines Aktienkurses ist der für den 30. Juni angezeigte Preis nicht der Aktienkurs am 30. Juni, sondern der Durchschnittspreis der Aktie über die letzten 13 Wochen bis einschließlich 30. Juni (d. h. für den Zeitraum 1. April bis 30. Juni). Der Wert am 1. Juli ist der Durchschnittspreis für den Zeitraum 2. April bis 1. Juli. Der Wert am 2. Juli ist der Durchschnittspreis für den Zeitraum 3. April bis 2. Juli und so weiter. Jeden Tag addieren wir den aktuellen Tageswert zum gleitenden Durchschnitt und entfernen den ältesten Tageswert. Auf diese Weise werden täglichen Schwankungen ausgeglichen und Trends besser erkennbar.

Gleitende Durchschnittswerte können mit einem „gleitenden Fenster“ berechnet werden. Das Fenster hat eine bestimmte Breite in Zeilen. In dem oben genannten Beispiel zum Aktienkurs sind es 13 Wochen (91 Tage), sodass das gleitende Fenster 91 Tage aufweisen würde. Wenn die Messung einmal pro Tag durchgeführt wird (z. B. am Ende des Tages), wäre das Fenster 91 Zeilen „breit“.

So definieren Sie ein Fenster mit einer Breite von 91 Zeilen:

SELECT AVG(price) OVER (ORDER BY timestamp1
                        ROWS BETWEEN 90 PRECEDING AND CURRENT ROW);

Bemerkung

Am Anfang ist das Fenster möglicherweise weniger als 91 Tage breit. Angenommen, Sie möchten den gleitenden 13-Wochen-Durchschnitt einer Aktie erhalten. Wenn der Bestand zum ersten Mal am 1. April erstellt wurde, sind am 3. April nur Preisinformationen für 3 Tage vorhanden, sodass das Fenster nur 3 Zeilen breit ist.

Das folgende Beispiel zeigt das Ergebnis der Summierung über ein gleitendes Fenster, das groß genug ist, um zwei Samples aufzunehmen:

SELECT MONTH(sales_date) AS MONTH_NUM,
       quantity,
       SUM(quantity) OVER (ORDER BY sales_date
                           ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 
           AS SLIDING_SUM_QUANTITY
  FROM sales
  ORDER BY sales_date;

Das Abfrageergebnis enthält zusätzliche Kommentare, aus denen hervorgeht, wie die Spalte SLIDING_SUM_QUANTITY berechnet wurde:

+-----------+----------+----------------------+
| MONTH_NUM | QUANTITY | SLIDING_SUM_QUANTITY |
|-----------+----------+----------------------+
|         1 |        1 |                   1  |  -- sum = 1
|         1 |        3 |                   4  |  -- sum = 1 + 3
|         1 |        5 |                   8  |  -- sum = 3 + 5 (1 is no longer in the window)
|         2 |        2 |                   7  |  -- sum = 5 + 2 (3 is no longer in the window)
+-----------+----------+----------------------+

Beachten Sie, dass die Funktionalität für gleitende Fenster nicht ohne die ORDER BY-Klausel verwendet werden kann. Das gleitende Fenster muss die Reihenfolge kennen, in der die Zeilen das gleitende Fenster betreten und verlassen.

Fenster mit PARTITION BY- und ORDER BY-Klauseln

Sie können PARTITION BY- und ORDER BY-Klauseln kombinieren, um aktuelle Summen in Partitionen abzurufen. In diesem Beispiel ist die Partition auf einen Monat festgelegt. Da die Summen nur für eine Partition gelten, wird die Summe zu Beginn jedes neuen Monats auf 0 zurückgesetzt:

SELECT MONTH(sales_date) AS MONTH_NUM,
       SUM(quantity) OVER (PARTITION BY MONTH(sales_date) ORDER BY sales_date)
          AS MONTHLY_CUMULATIVE_SUM_QUANTITY
    FROM sales
    ORDER BY sales_date;

Das Abfrageergebnis enthält zusätzliche Kommentare, aus denen hervorgeht, wie die Spalte MONTHLY_CUMULATIVE_SUM_QUANTITY berechnet wurde:

+-----------+---------------------------------+
| MONTH_NUM | MONTHLY_CUMULATIVE_SUM_QUANTITY |
|-----------+---------------------------------+
|         1 |                               1 |  -- sum = 1
|         1 |                               4 |  -- sum = 1 + 3
|         1 |                               9 |  -- sum = 1 + 3 + 5
|         2 |                               2 |  -- sum = 0 + 2 (new month)
+-----------+---------------------------------+

Sie können Partitionen und gleitende Fenster kombinieren. Im folgenden Beispiel ist das gleitende Fenster normalerweise zwei Zeilen breit. Jedes Mal, wenn eine neue Partition (d. h. ein neuer Monat) erreicht wird, beginnt das gleitende Fenster bei der ersten Zeile dieser Partition:

SELECT
       MONTH(sales_date) AS MONTH_NUM,
       quantity,
       SUM(quantity) OVER (PARTITION BY MONTH(sales_date) 
                           ORDER BY sales_date
                           ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 
         AS MONTHLY_SLIDING_SUM_QUANTITY
    FROM sales
    ORDER BY sales_date;

Das Abfrageergebnis enthält zusätzliche Kommentare, aus denen hervorgeht, wie die Spalte MONTHLY_SLIDING_SUM_QUANTITY berechnet wurde:

+-----------+----------+------------------------------+
| MONTH_NUM | QUANTITY | MONTHLY_SLIDING_SUM_QUANTITY |
|-----------+----------+------------------------------+
|         1 |        1 |                           1  |  -- sum = 1
|         1 |        3 |                           4  |  -- sum = 1 + 3
|         1 |        5 |                           8  |  -- sum = 3 + 5
|         2 |        2 |                           2  |  -- sum = 0 + 2 (new month)
+-----------+----------+------------------------------+