Fensterfunktionen

Eine Fensterfunktion wird in einem Fenster ausgeführt, bei dem es sich um eine Gruppe zusammengehöriger Zeilen handelt.

Bei jedem Aufruf einer Fensterfunktion werden eine Zeile (die aktuelle Zeile im Fenster) und das Fenster übergeben, das die aktuelle Zeile enthält. Die Fensterfunktion gibt für jede Eingabezeile eine Ausgabezeile zurück. Die Ausgabe hängt von der einzelnen Zeile ab, die an die Funktion übergeben wird, und den Werten der anderen Zeilen im Fenster, die an die Funktion übergeben werden.

Bestimmte Fensterfunktionen sind Reihenfolge-abhängig. Es gibt zwei Haupttypen von Reihenfolge-abhängigen Fensterfunktionen:

  • Rangbezogene Funktionen

  • Fensterrahmenfunktionen

Rangbezogene Funktionen listen Informationen basierend auf dem Rang einer Zeile auf. Wenn Sie beispielsweise Läden in absteigender Reihenfolge nach dem Gewinn pro Jahr einstufen, wird der Laden mit dem höchsten Gewinn auf Rang 1 eingestuft, der Laden mit dem zweitgrößten Gewinn auf Rang 2 usw.

Mit Fensterrahmenfunktionen können Sie fortlaufende Operationen ausführen, z. B. die Berechnung einer laufenden Summe oder eines gleitenden Durchschnitts für eine Teilmenge der Zeilen im Fenster.

Benutzer, die mit Fensterfunktionen, rangbezogenen Funktionen oder Fensterrahmenfunktionen nicht vertraut sind, sollten sich erst mit dem Konzept vertraut machen, das unter Verwenden der Fensterfunktionen beschrieben wird.

Unter diesem Thema:

Übersicht

Was ist ein Fenster?

Ein Fenster ist eine Gruppe zusammengehöriger Zeilen. Beispielsweise kann ein Fenster auf Basis von Zeitstempeln definiert werden, wobei alle Zeilen desselben Monats in einem Fenster gruppiert werden. Oder ein Fenster kann auf Basis des Standorts definiert werden, wobei alle Zeilen aus einer bestimmten Stadt in demselben Fenster gruppiert werden.

Ein Fenster kann aus keiner, einer oder mehreren Zeilen bestehen. Der Einfachheit halber wird in der Snowflake-Dokumentation nur angegeben, dass ein Fenster mehrere Zeilen enthält.

Was ist eine Fensterfunktion?

Eine Fensterfunktion ist eine Funktion, die auf einem Fenster aus Zeilen ausgeführt wird.

Einer Fensterfunktion werden im Allgemeinen zwei Parameter übergeben:

  • Eine Zeile. Genauer gesagt werden einer Fensterfunktion 0 oder mehr Ausdrücke übergeben. In fast allen Fällen verweist mindestens einer dieser Ausdrücke auf eine Spalte in dieser Zeile. (Für die meisten Fensterfunktionen ist mindestens eine Spalte oder ein Ausdruck erforderlich, für einige Fensterfunktionen, z. B. für einige rangbezogene Funktionen, ist jedoch keine explizite Spalte oder kein expliziter Ausdruck erforderlich.)

  • Ein Fenster zusammengehöriger Zeilen, das diese Zeile enthält. Das Fenster kann die gesamte Tabelle oder eine Teilmenge der Zeilen in der Tabelle sein.

    Bei Funktionen, die keine Fensterfunktionen sind, werden normalerweise alle Argumente explizit an die Funktion übergeben. Beispiel:

    MY_FUNCTION(Argument1, Argument2, …)

    Fensterfunktionen verhalten sich anders. Obwohl die aktuelle Zeile auf normale Weise als Argument übergeben wird, wird das Fenster durch eine separate Klausel, die als OVER -Klausel bezeichnet wird, übergeben. Die Syntax der OVER-Klausel wird später dokumentiert.

Liste der Funktionen, die Fenster unterstützen

In der folgenden Liste sind alle Fensterfunktionen aufgeführt.

