Fensterfunktionen¶

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.

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;

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.

Was ist eine rangbezogene Funktion?¶

Eine rangbezogene Funktion gibt den Rang (die Position) der aktuellen Zeile im Fenster an. Rangbezogene Funktion sind nützlich, wenn sie für ein Fenster von Zeilen aufgerufen werden, das bereits nach einem nützlichen Kriterium sortiert wurde. Wenn die Zeilen in einem Fenster beispielsweise Informationen zur Rentabilität einzelner Läden einer Ladenkette enthalten und die Zeilen in absteigender Reihenfolge der Rentabilität sortiert sind, stellen die Ränge der Zeilen im Fenster (1, 2, 3 usw.) die Rentabilität der Läden dar (höchste Rentabilität, zweithöchste Rentabilität, dritthöchste Rentabilität usw.). Rangbezogene Funktionen sind daher immer Reihenfolge-abhängige Funktionen und erfordern die Unterklausel ORDER BY der OVER()-Klausel.

Die Funktion RANK gibt einen positiven Ganzzahlwert zwischen 1 und der Anzahl der Zeilen im Fenster (einschließlich) zurück.

Wie bei jeder anderen Fensterfunktion übergeben Sie beim Aufrufen einer rangbezogenen Funktion explizit nicht nur eine Zeile (oder genauer gesagt eine oder mehr Spalten einer Zeile oder Ausdrücke basierend auf den Spalten in der Zeile), sondern auch ein Fenster von Zeilen. Die Ausgabe der Funktion hängt von Folgendem ab:

• Von der einzelnen Zeile, die an die Funktion übergeben wurde.

• Von den Werten der anderen Zeilen in dem Fenster, das an die Funktion übergeben wurde.

• Von der Reihenfolge aller Zeilen im Fenster.

Mit der Funktion RANK können Sie Folgendes erstellen:

• Erstellen einer Rangliste der Verkäufer nach Umsatz (Verkäufe) vom höchsten zum niedrigsten Wert.

• Erstellen einer Rangliste der Länder auf Basis des Pro-Kopf-BIP (Einkommen pro Person) vom höchsten zum niedrigsten Wert.

• Erstellen einer Rangliste der Länder hinsichtlich Luftverschmutzung vom niedrigsten zum höchsten Wert.

Ein Verkaufsbericht, der eine Rangliste verwendet, kann folgendermaßen aussehen:

+------------+-------+------+
| Name       | Sales | Rank |
|------------+-------+------|
| Smith      |  2000 |    1 |
| Jones      |  1500 |    2 |
| Torkelson  |  1100 |    3 |
| Dolenz     |  1000 |    4 |
+------------+-------+------+

Das Generieren eines solchen Berichts wird im Abschnitt Beispiele (unter diesem Thema) gezeigt.

Fenstersyntax¶

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

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.

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.

Fensterrahmensyntax¶

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

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

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.

Syntax der rangbezogenen Fensterfunktionen¶

Die Syntax für eine rangbezogene Fensterfunktion entspricht im Wesentlichen der Syntax für andere Fensterfunktionen. Zu den Funktionen gehören:

• Da rangbezogene Fensterfunktionen von der Reihenfolge abhängig sind, ist die ORDER BY-Klausel erforderlich und nicht optional.

• Für einige rangbezogene Funktionen, wie z. B. RANK selbst, ist kein Eingabeargument erforderlich. Bei der RANK-Funktion basiert der zurückgegebene Wert ausschließlich auf dem Rang, der durch die Unterklausel ORDER BY der Klausel OVER bestimmt wird. Daher ist es nicht erforderlich, einen Spaltennamen oder einen Ausdruck an die RANK-Funktion zu übergeben.

Hinweise zur Verwendung der rangbezogenen Fensterfunktion¶

