Syntax und Verwendung von Fensterfunktionen¶
Unter diesem Thema:
Snowflake unterstützt eine große Anzahl von analytischen SQL-Funktionen, die als Fensterfunktionen bekannt sind. Die Details für jede Funktion sind auf den einzelnen Referenzseiten dokumentiert. Der Zweck dieses Abschnitts besteht darin, allgemeine Referenzinformationen bereitzustellen, die für einige oder alle Fensterfunktionen gelten, einschließlich einer detaillierten Syntax für die Hauptkomponenten der OVER-Klausel:
PARTITION BY-Klausel.
ORDER BY-Klausel.
Fensterrahmensyntax
Benutzer, die mit Fensterfunktionen nicht vertraut sind, sollten sich erst mit dem Konzept vertraut machen, das unter Analysieren von Daten mit Fensterfunktionen beschrieben wird.
Syntax¶
<function> ( [ <arguments> ] ) OVER ( [ <windowDefinition> ] )
Wobei:
windowDefinition ::=
[ PARTITION BY <expr1> [, ...] ]
[ ORDER BY <expr2> [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ <windowFrameClause> ]
Wobei:
windowFrameClause ::=
{
{ ROWS | RANGE } UNBOUNDED PRECEDING
| { ROWS | RANGE } <n> PRECEDING
| { ROWS | RANGE } CURRENT ROW
| { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
| { ROWS | RANGE } BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
| { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
| { ROWS | RANGE } BETWEEN <n> { PRECEDING | FOLLOWING } AND <n> { PRECEDING | FOLLOWING }
| { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND <n> { PRECEDING | FOLLOWING }
| { ROWS | RANGE } BETWEEN <n> { PRECEDING | FOLLOWING } AND UNBOUNDED FOLLOWING
}
Parameter¶
OVER( [ windowDefinition ] )Gibt an, dass die Funktion als Fensterfunktion verwendet wird und gibt das Fenster an, über das die Funktion operiert. Die OVER-Klause muss Klammern enthalten, die jedoch je nach Anforderungen der betreffenden Funktion auch leer sein können. Eine leere OVER-Klausel hat keine Partitionen und einen impliziten Standard-Fensterrahmen.
PARTITION BY expr1Gruppiert Zeilen in Partitionen, z. B. nach Produkt, Stadt oder Jahr. Die eingegebenen Zeilen werden nach Partitionen gruppiert, dann wird die Funktion über jede Partition berechnet. Die PARTITION BY-Klausel ist optional. Sie können eine Gruppe von Zeilen als eine einzige Partition analysieren.
ORDER BY expr2Sortiert die Zeilen innerhalb jeder Partition oder innerhalb des gesamten Zeilensatzes, wenn keine Partition angegeben ist. Diese ORDER BY-Klausel unterscheidet sich von der ORDER BY-Klausel, die die Reihenfolge aller Zeilen steuert, die im Endergebnis einer Abfrage zurückgegeben werden. Obwohl die ORDER BY-Klausel für einige Fensterfunktionen optional ist, ist sie für andere erforderlich. Zum Beispiel erfordern Ranking-Fensterfunktionen wie RANK und NTILE, dass ihre Eingabedaten in einer sinnvollen Reihenfolge vorliegen.
Die ORDER BY-Klausel für eine Fensterfunktion folgt ähnlichen Regeln wie die ORDER BY-Hauptklausel in einer Abfrage, was die Reihenfolge ASC/DESC (aufsteigend/absteigend) und die Behandlung von NULL betrifft. Weitere Details dazu finden Sie unter 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 wenden einen impliziten Fensterrahmen auf die ORDER BY-Klausel an. (Weitere Informationen dazu finden Sie unter Nutzungshinweise für Fensterrahmen.)
{ ROWS | RANGE }Gibt den Typ oder Modus des Fensterrahmens an, der entweder eine physische Anzahl von Zeilen (ROWS) oder eine logisch berechnete Menge von Zeilen (RANGE) definiert. Siehe Rangbezogene versus zeilenbezogene Fensterrahmen.
Beide Arten von Rahmen legen Anfangs- und Endpunkte fest, wobei entweder implizite benannte Grenzen oder explizite Offset-Werte verwendet werden. Eine benannte Begrenzung wird mit den Schlüsselwörtern CURRENT ROW, UNBOUNDED PRECEDING und UNBOUNDED FOLLOWING definiert. Explizite Offsets werden mit Zahlen oder Intervallen (
n PRECEDINGodern FOLLOWING) definiert.
{ RANGE BETWEEN n PRECEDING | n FOLLOWING }Gibt einen rangbezogenen Fensterrahmen mit expliziten Offsets an.
RANGE BETWEEN-Fensterrahmen mit expliziten Offsets dürfen nur einen ORDER BY-Ausdruck haben. Die folgenden Datentypen werden für diesen Ausdruck unterstützt:
DATE, TIMESTAMP, TIMESTAMP_LTZ , TIMESTAMP_NTZ (DATETIME) , TIMESTAMP_TZ
NUMBER, einschließlich INT, FLOAT und so weiter
TIME und andere Snowflake-Datentypen werden nicht unterstützt, wenn dieser Typ von Fensterrahmen verwendet wird. Für andere Fensterrahmen können andere Datentypen, wie z. B. VARCHAR im ORDER BY-Ausdruck verwendet werden.
Für RANGE BETWEEN-Fensterrahmen muss n eine Konstante ohne Vorzeichen (ein positiver numerischer Wert, einschließlich 0) oder eine positive INTERVAL-Konstante sein:
Wenn
expr2ein numerischer Datentyp ist, mussneine Konstante ohne Vorzeichen sein.Wenn
expr2ein TIMESTAMP-Datentyp ist, mussneine INTERVAL-Konstante sein. Beispiel:INTERVAL '12 hours'oderINTERVAL '3 days'Wenn
expr2ein DATE-Datentyp ist, kannneine Konstante ohne Vorzeichen oder eine INTERVAL-Konstante sein, aber der Anfang und das Ende des Rahmens müssen denselben Datentyp für den Wertnverwenden.
Wenn der Ausdruck ORDER BY aufsteigend ist (ASC), bedeutet die Syntax
n FOLLOWING„Zeilen mit Werten größer als (oder später als) x “, undn PRECEDINGbedeutet „Zeilen mit Werten kleiner als (oder früher als) x “, wobei x der ORDER BY-Wert für die aktuelle Zeile ist. Wenn der ORDER BY-Ausdruck absteigend ist (DESC), ist das Gegenteil der Fall. (Die Offsets0 PRECEDINGund0 FOLLOWINGsind äquivalent zu CURRENT ROW. )
RANGE-BETWEEN-Einschränkungen¶
Die folgende Untergruppe von Fensterfunktionen unterstützt die RANGE BETWEEN-Syntax mit expliziten Offsets:
STDDEV, STDDEV_SAMP, STDDEV_POP (und Aliasse)
VARIANCE , VARIANCE_SAMP, VARIANCE_POP (und Aliasse)
Beachten Sie darüber hinaus, dass:
DISTINCT-Versionen dieser Funktionen diese Syntax nicht unterstützen.
Die folgenden Beschränkungen gelten, wenn die Fensterfunktion COUNT mit dieser Syntax verwendet wird.
Es wird nur ein Eingabeargument unterstützt.
COUNT(table.*)-Platzhalterabfragen werden nicht unterstützt. Sie können zum Beispiel Folgendes nicht angeben:COUNT(t1.*) OVER(ORDER BY col1 RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING)
Sie können keinen Rahmen angeben, der zu einer logischen Umkehrung der Start- und Endposition des Rahmens führt. Die folgenden Rahmen zum Beispiel Fehler zurück, weil die letzte Zeile des Rahmens vor der ersten Zeile liegt:
ORDER BY col1 ASC RANGE BETWEEN 2 PRECEDING AND 4 PRECEDING ORDER BY col1 ASC RANGE BETWEEN 2 FOLLOWING AND 2 PRECEDING
RANGE BETWEEN-Verhaltensweise, wenn der Ausdruck ORDER BY-Ausdruck NULL-Werte enthält¶
Beachten Sie die folgende Verhaltensweise, wenn ein Fensterrahmen RANGE BETWEEN verwendet wird und die ORDER BY-Spalte NULL-Werte enthält:
Wenn die ORDER BY-Klausel NULLS FIRST angibt, werden Zeilen mit NULL in der ORDER BY-Spalte in die UNBOUNDED PRECEDING-Rahmen aufgenommen.
Wenn die ORDER BY-Klausel NULLS LAST angibt, werden Zeilen mit NULL in der ORDER BY-Spalte in die UNBOUNDED FOLLOWING-Rahmen aufgenommen.
Zeilen mit NULL in der ORDER BY-Spalte werden nur dann in einen Rahmen mit explizitem Offset einbezogen, wenn der ORDER BY-Wert der aktuellen Zeile NULL ist.
Siehe Beispiele für RANGE BETWEEN mit NULL-Werten in der ORDER BY-Klausel.
Nutzungshinweise für Fensterrahmen¶
Alle Fensterfunktionen unterstützen Fensterrahmen. Die Unterstützung für die Syntax von Fensterrahmen variiert jedoch je nach Funktion. Wenn kein Fensterrahmen angegeben ist, hängt der Standard von der Funktion ab:
Für Funktionen, die nicht rangbezogen sind (wie COUNT, MAX, MIN und SUM), wird standardmäßig der folgende Fensterrahmen verwendet (gemäß dem ANSI-Standard):
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
Bei rangbezogenen Funktionen (wie FIRST_VALUE, LAST_VALUE, NTH_VALUE) ist der Standard das gesamte Fenster:
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
Beachten Sie, dass diese Verhaltensweise nicht mit dem ANSI-Standard übereinstimmt.
Bemerkung
Snowflake empfiehlt, aus Gründen der Übersichtlichkeit auf implizite Fensterrahmen zu verzichten. Wenn Ihre Abfrage einen Fensterrahmen verwendet, definieren Sie einen expliziten Fensterrahmen.
Fensterrahmen erfordern, dass die Daten im Fenster in einer bekannten Reihenfolge vorliegen. Daher ist die ORDER BY-Klausel innerhalb der OVER Klausel für die Syntax des Fensterrahmens erforderlich, auch wenn die ORDER BY-Klausel im Allgemeinen optional ist.
Beispiele¶
Dieser Abschnitt enthält Beispiele, die zeigen, wie Fensterfunktionen auf unterschiedliche Weise verwendet werden können. Weitere Beispiele finden Sie unter Analysieren von Daten mit Fensterfunktionen und auf den Seiten für die einzelnen Funktionen.
Einführungsbeispiel¶
Angenommen, Sie besitzen eine Ladenkette. Die folgende Abfrage zeigt den prozentualen Anteil der einzelnen Filialen am Gesamtgewinn der Kette. Die Abfrage verwendet die Funktion RATIO_TO_REPORT, die einen Wert (net_profit) aus der aktuellen Zeile nimmt und ihn durch die Summe der entsprechenden Werte aus allen anderen Zeilen dividiert:
Erstellen und Laden der 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;
Fragen Sie die Tabelle ab:
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 |
+-----------+------------+-------------------------+
Fensterrahmen mit einer unbegrenzten Ausgangsposition¶
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 Fensterrahmen mit einer unbegrenzten Ausgangsposition verwendet, und zeigen Sie die Ausgabe an. Ermitteln der kumulierten Werte COUNT, SUM, AVG, MIN und MAX für jede Zeile in jeder Partition zurück:
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 derselben Ergebnisse wie in der obigen Abfrage aber durch Verwendung des Standard-Fensterrahmens (d. h. ROWS 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 |
+-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------+
Fensterrahmen mit expliziten Offsets¶
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 Ergebnisse MIN-Funktion für zwei Spalten (numerisch und Zeichenfolge) über gleitende Fenster 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 Ergebnisse MAX-Funktion für zwei Spalten (numerisch und Zeichenfolge) über gleitende Fenster 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 rangbezogene Funktionen¶
Das folgende Beispiel zeigt, wie die Rangfolge der Vertriebsmitarbeiter nach dem Gesamtverkaufsbetrag (in US-Dollar) erstellt wird. Die ORDER BY-Klausel innerhalb der OVER-Klausel sortiert die Summen in absteigender Reihenfolge (von der höchsten bis zur niedrigsten). Die Abfrage berechnet dann den Rang jedes Vertriebsmitarbeiters im Verhältnis zu den anderen Vertriebsmitarbeitern.
Erstellen Sie die Tabelle und fügen Sie die Daten ein:
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);
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 |
+------------------+------------------+------------+
Die Ausgabe ist nicht notwendigerweise nach Rang sortiert. Um die Ergebnisse nach Rang sortiert anzuzeigen, geben Sie eine ORDER BY-Klausel für die Abfrage selbst an (zusätzlich zur ORDER BY-Klausel für die Fensterfunktion), wie hier gezeigt:
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 |
+------------------+------------------+------------+
Das vorangegangene 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 |
+------------------+------------------+------------+
Beispiel für RANGE BETWEEN mit expliziten numerischen Offsets¶
Das folgende Beispiel verwendet die RANGE BETWEEN-Syntax mit expliziten numerischen Offsets. Um dieses Beispiel auszuführen, befolgen Sie die folgenden Anweisungen: Erstellen und Laden der „menu_items“-Tabelle. Ähnliche Beispiele, die INTERVAL-Offsets verwenden, finden Sie unter Verwenden von fensterbasierten Aggregationen für fortlaufende Berechnungen.
Die folgende Abfrage berechnet die durchschnittlichen Kosten der verkauften Waren für Kategorien von Menüartikeln, die in einem Imbisswagen erhältlich sind. Die Fensterfunktion partitioniert die Resultset nicht. Daher werden die Durchschnittswerte über das gesamte Resultset berechnet, wobei ein rangbezogener Rahmen gilt.
Die Begrenzung des Rahmens ist der Wert der Kosten für die Waren in der aktuellen Zeile plus zwei (z. B. die erste Zeile = 0,50 + 2,00). Zeilen sind für den Rahmen qualifiziert, wenn sie innerhalb dieses Zwei-Dollar-Bereichs liegen.
SELECT menu_category, menu_cogs_usd,
AVG(menu_cogs_usd)
OVER(ORDER BY menu_cogs_usd RANGE BETWEEN CURRENT ROW AND 2 FOLLOWING) avg_cogs
FROM menu_items
WHERE menu_category IN('Beverage','Dessert','Snack')
GROUP BY menu_category, menu_cogs_usd
ORDER BY menu_category, menu_cogs_usd;
+---------------+---------------+----------+
| MENU_CATEGORY | MENU_COGS_USD | AVG_COGS |
|---------------+---------------+----------|
| Beverage | 0.50 | 1.18333 |
| Beverage | 0.65 | 1.37857 |
| Beverage | 0.75 | 1.50000 |
| Dessert | 0.50 | 1.18333 |
| Dessert | 1.00 | 1.87500 |
| Dessert | 1.25 | 2.05000 |
| Dessert | 2.50 | 3.16666 |
| Dessert | 3.00 | 3.50000 |
| Snack | 1.25 | 2.05000 |
| Snack | 2.25 | 2.93750 |
| Snack | 4.00 | 4.00000 |
+---------------+---------------+----------+
Der Wert avg_cogs für die erste Zeile ist zum Beispiel 1.1833. Dies wird berechnet als die Summe aller menu_cogs_usd-Werte, die zwischen 0,50 und 2,50 liegen, geteilt durch die Anzahl dieser Zeilen:
(0.50 + 0.65 + 0.75 + 0.50 + 1.00 + 1.25 + 2.50 + 1.25 + 2.25) / 9 = 1.18333
Die vorletzte Zeile hat einen avg_cogs-Wert von 2,93750. Dies wird berechnet als die Summe aller menu_cogs_usd-Werte, die zwischen 2,25 und 4,25 liegen, geteilt durch die Anzahl dieser Zeilen:
(2.25 + 2.50 + 3.00 + 4.00) / 4 = 2.93750
Die letzte Zeile ergibt 4,0 sowohl für avg_cogs als auch für menu_cogs_usd. Dieses Ergebnis ist genau, weil nur diese Zeile zu dem Rahmen gehört. 4.0 ist der Höchstwert von menu_cogs_usd im gesamten Ergebnis, sodass es sich um einen einzeiligen Rahmen handelt. Es hat keine „folgenden“ Zeilen.
Beachten Sie, dass diese Abfrage eine ORDER BY-Klausel für die Fensterfunktion und eine ORDER BY-Klausel für die Endergebnisse der Abfrage enthält. Die endgültige ORDER BY-Ausgabe hat keinen Einfluss auf die Berechnung der Ergebnisse der Fensterfunktion. Das geordnete Resultset zur Berechnung der Funktion ist ein Zwischen-Resultset, das in der endgültigen Abfrage nicht angezeigt wird.
Beispiele für RANGE BETWEEN mit NULL-Werten in der ORDER BY-Klausel¶
Die Tabelle nulls enthält fünf Zeilen und zwei davon haben NULL-Werte in der Spalte c1. Erstellen und laden Sie die Tabelle wie folgt:
CREATE OR REPLACE TABLE nulls(c1 int, c2 int);
INSERT INTO nulls VALUES
(1,10),
(2,20),
(3,30),
(NULL,20),
(NULL,50);
Wenn NULLS LAST angegeben ist und der Fensterrahmen explizite Offsets verwendet, werden Zeilen mit NULL in c1 nur dann in den Rahmen aufgenommen, wenn der Wert ORDER BY der aktuellen Zeile NULL ist. Die folgende Abfrage gibt eine Summe von 50 zurück, wenn die Zeile 3 die aktuelle Zeile ist. Die folgende NULL-Zeile ist nicht im Rahmen enthalten.
SELECT c1 c1_nulls_last, c2,
SUM(c2) OVER(ORDER BY c1 NULLS LAST RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) sum_c2
FROM nulls;
+---------------+----+--------+
| C1_NULLS_LAST | C2 | SUM_C2 |
|---------------+----+--------|
| 1 | 10 | 30 |
| 2 | 20 | 60 |
| 3 | 30 | 50 |
| NULL | 20 | 70 |
| NULL | 50 | 70 |
+---------------+----+--------+
Wenn NULLS LAST angegeben ist und der Fensterrahmen UNBOUNDED FOLLOWING verwendet, werden Zeilen mit NULL in c1 in den Rahmen aufgenommen. Die folgende Abfrage gibt eine Summe von 120 zurück, wenn die Zeile 3 die aktuelle Zeile ist. Die beiden folgenden NULL-Zeilen sind in dem Rahmen enthalten.
SELECT c1 c1_nulls_last, c2,
SUM(c2) OVER(ORDER BY c1 NULLS LAST RANGE BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING) sum_c2
FROM nulls;
+---------------+----+--------+
| C1_NULLS_LAST | C2 | SUM_C2 |
|---------------+----+--------|
| 1 | 10 | 130 |
| 2 | 20 | 130 |
| 3 | 30 | 120 |
| NULL | 20 | 70 |
| NULL | 50 | 70 |
+---------------+----+--------+
Erstellen und laden Sie die Tabelle heavy_weather¶
Führen Sie dieses Skript aus, um Zeilen in der Tabelle heavy_weather zu erstellen und einzufügen, die in einigen Beispielen für Fensterfunktionen verwendet wird.
CREATE OR REPLACE TABLE heavy_weather
(start_time TIMESTAMP, precip NUMBER(3,2), city VARCHAR(20), county VARCHAR(20));
INSERT INTO heavy_weather VALUES
('2021-12-23 06:56:00.000',0.08,'Mount Shasta','Siskiyou'),
('2021-12-23 07:51:00.000',0.09,'Mount Shasta','Siskiyou'),
('2021-12-23 16:23:00.000',0.56,'South Lake Tahoe','El Dorado'),
('2021-12-23 17:24:00.000',0.38,'South Lake Tahoe','El Dorado'),
('2021-12-23 18:30:00.000',0.28,'South Lake Tahoe','El Dorado'),
('2021-12-23 19:35:00.000',0.37,'Mammoth Lakes','Mono'),
('2021-12-23 19:36:00.000',0.80,'South Lake Tahoe','El Dorado'),
('2021-12-24 04:43:00.000',0.25,'Alta','Placer'),
('2021-12-24 05:26:00.000',0.34,'Alta','Placer'),
('2021-12-24 05:35:00.000',0.42,'Big Bear City','San Bernardino'),
('2021-12-24 06:49:00.000',0.17,'South Lake Tahoe','El Dorado'),
('2021-12-24 07:40:00.000',0.07,'Alta','Placer'),
('2021-12-24 08:36:00.000',0.07,'Alta','Placer'),
('2021-12-24 11:52:00.000',0.08,'Alta','Placer'),
('2021-12-24 12:52:00.000',0.38,'Alta','Placer'),
('2021-12-24 15:44:00.000',0.13,'Alta','Placer'),
('2021-12-24 15:53:00.000',0.07,'South Lake Tahoe','El Dorado'),
('2021-12-24 16:55:00.000',0.09,'Big Bear City','San Bernardino'),
('2021-12-24 21:53:00.000',0.07,'Montague','Siskiyou'),
('2021-12-25 02:52:00.000',0.07,'Alta','Placer'),
('2021-12-25 07:52:00.000',0.07,'Alta','Placer'),
('2021-12-25 08:52:00.000',0.08,'Alta','Placer'),
('2021-12-25 09:48:00.000',0.18,'Alta','Placer'),
('2021-12-25 12:52:00.000',0.10,'Alta','Placer'),
('2021-12-25 17:21:00.000',0.23,'Alturas','Modoc'),
('2021-12-25 17:52:00.000',1.54,'Alta','Placer'),
('2021-12-26 01:52:00.000',0.61,'Alta','Placer'),
('2021-12-26 05:43:00.000',0.16,'South Lake Tahoe','El Dorado'),
('2021-12-26 05:56:00.000',0.08,'Bishop','Inyo'),
('2021-12-26 06:52:00.000',0.75,'Bishop','Inyo'),
('2021-12-26 06:53:00.000',0.08,'Lebec','Los Angeles'),
('2021-12-26 07:52:00.000',0.65,'Alta','Placer'),
('2021-12-26 09:52:00.000',2.78,'Alta','Placer'),
('2021-12-26 09:55:00.000',0.07,'Big Bear City','San Bernardino'),
('2021-12-26 14:22:00.000',0.32,'Alta','Placer'),
('2021-12-26 14:52:00.000',0.34,'Alta','Placer'),
('2021-12-26 15:43:00.000',0.35,'Alta','Placer'),
('2021-12-26 17:31:00.000',5.24,'Alta','Placer'),
('2021-12-26 22:52:00.000',0.07,'Alta','Placer'),
('2021-12-26 23:15:00.000',0.52,'Alta','Placer'),
('2021-12-27 02:52:00.000',0.08,'Alta','Placer'),
('2021-12-27 03:52:00.000',0.14,'Alta','Placer'),
('2021-12-27 04:52:00.000',1.52,'Alta','Placer'),
('2021-12-27 14:37:00.000',0.89,'Alta','Placer'),
('2021-12-27 14:53:00.000',0.07,'South Lake Tahoe','El Dorado'),
('2021-12-27 17:53:00.000',0.07,'South Lake Tahoe','El Dorado'),
('2021-12-30 11:23:00.000',0.12,'Lebec','Los Angeles'),
('2021-12-30 11:43:00.000',0.98,'Lebec','Los Angeles'),
('2021-12-30 13:53:00.000',0.23,'Lebec','Los Angeles'),
('2021-12-30 14:53:00.000',0.13,'Lebec','Los Angeles'),
('2021-12-30 15:15:00.000',0.29,'Lebec','Los Angeles'),
('2021-12-30 17:53:00.000',0.10,'Lebec','Los Angeles'),
('2021-12-30 18:53:00.000',0.09,'Lebec','Los Angeles'),
('2021-12-30 19:53:00.000',0.07,'Lebec','Los Angeles'),
('2021-12-30 20:53:00.000',0.07,'Lebec','Los Angeles')
;