Beachten Sie, dass einige Funktionen, die als Fensterrahmenfunktionen aufgeführt sind, nicht alle möglichen Arten von Fensterrahmen unterstützen.

Funktionsname

Fenster

Fensterrahmen

Rangbezogen

Anmerkungen

Allgemeines Fenster

Verwendet keine Standard-Fenstersyntax.

Verwendet eine andere Syntax als die anderen Fensterfunktionen.

Verwendet eine andere Syntax als die anderen Fensterfunktionen.

STDDEV und STDDEV_SAMP sind Aliasse.

Alias für VAR_POP.

Alias für VAR_SAMP.

Rangbezogen

Unterstützt bereichsbasierte, kumulative Fensterrahmen, jedoch keine anderen Typen von Fensterrahmen.

Bitweise Aggregation

Boolesche Aggregation

Hash

Aggregation semistrukturierter Daten

Zählen diskreter Werte

Lineare Regression

Statistik und Wahrscheinlichkeit

Kardinalitätsschätzung . (mit HyperLogLog)

Alias für HLL.

Keine Aggregationsfunktion; verwendet skalare Eingabe von HLL_ACCUMULATE oder HLL_COMBINE.

Ähnlichkeitsschätzung . (mit MinHash)

Alias für APPROXIMATE_SIMILARITY.

Häufigkeitsschätzung . (mit Space-Saving)

Keine Aggregationsfunktion; verwendet skalare Eingabe von APPROX_TOP_K_ACCUMULATE oder APPROX_TOP_K_COMBINE.

Perzentilschätzung . (mit t-Digest)

Keine Aggregationsfunktion; verwendet skalare Eingabe von APPROX_PERCENTILE_ACCUMULATE oder APPROX_PERCENTILE_COMBINE.

Fensterfunktionen und NULL-Werte

Einige Funktionen ignorieren NULL-Werte. Beispielsweise berechnet AVG den Durchschnitt der Werte 1, 5 und NULL mit dem Ergebnis 3, basierend auf der folgenden Formel:

(1 + 5) / 2 = 3

Sowohl im Zähler als auch im Nenner werden nur die Nicht-NULL-Werte berücksichtigt.

Wenn alle übergebenen Werte NULL sind, gibt die Funktion NULL zurück.

Einigen Fensterfunktionen können mehr als eine Spalte übergeben werden. Beispiel:

SELECT COUNT(col1, col2) FROM table1;
Copy

In diesen Fällen ignoriert die Funktion eine Zeile, wenn eine einzelne Spalte NULL ist.

In der folgenden Abfrage gibt COUNT beispielsweise 1 zurück, nicht 4, da drei der vier Zeilen mindestens einen NULL-Wert in den ausgewählten Spalten enthalten:

Erstellen Sie eine Tabelle, und füllen Sie sie mit Werten auf:

CREATE TABLE t (x INT, y INT);
INSERT INTO t (x, y) VALUES
  (1, 2),         -- No NULLs.
  (3, NULL),      -- One but not all columns are NULL.
  (NULL, 6),      -- One but not all columns are NULL.
  (NULL, NULL);   -- All columns are NULL.
Copy

Abfragen der Tabelle:

SELECT COUNT(x, y) FROM t;
+-------------+
| COUNT(X, Y) |
|-------------|
|           1 |
+-------------+
Copy

Wenn SUM mit einem Ausdruck aufgerufen wird, der auf zwei oder mehr Spalten verweist, und wenn eine oder mehrere dieser Spalten NULL sind, wird der Ausdruck zu NULL ausgewertet, und die Zeile wird ignoriert:

SELECT SUM(x + y) FROM t;
+------------+
| SUM(X + Y) |
|------------|
|          3 |
+------------+
Copy

Beachten Sie, dass sich dieses Verhalten vom Verhalten von GROUP BY unterscheidet, bei dem Zeilen nicht gelöscht werden, wenn einige Spalten NULL sind:

SELECT x AS X_COL, y AS Y_COL FROM t GROUP BY x, y;
+-------+-------+
| X_COL | Y_COL |
|-------+-------|
|     1 |     2 |
|     3 |  NULL |
|  NULL |     6 |
|  NULL |  NULL |
+-------+-------+
Copy