• Eine Funktion kann sowohl eine rangbezogene Funktion als auch eine Fensterrahmenfunktion sein. Beispielsweise können Sie den Rang von Zeilen innerhalb eines Schiebefensters bestimmen.

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

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 |
+-----+---+--------+------------------+----------------+----------------+----------------+----------------+

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 |
+-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------+

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 |
+-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------+

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');

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      |
+-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------+

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      |
+-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------+

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 |
+-----+----+-------+-------------+-------------+-------------+

Beispiele für analytische Funktionen¶

Das folgende Beispiel zeigt, wie die Rangfolge der Verkäufer nach dem Gesamtverkaufsbetrag (in US-Dollar) erstellt wird. Die Abfrage verwendet die OVER-Klausel, um ein Fenster zu erstellen, das den Gesamtumsatz der einzelnen Verkäufer enthält. Die Unterklausel ORDER BY in der OVER-Klausel sortiert diese Zeilen in absteigender Reihenfolge nach Gesamtumsatz (d. h. vom höchsten zum niedrigsten). Die Abfrage berechnet dann den Rang jedes Vertriebsmitarbeiters im Verhältnis zu den anderen Vertriebsmitarbeitern.

Beginnen Sie mit dem Erstellen der Tabelle und dem Einfügen von Daten:

CREATE TABLE sales_table (salesperson_name VARCHAR, sales_in_dollars INTEGER);
INSERT INTO sales_table (salesperson_name, sales_in_dollars) VALUES
    ('Smith', 600),
    ('Jones', 1000),
    ('Torkelson', 700),
    ('Dolenz', 800);

Copy

Fragen Sie jetzt die Daten ab:

SELECT
    salesperson_name,
    sales_in_dollars,
    RANK() OVER (ORDER BY sales_in_dollars DESC) AS sales_rank
  FROM sales_table;
+------------------+------------------+------------+
| SALESPERSON_NAME | SALES_IN_DOLLARS | SALES_RANK |
|------------------+------------------+------------|
| Jones            |             1000 |          1 |
| Dolenz           |              800 |          2 |
| Torkelson        |              700 |          3 |
| Smith            |              600 |          4 |
+------------------+------------------+------------+

Copy

Die Ausgabe wird nicht notwendigerweise nach Rang sortiert ausgegeben. Sie können die Ausgabe sortiert nach dem Rang erzwingen, indem Sie eine ORDER BY-Klausel (außerhalb der OVER-Klausel) verwenden (siehe unten):

SELECT
    salesperson_name,
    sales_in_dollars,
    RANK() OVER (ORDER BY sales_in_dollars DESC) AS sales_rank
  FROM sales_table
  ORDER BY 3;
+------------------+------------------+------------+
| SALESPERSON_NAME | SALES_IN_DOLLARS | SALES_RANK |
|------------------+------------------+------------|
| Jones            |             1000 |          1 |
| Dolenz           |              800 |          2 |
| Torkelson        |              700 |          3 |
| Smith            |              600 |          4 |
+------------------+------------------+------------+

Copy

Das vorherige Beispiel hat zwei ORDER BY-Klauseln:

• Eine steuert die Reihenfolge des Rankings.

• Die andere steuert die Reihenfolge für die Ausgabe.

Beide Klauseln sind unabhängig voneinander. So können Sie beispielsweise die Rangfolgen auf Basis des Gesamtumsatzes (wie oben beschrieben) ermitteln, aber die Ausgabezeilen auf Basis des Nachnamens des Verkäufers sortieren:

SELECT
    salesperson_name,
    sales_in_dollars,
    RANK() OVER (ORDER BY sales_in_dollars DESC) AS sales_rank
  FROM sales_table
  ORDER BY salesperson_name;
+------------------+------------------+------------+
| SALESPERSON_NAME | SALES_IN_DOLLARS | SALES_RANK |
|------------------+------------------+------------|
| Dolenz           |              800 |          2 |
| Jones            |             1000 |          1 |
| Smith            |              600 |          4 |
| Torkelson        |              700 |          3 |
+------------------+------------------+------------+

Copy