Einführungsbeispiel

Angenommen, Sie besitzen eine Ladenkette. Die folgende Abfrage zeigt den Prozentsatz des Gesamtgewinns der Kette, der von jedem Laden generiert wird. Die Abfrage verwendet die Funktion RATIO_TO_REPORT, die einen Wert (z. B. „net_profit“) aus der aktuellen Zeile aufnimmt und durch die Summe der entsprechenden Werte („net_profit“) aus allen anderen Zeilen dividiert:

Erstellen und laden Sie die Tabelle:

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;
Copy

Abfragen der Tabelle:

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

Was ist ein Fensterrahmen?

Ein Fensterrahmen ist eine Untergruppe von Zeilen in einem Fenster. Durch das Erstellen von Teilmengen können Werte nur für die angegebene Untergruppe von Zeilen berechnet werden. Fensterrahmen werden als zusätzliche Subklausel in der ORDER BY-Subklausel der OVER-Klausel angegeben.

Typen von Fensterrahmen

Snowflake unterstützt zwei Arten von Fensterrahmen:

Kumulativ:

Ermöglicht die Berechnung von rollierenden Werten vom Anfang des Fensters bis zur aktuellen Zeile oder von der aktuellen Zeile bis zum Ende des Fensters.

Gleitend:

Ermöglicht die Berechnung von rollierenden Werten zwischen zwei beliebigen Zeilen (einschließlich) im Fenster, bezogen auf die aktuelle Zeile.

Weitere Informationen zu Fensterrahmen, einschließlich der für Fensterrahmen verwendeten Syntax, finden Sie unter Syntax und Nutzung von Fensterrahmen.

Fenstersyntax und Verwendung

Fenstersyntax

<function> ( [ <arguments> ] ) OVER ( [ PARTITION BY <expr1> ] [ ORDER BY <expr2> ] )
Copy

Die OVER-Klausel gibt an, dass die Funktion als Fensterfunktion verwendet wird.

Mit der Unterklausel PARTITION BY können Zeilen in Untergruppen gruppiert werden, beispielsweise nach Stadt, Jahr usw. Die PARTITION BY-Klausel ist optional. Sie können eine ganze Gruppe von Zeilen analysieren, ohne sie in Untergruppen aufteilen zu müssen.

Die ORDER BY-Klausel sortiert Zeilen innerhalb des Fensters. (Dies unterscheidet sich von der Sortierung der Ausgabe einer Abfrage. Eine Abfrage kann eine ORDER BY-Klausel enthalten, die die Reihenfolge der Zeilen innerhalb eines Fensters steuert, und eine separate ORDER BY-Klausel außerhalb der OVER-Klausel, die die Ausgabereihenfolge der gesamten Abfrage steuert.) Obwohl die ORDER BY-Klausel für einige Fensterfunktionen optional ist, ist sie für andere erforderlich. Beispielsweise erfordern Fensterrahmenfunktionen und rangbezogene Funktionen, dass die Daten in einer aussagekräftigen Reihenfolge vorliegen, und erfordern daher eine ORDER BY-Unterklausel.

Bemerkung

Die Funktionen, die Fensterrahmen unterstützen, verwenden eine modifizierte/erweiterte Syntax. Weitere Details dazu finden Sie unter Syntax und Nutzung von Fensterrahmen (unter diesem Thema).

Hinweise zur Nutzung des Fensters

  • Die OVER-Klausel gibt das Fenster an, über das die Funktion arbeitet. Die Klausel besteht aus einer (oder beiden) der folgenden Komponenten:

    • PARTITION BY expr1: Unterklausel, die die eventuell vorhandene Partition für das Fenster definiert (d. h. wie die Daten vor Anwendung der Funktion gruppiert werden).

    • ORDER BY expr2: Unterklausel, die die Reihenfolge der Zeilen im Fenster bestimmt.

      Die Unterklausel ORDER BY folgt ähnlichen Regeln wie die Abfrageklausel ORDER BY, z. B. in Bezug auf ASC/DESC (aufsteigend/absteigend) und NULL. Weitere Informationen zu zusätzlichen unterstützten Optionen finden Sie in der Dokumentation zum Abfragekonstrukt ORDER BY.

      Verschiedene Funktionen behandeln die ORDER BY-Klausel auf unterschiedliche Weise:

      • 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.)

      Bemerkung

      Snowflake empfiehlt, aus Gründen der Übersichtlichkeit auf implizite Fensterrahmen zu verzichten. Wenn Ihre Abfrage einen Fensterrahmen verwendet, machen Sie ihn zu einem expliziten Fensterrahmen.

Syntax und Nutzung von Fensterrahmen

Fensterrahmensyntax

<function> ( <arguments> ) OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ cumulativeFrame | slidingFrame ] )
Copy

Wobei:

cumulativeFrame ::=
    {
       { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
     | { ROWS | RANGE } BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    }
Copy
slidingFrame ::=
    {
       ROWS BETWEEN <N> { PRECEDING | FOLLOWING } AND <N> { PRECEDING | FOLLOWING }
     | ROWS BETWEEN UNBOUNDED PRECEDING AND <N> { PRECEDING | FOLLOWING }
     | ROWS BETWEEN <N> { PRECEDING | FOLLOWING } AND UNBOUNDED FOLLOWING
    }
Copy

Bemerkung

Fensterrahmen erfordern, dass die Daten im Fenster in einer bekannten Reihenfolge vorliegen. Daher ist ORDER BY innerhalb der OVER-Klausel für die Fensterrahmensyntax erforderlich, obwohl diese ORDER BY-Klausel in der allgemeinen Fensterfunktionssyntax optional ist.

Hinweise zur Nutzung von Fensterrahmen

  • Für kumulative Fensterrahmen:

    • ROWS berechnet das Ergebnis für die aktuelle Zeile unter Verwendung aller Zeilen vom Anfang oder Ende der Partition bis zur aktuellen Zeile (gemäß der angegebenen ORDER BY-Unterklausel).

    • RANGE ist ähnlich wie ROWS, außer dass es das Ergebnis nur für Zeilen berechnet, die den gleichen Wert wie die aktuelle Zeile haben (gemäß der angegebenen ORDER BY-Unterklausel).

  • Für gleitende Fensterrahmen:

    • ROWS ist inklusive und immer relativ zur aktuellen Zeile.

    • RANGE wird nicht unterstützt.

    • Wenn die angegebene Anzahl von vorherigen oder nachfolgenden ROWS über die Fenstergrenzen hinausgeht, behandelt Snowflake den Wert als NULL.

  • Wenn kein Fensterrahmen angegeben ist, hängt der Standard von der Funktion ab:

    • Bei Funktionen, die nicht rangbezogen sind (COUNT, MAX, MIN, SUM), ist der Standard der folgende kumulative Fensterrahmen (gemäß ANSI-Standard):

      RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

    • Bei rangbezogenen Funktionen (FIRST_VALUE, LAST_VALUE, NTH_VALUE) ist der Standard das gesamte Fenster:

      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

      Beachten Sie, dass dies vom ANSI-Standard abweicht.

Allgemeine Hinweise

  • Die Syntax zeigt alle Unterklauseln der OVER-Klausel als optional für Fensterfunktionen an. Dies ist beabsichtigt (d. h. Sie können OVER ohne Unterklauseln innerhalb der Klammern verwenden). Dies kann in bestimmten Szenarien hilfreich sein (z. B. Deaktivieren der Parallelverarbeitung).

  • PARTITION BY ist nicht immer mit GROUP BY kompatibel.

  • Wenn eine Abfrage mehr als eine Fensterfunktion verwendet, sollte das Eingabe-Dataset jeder Funktion normalerweise auf dieselbe Weise partitioniert werden. Im Beispiel unten ist die erste Anweisung wahrscheinlich eher korrekt als die zweite Anweisung:

    SELECT SUM(a) OVER (PARTITION BY x), SUM(b) OVER (PARTITION BY x) ... ;
    
    SELECT SUM(a)                      , SUM(b) OVER (PARTITION BY x) ... ;
    
    Copy

    Die Fehlermeldung SQL compilation error: ... is not a valid group by expression ist häufig ein Zeichen dafür, dass verschiedene Spalten in der „Projekt“-Klausel der SELECT-Anweisung nicht auf dieselbe Weise partitioniert sind und daher möglicherweise eine unterschiedliche Anzahl von Zeilen erzeugen.

Beispiele

Weitere Beispiele finden Sie unter Verwenden der Fensterfunktionen.

Beispiele für kumulative Fensterrahmen

Erstellen Sie eine Tabelle, und füllen Sie diese mit Werten:

CREATE OR REPLACE TABLE example_cumulative (p INT, o INT, i INT);

INSERT INTO example_cumulative VALUES
    (  0, 1, 10), (0, 2, 20), (0, 3, 30),
    (100, 1, 10),(100, 2, 30),(100, 2, 5),(100, 3, 11),(100, 3, 120),
    (200, 1, 10000),(200, 1, 200),(200, 1, 808080),(200, 2, 33333),(200, 3, null), (200, 3, 4),
    (300, 1, null), (300, 1, null);
Copy

Führen Sie eine Abfrage aus, die einen kumulativen Fensterrahmen verwendet, und zeigen Sie die Ausgabe an. Ermitteln von kumulativer Anzahl, Summe, Min und Max für Zeilen im angegebenen Fenster der Tabelle:

SELECT
    p, o, i,
    COUNT(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) count_i_Rows_Pre,
    SUM(i)   OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_i_Rows_Pre,
    AVG(i)   OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) avg_i_Rows_Pre,
    MIN(i)   OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) min_i_Rows_Pre,
    MAX(i)   OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_i_Rows_Pre
  FROM example_cumulative
  ORDER BY p,o;
+-----+---+--------+------------------+----------------+----------------+----------------+----------------+
|   P | O |      I | COUNT_I_ROWS_PRE | SUM_I_ROWS_PRE | AVG_I_ROWS_PRE | MIN_I_ROWS_PRE | MAX_I_ROWS_PRE |
|-----+---+--------+------------------+----------------+----------------+----------------+----------------|
|   0 | 1 |     10 |                1 |             10 |         10.000 |             10 |             10 |
|   0 | 2 |     20 |                2 |             30 |         15.000 |             10 |             20 |
|   0 | 3 |     30 |                3 |             60 |         20.000 |             10 |             30 |
| 100 | 1 |     10 |                1 |             10 |         10.000 |             10 |             10 |
| 100 | 2 |     30 |                2 |             40 |         20.000 |             10 |             30 |
| 100 | 2 |      5 |                3 |             45 |         15.000 |              5 |             30 |
| 100 | 3 |     11 |                4 |             56 |         14.000 |              5 |             30 |
| 100 | 3 |    120 |                5 |            176 |         35.200 |              5 |            120 |
| 200 | 1 |  10000 |                1 |          10000 |      10000.000 |          10000 |          10000 |
| 200 | 1 |    200 |                2 |          10200 |       5100.000 |            200 |          10000 |
| 200 | 1 | 808080 |                3 |         818280 |     272760.000 |            200 |         808080 |
| 200 | 2 |  33333 |                4 |         851613 |     212903.250 |            200 |         808080 |
| 200 | 3 |   NULL |                4 |         851613 |     212903.250 |            200 |         808080 |
| 200 | 3 |      4 |                5 |         851617 |     170323.400 |              4 |         808080 |
| 300 | 1 |   NULL |                0 |           NULL |           NULL |           NULL |           NULL |
| 300 | 1 |   NULL |                0 |           NULL |           NULL |           NULL |           NULL |
+-----+---+--------+------------------+----------------+----------------+----------------+----------------+
Copy

Ermitteln von kumulativer Anzahl, Summe, Min und Max nach Rang sortiert für Zeilen im angegebenen Fenster der Tabelle:

SELECT
    p, o, i,
    COUNT(i) OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) count_i_Range_Pre,
    SUM(i)   OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_i_Range_Pre,
    AVG(i)   OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) avg_i_Range_Pre,
    MIN(i)   OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) min_i_Range_Pre,
    MAX(i)   OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_i_Range_Pre
  FROM example_cumulative
  ORDER BY p,o;
+-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------+
|   P | O |      I | COUNT_I_RANGE_PRE | SUM_I_RANGE_PRE | AVG_I_RANGE_PRE | MIN_I_RANGE_PRE | MAX_I_RANGE_PRE |
|-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------|
|   0 | 1 |     10 |                 1 |              10 |       10.000000 |              10 |              10 |
|   0 | 2 |     20 |                 2 |              30 |       15.000000 |              10 |              20 |
|   0 | 3 |     30 |                 3 |              60 |       20.000000 |              10 |              30 |
| 100 | 1 |     10 |                 1 |              10 |       10.000000 |              10 |              10 |
| 100 | 2 |     30 |                 3 |              45 |       15.000000 |               5 |              30 |
| 100 | 2 |      5 |                 3 |              45 |       15.000000 |               5 |              30 |
| 100 | 3 |     11 |                 5 |             176 |       35.200000 |               5 |             120 |
| 100 | 3 |    120 |                 5 |             176 |       35.200000 |               5 |             120 |
| 200 | 1 |  10000 |                 3 |          818280 |   272760.000000 |             200 |          808080 |
| 200 | 1 |    200 |                 3 |          818280 |   272760.000000 |             200 |          808080 |
| 200 | 1 | 808080 |                 3 |          818280 |   272760.000000 |             200 |          808080 |
| 200 | 2 |  33333 |                 4 |          851613 |   212903.250000 |             200 |          808080 |
| 200 | 3 |   NULL |                 5 |          851617 |   170323.400000 |               4 |          808080 |
| 200 | 3 |      4 |                 5 |          851617 |   170323.400000 |               4 |          808080 |
| 300 | 1 |   NULL |                 0 |            NULL |            NULL |            NULL |            NULL |
| 300 | 1 |   NULL |                 0 |            NULL |            NULL |            NULL |            NULL |
+-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------+
Copy

Ermitteln derselben Ergebnisse wie in der obigen Abfrage aber durch Verwendung der Standardsemantik für Fensterrahmen (d. h. RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW):

SELECT
    p, o, i,
    COUNT(i) OVER (PARTITION BY p ORDER BY o) count_i_Range_Pre,
    SUM(i)   OVER (PARTITION BY p ORDER BY o) sum_i_Range_Pre,
    AVG(i)   OVER (PARTITION BY p ORDER BY o) avg_i_Range_Pre,
    MIN(i)   OVER (PARTITION BY p ORDER BY o) min_i_Range_Pre,
    MAX(i)   OVER (PARTITION BY p ORDER BY o) max_i_Range_Pre
  FROM example_cumulative
  ORDER BY p,o;
+-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------+
|   P | O |      I | COUNT_I_RANGE_PRE | SUM_I_RANGE_PRE | AVG_I_RANGE_PRE | MIN_I_RANGE_PRE | MAX_I_RANGE_PRE |
|-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------|
|   0 | 1 |     10 |                 1 |              10 |       10.000000 |              10 |              10 |
|   0 | 2 |     20 |                 2 |              30 |       15.000000 |              10 |              20 |
|   0 | 3 |     30 |                 3 |              60 |       20.000000 |              10 |              30 |
| 100 | 1 |     10 |                 1 |              10 |       10.000000 |              10 |              10 |
| 100 | 2 |     30 |                 3 |              45 |       15.000000 |               5 |              30 |
| 100 | 2 |      5 |                 3 |              45 |       15.000000 |               5 |              30 |
| 100 | 3 |     11 |                 5 |             176 |       35.200000 |               5 |             120 |
| 100 | 3 |    120 |                 5 |             176 |       35.200000 |               5 |             120 |
| 200 | 1 |  10000 |                 3 |          818280 |   272760.000000 |             200 |          808080 |
| 200 | 1 |    200 |                 3 |          818280 |   272760.000000 |             200 |          808080 |
| 200 | 1 | 808080 |                 3 |          818280 |   272760.000000 |             200 |          808080 |
| 200 | 2 |  33333 |                 4 |          851613 |   212903.250000 |             200 |          808080 |
| 200 | 3 |   NULL |                 5 |          851617 |   170323.400000 |               4 |          808080 |
| 200 | 3 |      4 |                 5 |          851617 |   170323.400000 |               4 |          808080 |
| 300 | 1 |   NULL |                 0 |            NULL |            NULL |            NULL |            NULL |
| 300 | 1 |   NULL |                 0 |            NULL |            NULL |            NULL |            NULL |
+-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------+
Copy

Beispiele für gleitende Fensterrahmen

Erstellen Sie eine Tabelle, und füllen Sie diese mit Werten:

CREATE TABLE example_sliding
  (p INT, o INT, i INT, r INT, s VARCHAR(100));

INSERT INTO example_sliding VALUES
  (100,1,1,70,'seventy'),(100,2,2,30, 'thirty'),(100,3,3,40,'forty'),(100,4,NULL,90,'ninety'),
  (100,5,5,50,'fifty'),(100,6,6,30,'thirty'),
  (200,7,7,40,'forty'),(200,8,NULL,NULL,'n_u_l_l'),(200,9,NULL,NULL,'n_u_l_l'),(200,10,10,20,'twenty'),
  (200,11,NULL,90,'ninety'),
  (300,12,12,30,'thirty'),
  (400,13,NULL,20,'twenty');
Copy

Ermitteln des Minimumwerts für zwei Spalten (numerische und Zeichenfolge) in gleitenden Fenstern vor, nach und um die aktuelle Zeile:

select p, o, i AS i_col,
    MIN(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) min_i_3P_1P,
    MIN(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) min_i_1F_3F,
    MIN(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 PRECEDING AND 3 FOLLOWING) min_i_1P_3F,
    s,
    MIN(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) min_s_3P_1P,
    MIN(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) min_s_1F_3F,
    MIN(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 PRECEDING AND 3 FOLLOWING) min_s_1P_3F
  FROM example_sliding
  ORDER BY p, o;
+-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------+
|   P |  O | I_COL | MIN_I_3P_1P | MIN_I_1F_3F | MIN_I_1P_3F | S       | MIN_S_3P_1P | MIN_S_1F_3F | MIN_S_1P_3F |
|-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------|
| 100 |  1 |     1 |        NULL |           2 |           1 | seventy | NULL        | forty       | forty       |
| 100 |  2 |     2 |           1 |           3 |           1 | thirty  | seventy     | fifty       | fifty       |
| 100 |  3 |     3 |           1 |           5 |           2 | forty   | seventy     | fifty       | fifty       |
| 100 |  4 |  NULL |           1 |           5 |           3 | ninety  | forty       | fifty       | fifty       |
| 100 |  5 |     5 |           2 |           6 |           5 | fifty   | forty       | thirty      | fifty       |
| 100 |  6 |     6 |           3 |        NULL |           5 | thirty  | fifty       | NULL        | fifty       |
| 200 |  7 |     7 |        NULL |          10 |           7 | forty   | NULL        | n_u_l_l     | forty       |
| 200 |  8 |  NULL |           7 |          10 |           7 | n_u_l_l | forty       | n_u_l_l     | forty       |
| 200 |  9 |  NULL |           7 |          10 |          10 | n_u_l_l | forty       | ninety      | n_u_l_l     |
| 200 | 10 |    10 |           7 |        NULL |          10 | twenty  | forty       | ninety      | n_u_l_l     |
| 200 | 11 |  NULL |          10 |        NULL |          10 | ninety  | n_u_l_l     | NULL        | ninety      |
| 300 | 12 |    12 |        NULL |        NULL |          12 | thirty  | NULL        | NULL        | thirty      |
| 400 | 13 |  NULL |        NULL |        NULL |        NULL | twenty  | NULL        | NULL        | twenty      |
+-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------+
Copy

Ermitteln der Maximumwerte für zwei Spalten (numerische und Zeichenfolge) in gleitenden Fenstern vor, nach und um die aktuelle Zeile:

SELECT p, o, i AS i_col,
    MAX(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) max_i_3P_1P,
    MAX(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) max_i_1F_3F,
    MAX(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 PRECEDING AND 3 FOLLOWING) max_i_1P_3F,
    s,
    MAX(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) max_s_3P_1P,
    MAX(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) max_s_1F_3F,
    MAX(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 PRECEDING AND 3 FOLLOWING) max_s_1P_3F
  FROM example_sliding
  ORDER BY p, o;
+-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------+
|   P |  O | I_COL | MAX_I_3P_1P | MAX_I_1F_3F | MAX_I_1P_3F | S       | MAX_S_3P_1P | MAX_S_1F_3F | MAX_S_1P_3F |
|-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------|
| 100 |  1 |     1 |        NULL |           3 |           3 | seventy | NULL        | thirty      | thirty      |
| 100 |  2 |     2 |           1 |           5 |           5 | thirty  | seventy     | ninety      | thirty      |
| 100 |  3 |     3 |           2 |           6 |           6 | forty   | thirty      | thirty      | thirty      |
| 100 |  4 |  NULL |           3 |           6 |           6 | ninety  | thirty      | thirty      | thirty      |
| 100 |  5 |     5 |           3 |           6 |           6 | fifty   | thirty      | thirty      | thirty      |
| 100 |  6 |     6 |           5 |        NULL |           6 | thirty  | ninety      | NULL        | thirty      |
| 200 |  7 |     7 |        NULL |          10 |          10 | forty   | NULL        | twenty      | twenty      |
| 200 |  8 |  NULL |           7 |          10 |          10 | n_u_l_l | forty       | twenty      | twenty      |
| 200 |  9 |  NULL |           7 |          10 |          10 | n_u_l_l | n_u_l_l     | twenty      | twenty      |
| 200 | 10 |    10 |           7 |        NULL |          10 | twenty  | n_u_l_l     | ninety      | twenty      |
| 200 | 11 |  NULL |          10 |        NULL |          10 | ninety  | twenty      | NULL        | twenty      |
| 300 | 12 |    12 |        NULL |        NULL |          12 | thirty  | NULL        | NULL        | thirty      |
| 400 | 13 |  NULL |        NULL |        NULL |        NULL | twenty  | NULL        | NULL        | twenty      |
+-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------+
Copy

Ermitteln der Summe einer Zahlenspalte in gleitenden Fenstern vor, nach und um die aktuelle Zeile:

SELECT p, o, r AS r_col,
    SUM(r) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 4 PRECEDING AND 2 PRECEDING) sum_r_4P_2P,
    sum(r) over (partition by p ORDER BY o ROWS BETWEEN 2 FOLLOWING AND 4 FOLLOWING) sum_r_2F_4F,
    sum(r) over (partition by p ORDER BY o ROWS BETWEEN 2 PRECEDING AND 4 FOLLOWING) sum_r_2P_4F
  FROM example_sliding
  ORDER BY p, o;
+-----+----+-------+-------------+-------------+-------------+
|   P |  O | R_COL | SUM_R_4P_2P | SUM_R_2F_4F | SUM_R_2P_4F |
|-----+----+-------+-------------+-------------+-------------|
| 100 |  1 |    70 |        NULL |         180 |         280 |
| 100 |  2 |    30 |        NULL |         170 |         310 |
| 100 |  3 |    40 |          70 |          80 |         310 |
| 100 |  4 |    90 |         100 |          30 |         240 |
| 100 |  5 |    50 |         140 |        NULL |         210 |
| 100 |  6 |    30 |         160 |        NULL |         170 |
| 200 |  7 |    40 |        NULL |         110 |         150 |
| 200 |  8 |  NULL |        NULL |         110 |         150 |
| 200 |  9 |  NULL |          40 |          90 |         150 |
| 200 | 10 |    20 |          40 |        NULL |         110 |
| 200 | 11 |    90 |          40 |        NULL |         110 |
| 300 | 12 |    30 |        NULL |        NULL |          30 |
| 400 | 13 |    20 |        NULL |        NULL |          20 |
+-----+----+-------+-------------+-------------+-------------+
Copy