Fensterfunktionen

DuckDB unterstützt Fensterfunktionen, die mehrere Zeilen verwenden können, um für jede Zeile einen Wert zu berechnen. Fensterfunktionen sind blockierende Operatoren, d. h. sie müssen ihre gesamte Eingabe puffern und gehören damit zu den speicherintensivsten Operatoren in SQL.

Fensterfunktionen gibt es in SQL seit SQL:2003 und sie werden von den großen SQL-Datenbanksystemen unterstützt.

Beispiele

Erzeugt eine Spalte row_number, um Zeilen zu nummerieren:

SELECT row_number() OVER ()
FROM sales;

Tipp Wenn Sie nur eine Nummer für jede Zeile in einer Tabelle brauchen, können Sie die Pseudospalte rowid verwenden.

Erzeugt eine Spalte row_number, um Zeilen nach time geordnet zu nummerieren:

SELECT row_number() OVER (ORDER BY time)
FROM sales;

Erzeugt eine Spalte row_number, um Zeilen nach time geordnet und nach region partitioniert zu nummerieren:

SELECT row_number() OVER (PARTITION BY region ORDER BY time)
FROM sales;

Berechnet die Differenz zwischen dem aktuellen und dem vorherigen (nach time) amount:

SELECT amount - lag(amount) OVER (ORDER BY time)
FROM sales;

Berechnet den Anteil am gesamten amount der Verkäufe pro region für jede Zeile:

SELECT amount / sum(amount) OVER (PARTITION BY region)
FROM sales;

Syntax

Fensterfunktionen können nur in der SELECT-Klausel verwendet werden. Um OVER-Spezifikationen zwischen Funktionen zu teilen, verwenden Sie die WINDOW-Klausel der Anweisung und die Syntax OVER ⟨window_name⟩{:.language-sql .highlight}.

Allgemeine Fensterfunktionen

Die folgende Tabelle zeigt die verfügbaren allgemeinen Fensterfunktionen.

Name	Beschreibung
`cume_dist([ORDER BY ordering])`	Die kumulative Verteilung: (Anzahl der Partitionszeilen vor oder gleichrangig mit der aktuellen Zeile) / Gesamtzahl der Partitionszeilen.
`dense_rank()`	Der Rang der aktuellen Zeile ohne Lücken; diese Funktion zählt Peer-Gruppen.
`fill(expr [ ORDER BY ordering])`	Füllt fehlende Werte per linearer Interpolation, mit `ORDER BY` als X-Achse.
`first_value(expr[ ORDER BY ordering][ IGNORE NULLS])`	Gibt `expr` ausgewertet an der Zeile zurück, die die erste Zeile (mit einem Nicht-Null-Wert von `expr`, wenn `IGNORE NULLS` gesetzt ist) des Fensterrahmens ist.
`lag(expr[, offset[, default]][ ORDER BY ordering][ IGNORE NULLS])`	Gibt `expr` ausgewertet an der Zeile zurück, die `offset` Zeilen (unter Zeilen mit einem Nicht-Null-Wert von `expr`, wenn `IGNORE NULLS` gesetzt ist) vor der aktuellen Zeile im Fensterrahmen liegt; gibt es keine solche Zeile, wird stattdessen `default` zurückgegeben (muss denselben Typ wie `expr` haben). Sowohl `offset` als auch `default` werden bezüglich der aktuellen Zeile ausgewertet. Wird `offset` weggelassen, ist der Standard `1` und default `NULL`.
`last_value(expr[ ORDER BY ordering][ IGNORE NULLS])`	Gibt `expr` ausgewertet an der Zeile zurück, die die letzte Zeile (unter Zeilen mit einem Nicht-Null-Wert von `expr`, wenn `IGNORE NULLS` gesetzt ist) des Fensterrahmens ist.
`lead(expr[, offset[, default]][ ORDER BY ordering][ IGNORE NULLS])`	Gibt `expr` ausgewertet an der Zeile zurück, die `offset` Zeilen nach der aktuellen Zeile (unter Zeilen mit einem Nicht-Null-Wert von `expr`, wenn `IGNORE NULLS` gesetzt ist) im Fensterrahmen liegt; gibt es keine solche Zeile, wird stattdessen `default` zurückgegeben (muss denselben Typ wie `expr` haben). Sowohl `offset` als auch `default` werden bezüglich der aktuellen Zeile ausgewertet. Wird `offset` weggelassen, ist der Standard `1` und default `NULL`.
`nth_value(expr, nth[ ORDER BY ordering][ IGNORE NULLS])`	Gibt `expr` ausgewertet an der n-ten Zeile (unter Zeilen mit einem Nicht-Null-Wert von `expr`, wenn `IGNORE NULLS` gesetzt ist) des Fensterrahmens zurück (Zählung ab 1); `NULL`, wenn es keine solche Zeile gibt.
`ntile(num_buckets[ ORDER BY ordering])`	Eine ganze Zahl von 1 bis `num_buckets`, die die Partition möglichst gleichmäßig teilt.
`percent_rank([ORDER BY ordering])`	Der relative Rang der aktuellen Zeile: `(rank() - 1) / (total partition rows - 1)`.
`rank([ORDER BY ordering])`	Der Rang der aktuellen Zeile mit Lücken; gleich dem `row_number` ihres ersten Peers.
`row_number([ORDER BY ordering])`	Die Nummer der aktuellen Zeile innerhalb der Partition, Zählung ab 1.

`cume_dist([ORDER BY ordering])`

| Beschreibung | Die kumulative Verteilung: (Anzahl der Partitionszeilen vor oder gleichrangig mit der aktuellen Zeile) / Gesamtzahl der Partitionszeilen. Ist eine ORDER BY-Klausel angegeben, wird die Verteilung innerhalb des Rahmens mit der angegebenen Ordnung statt der Rahmenordnung berechnet. | | Rückgabetyp | DOUBLE | | Beispiel | cume_dist() |

`dense_rank()`

`fill(expr[ ORDER BY ordering])`

| Beschreibung | Ersetzt NULL-Werte von expr durch eine lineare Interpolation auf Basis der nächsten Nicht-NULL-Werte und der Sortierwerte. Beide Werte müssen Arithmetik unterstützen, und es darf nur einen Ordnungsschlüssel geben. Für fehlende Werte an den Enden wird lineare Extrapolation verwendet. Scheitert die Interpolation, bleibt der NULL-Wert erhalten. | | Rückgabetyp | Derselbe Typ wie expr | | Beispiel | fill(column) |

`first_value(expr[ ORDER BY ordering][ IGNORE NULLS])`

| Beschreibung | Gibt expr ausgewertet an der Zeile zurück, die die erste Zeile (mit einem Nicht-Null-Wert von expr, wenn IGNORE NULLS gesetzt ist) des Fensterrahmens ist. Ist eine ORDER BY-Klausel angegeben, wird die erste Zeilennummer innerhalb des Rahmens mit der angegebenen Ordnung statt der Rahmenordnung berechnet. | | Rückgabetyp | Derselbe Typ wie expr | | Beispiel | first_value(column) |

`lag(expr[, offset[, default]][ ORDER BY ordering][ IGNORE NULLS])`

| Beschreibung | Gibt expr ausgewertet an der Zeile zurück, die offset Zeilen (unter Zeilen mit einem Nicht-Null-Wert von expr, wenn IGNORE NULLS gesetzt ist) vor der aktuellen Zeile im Fensterrahmen liegt; gibt es keine solche Zeile, wird stattdessen default zurückgegeben (muss denselben Typ wie expr haben). Sowohl offset als auch default werden bezüglich der aktuellen Zeile ausgewertet. Wird offset weggelassen, ist der Standard 1 und default NULL. Ist eine ORDER BY-Klausel angegeben, wird die verzögerte Zeilennummer innerhalb des Rahmens mit der angegebenen Ordnung statt der Rahmenordnung berechnet. | | Rückgabetyp | Derselbe Typ wie expr | | Beispiel | lag(column, 3, 0) |

`last_value(expr[ ORDER BY ordering][ IGNORE NULLS])`

| Beschreibung | Gibt expr ausgewertet an der Zeile zurück, die die letzte Zeile (unter Zeilen mit einem Nicht-Null-Wert von expr, wenn IGNORE NULLS gesetzt ist) des Fensterrahmens ist. Wird offset weggelassen, ist der Standard 1 und default NULL. Ist eine ORDER BY-Klausel angegeben, wird die letzte Zeile innerhalb des Rahmens mit der angegebenen Ordnung statt der Rahmenordnung bestimmt. | | Rückgabetyp | Derselbe Typ wie expr | | Beispiel | last_value(column) |

`lead(expr[, offset[, default]][ ORDER BY ordering][ IGNORE NULLS])`

| Beschreibung | Gibt expr ausgewertet an der Zeile zurück, die offset Zeilen nach der aktuellen Zeile (unter Zeilen mit einem Nicht-Null-Wert von expr, wenn IGNORE NULLS gesetzt ist) im Fensterrahmen liegt; gibt es keine solche Zeile, wird stattdessen default zurückgegeben (muss denselben Typ wie expr haben). Sowohl offset als auch default werden bezüglich der aktuellen Zeile ausgewertet. Wird offset weggelassen, ist der Standard 1 und default NULL. Ist eine ORDER BY-Klausel angegeben, wird die vorausliegende Zeilennummer innerhalb des Rahmens mit der angegebenen Ordnung statt der Rahmenordnung berechnet. | | Rückgabetyp | Derselbe Typ wie expr | | Beispiel | lead(column, 3, 0) |

`nth_value(expr, nth[ ORDER BY ordering][ IGNORE NULLS])`

| Beschreibung | Gibt expr ausgewertet an der n-ten Zeile (unter Zeilen mit einem Nicht-Null-Wert von expr, wenn IGNORE NULLS gesetzt ist) des Fensterrahmens zurück (Zählung ab 1); NULL, wenn es keine solche Zeile gibt. Ist eine ORDER BY-Klausel angegeben, wird die n-te Zeilennummer innerhalb des Rahmens mit der angegebenen Ordnung statt der Rahmenordnung berechnet. | | Rückgabetyp | Derselbe Typ wie expr | | Beispiel | nth_value(column, 2) |

`ntile(num_buckets[ ORDER BY ordering])`

| Beschreibung | Eine ganze Zahl von 1 bis num_buckets, die die Partition möglichst gleichmäßig teilt. Ist eine ORDER BY-Klausel angegeben, wird das ntile innerhalb des Rahmens mit der angegebenen Ordnung statt der Rahmenordnung berechnet. | | Rückgabetyp | BIGINT | | Beispiel | ntile(4) |

`percent_rank([ORDER BY ordering])`

| Beschreibung | Der relative Rang der aktuellen Zeile: (rank() - 1) / (total partition rows - 1). Ist eine ORDER BY-Klausel angegeben, wird der relative Rang innerhalb des Rahmens mit der angegebenen Ordnung statt der Rahmenordnung berechnet. | | Rückgabetyp | DOUBLE | | Beispiel | percent_rank() |

`rank([ORDER BY ordering])`

| Beschreibung | Der Rang der aktuellen Zeile mit Lücken; gleich dem row_number ihres ersten Peers. Ist eine ORDER BY-Klausel angegeben, wird der Rang innerhalb des Rahmens mit der angegebenen Ordnung statt der Rahmenordnung berechnet. | | Rückgabetyp | BIGINT | | Beispiel | rank() |

`row_number([ORDER BY ordering])`

| Beschreibung | Die Nummer der aktuellen Zeile innerhalb der Partition, Zählung ab 1. Ist eine ORDER BY-Klausel angegeben, wird die Zeilennummer innerhalb des Rahmens mit der angegebenen Ordnung statt der Rahmenordnung berechnet. | | Rückgabetyp | BIGINT | | Beispiel | row_number() |

Aggregat-Fensterfunktionen

Alle Aggregatfunktionen können in einem Fensterkontext verwendet werden, einschließlich der optionalen FILTER-Klausel. Die Aggregatfunktionen first und last werden von den jeweiligen allgemeinen Fensterfunktionen überdeckt, mit der kleinen Folge, dass die FILTER-Klausel für diese nicht verfügbar ist, IGNORE NULLS jedoch schon.

`DISTINCT`-Argumente

Alle Aggregat-Fensterfunktionen unterstützen eine DISTINCT-Klausel für die Argumente. Ist die DISTINCT-Klausel angegeben, werden nur eindeutige Werte in die Berechnung des Aggregats einbezogen. Das wird typischerweise zusammen mit dem Aggregat COUNT verwendet, um die Anzahl eindeutiger Elemente zu erhalten; es kann aber mit jeder Aggregatfunktion im System verwendet werden. Es gibt Aggregate, die unempfindlich gegenüber Duplikaten sind (z. B. min, max); für diese wird die Klausel geparst und ignoriert.

-- Count the number of distinct users at a given point in time
SELECT count(DISTINCT name) OVER (ORDER BY time) FROM sales;
-- Concatenate those distinct users into a list
SELECT list(DISTINCT name) OVER (ORDER BY time) FROM sales;

`ORDER BY`-Argumente

Alle Aggregat-Fensterfunktionen unterstützen eine ORDER BY-Argumentklausel, die sich von der Fensterordnung unterscheidet. Ist die ORDER BY-Argumentklausel angegeben, werden die zu aggregierenden Werte vor Anwendung der Funktion sortiert. Normalerweise ist das unwichtig, aber es gibt ordnungssensitive Aggregate, die unbestimmte Ergebnisse haben können (z. B. mode, list und string_agg). Diese können durch das Ordnen der Argumente deterministisch gemacht werden. Für ordnungsunempfindliche Aggregate wird diese Klausel geparst und ignoriert.

-- Compute the modal value up to each time, breaking ties in favor of the most recent value.
SELECT mode(value ORDER BY time DESC) OVER (ORDER BY time) FROM sales;

Der SQL-Standard sieht ORDER BY bei allgemeinen Fensterfunktionen nicht vor, wir haben jedoch alle dieser Funktionen (außer dense_rank) so erweitert, dass sie diese Syntax akzeptieren und Framing verwenden, um den Bereich einzuschränken, auf den die sekundäre Ordnung angewendet wird.

-- Compare each athlete's time in an event with the best time to date
SELECT
    event,
    date,
    athlete,
    time,
    first_value(time ORDER BY time ASC) OVER w AS record_time,
    first_value(athlete ORDER BY time ASC) OVER w AS record_athlete,
FROM meet_results
WINDOW w AS (PARTITION BY event ORDER BY datetime)
ORDER BY ALL;

Beachten Sie, dass zwischen den Argumenten und der ORDER BY-Klausel kein Komma steht.

Nulls

Alle allgemeinen Fensterfunktionen, die IGNORE NULLS akzeptieren, respektieren Nulls standardmäßig. Dieses Standardverhalten kann optional mit RESPECT NULLS explizit gemacht werden.

Im Gegensatz dazu ignorieren alle Aggregat-Fensterfunktionen (außer list und seinen Aliasen, die Nulls über ein FILTER ignorieren können) Nulls und akzeptieren kein RESPECT NULLS. Zum Beispiel berechnet sum(column) OVER (ORDER BY time) AS cumulativeColumn eine kumulierte Summe, bei der Zeilen mit einem NULL-Wert von column denselben Wert von cumulativeColumn haben wie die Zeile davor.

Auswertung

Das Fenstern zerlegt eine Relation in unabhängige Partitionen, ordnet diese Partitionen und berechnet dann für jede Zeile eine neue Spalte als Funktion der benachbarten Werte. Einige Fensterfunktionen hängen nur von der Partitionsgrenze und der Ordnung ab, einige wenige (einschließlich aller Aggregate) verwenden außerdem einen Rahmen. Rahmen werden als Anzahl von Zeilen auf beiden Seiten (preceding oder following) der aktuellen Zeile angegeben. Die Distanz kann als Anzahl von Zeilen (ROWS), als Wertebereich (RANGE) anhand des Ordnungswerts der Partition und einer Distanz oder als Anzahl von Gruppen (Mengen von Zeilen mit demselben Sortierwert) angegeben werden.

Die vollständige Syntax ist im Diagramm oben auf der Seite gezeigt, und dieses Diagramm veranschaulicht die Auswertungsumgebung:

Partition und Ordnung

Das Partitionieren zerlegt die Relation in unabhängige, voneinander unabhängige Teile. Das Partitionieren ist optional; ist keines angegeben, wird die gesamte Relation als eine einzige Partition behandelt. Fensterfunktionen können nicht auf Werte außerhalb der Partition zugreifen, die die Zeile enthält, an der sie ausgewertet werden.

Die Ordnung ist ebenfalls optional, aber ohne sie sind die Ergebnisse allgemeiner Fensterfunktionen und ordnungssensitiver Aggregatfunktionen sowie die Reihenfolge des Framings nicht wohldefiniert. Jede Partition wird mit derselben Ordnungsklausel geordnet.

Hier ist eine Tabelle mit Stromerzeugungsdaten, verfügbar als CSV-Datei (power-plant-generation-history.csv). Zum Laden der Daten führen Sie aus:

CREATE TABLE "Generation History" AS
    FROM 'power-plant-generation-history.csv';

Nach dem Partitionieren nach Kraftwerk und dem Ordnen nach Datum hat sie dieses Layout:

Plant	Date	MWh
Boston	2019-01-02	564337
Boston	2019-01-03	507405
Boston	2019-01-04	528523
Boston	2019-01-05	469538
Boston	2019-01-06	474163
Boston	2019-01-07	507213
Boston	2019-01-08	613040
Boston	2019-01-09	582588
Boston	2019-01-10	499506
Boston	2019-01-11	482014
Boston	2019-01-12	486134
Boston	2019-01-13	531518
Worcester	2019-01-02	118860
Worcester	2019-01-03	101977
Worcester	2019-01-04	106054
Worcester	2019-01-05	92182
Worcester	2019-01-06	94492
Worcester	2019-01-07	99932
Worcester	2019-01-08	118854
Worcester	2019-01-09	113506
Worcester	2019-01-10	96644
Worcester	2019-01-11	93806
Worcester	2019-01-12	98963
Worcester	2019-01-13	107170

Im Folgenden verwenden wir diese Tabelle (oder kleine Ausschnitte davon), um verschiedene Teile der Auswertung von Fensterfunktionen zu veranschaulichen.

Die einfachste Fensterfunktion ist row_number(). Diese Funktion berechnet nur die 1-basierte Zeilennummer innerhalb der Partition mit der Abfrage:

SELECT
    "Plant",
    "Date",
    row_number() OVER (PARTITION BY "Plant" ORDER BY "Date") AS "Row"
FROM "Generation History"
ORDER BY 1, 2;

Das Ergebnis ist:

Plant	Date	Row
Boston	2019-01-02	1
Boston	2019-01-03	2
Boston	2019-01-04	3
…	…	…
Worcester	2019-01-02	1
Worcester	2019-01-03	2
Worcester	2019-01-04	3
…	…	…

Beachten Sie, dass das Ergebnis selbst dann nicht sortiert sein muss, wenn die Funktion mit einer ORDER BY-Klausel berechnet wird; das SELECT muss also explizit sortiert werden, wenn das gewünscht ist.

Framing

Framing legt eine Menge von Zeilen relativ zu jeder Zeile fest, an der die Funktion ausgewertet wird. Die Distanz von der aktuellen Zeile wird als Ausdruck entweder PRECEDING oder FOLLOWING der aktuellen Zeile in der durch die ORDER BY-Klausel in der OVER-Spezifikation angegebenen Ordnung angegeben. Diese Distanz kann entweder als ganzzahlige Anzahl von ROWS oder GROUPS oder als RANGE-Delta-Ausdruck angegeben werden. Es ist ungültig, wenn ein Rahmen nach seinem Ende beginnt. Für eine RANGE-Spezifikation darf es nur einen Ordnungsausdruck geben, und er muss Subtraktion unterstützen, es sei denn, es werden nur die Sentinel-Grenzwerte UNBOUNDED PRECEDING / UNBOUNDED FOLLOWING / CURRENT ROW verwendet. Mit der EXCLUDE-Klausel können Zeilen, die im angegebenen Ordnungsausdruck gleich der aktuellen Zeile sind (sogenannte Peers), aus dem Rahmen ausgeschlossen werden.

Der Standardrahmen ist unbegrenzt (d. h. die gesamte Partition), wenn keine ORDER BY-Klausel vorhanden ist, und RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, wenn eine ORDER BY-Klausel vorhanden ist. Standardmäßig bedeutet der Grenzwert CURRENT ROW (aber nicht das CURRENT ROW in der EXCLUDE-Klausel) die aktuelle Zeile und alle ihre Peers, wenn RANGE- oder GROUP-Framing verwendet wird, aber nur die aktuelle Zeile, wenn ROWS-Framing verwendet wird.

`ROWS`-Framing

Hier ist eine einfache ROW-Rahmenabfrage mit einer Aggregatfunktion:

SELECT points,
    sum(points) OVER (
        ROWS BETWEEN 1 PRECEDING
                 AND 1 FOLLOWING) AS we
FROM results;

Diese Abfrage berechnet die sum jedes Punkts und der Punkte zu beiden Seiten:

Beachten Sie, dass am Rand der Partition nur zwei Werte addiert werden. Das liegt daran, dass Rahmen an den Rand der Partition beschnitten werden.

`RANGE`-Framing

Zurück zu den Stromdaten: Angenommen, die Daten sind verrauscht. Wir möchten vielleicht einen 7-Tage-gleitenden Durchschnitt für jedes Kraftwerk berechnen, um das Rauschen zu glätten. Dazu können wir diese Fensterabfrage verwenden:

SELECT "Plant", "Date",
    avg("MWh") OVER (
        PARTITION BY "Plant"
        ORDER BY "Date" ASC
        RANGE BETWEEN INTERVAL 3 DAYS PRECEDING
                  AND INTERVAL 3 DAYS FOLLOWING)
        AS "MWh 7-day Moving Average"
FROM "Generation History"
ORDER BY 1, 2;

Diese Abfrage partitioniert die Daten nach Plant (um die Daten der verschiedenen Kraftwerke getrennt zu halten), ordnet die Partition jedes Kraftwerks nach Date (um die Energiemessungen nebeneinander zu legen) und verwendet einen RANGE-Rahmen von drei Tagen auf beiden Seiten jedes Tages für den avg (um fehlende Tage zu behandeln). Das ist das Ergebnis:

Plant	Date	MWh 7-day Moving Average
Boston	2019-01-02	517450.75
Boston	2019-01-03	508793.20
Boston	2019-01-04	508529.83
…	…	…
Boston	2019-01-13	499793.00
Worcester	2019-01-02	104768.25
Worcester	2019-01-03	102713.00
Worcester	2019-01-04	102249.50
…	…	…

`GROUPS`-Framing

Die dritte Framing-Art zählt Gruppen von Zeilen relativ zur aktuellen Zeile. Eine Gruppe in diesem Framing ist eine Menge von Werten mit identischen ORDER BY-Werten. Wenn wir annehmen, dass an jedem Tag Strom erzeugt wird, können wir GROUPS-Framing verwenden, um den gleitenden Durchschnitt allen im System erzeugten Stroms zu berechnen, ohne Datumsarithmetik zu brauchen:

SELECT "Date", "Plant",
    avg("MWh") OVER (
        ORDER BY "Date" ASC
        GROUPS BETWEEN 3 PRECEDING
                   AND 3 FOLLOWING)
        AS "MWh 7-day Moving Average"
FROM "Generation History"
ORDER BY 1, 2;

Date	Plant	MWh 7-day Moving Average
2019-01-02	Boston	311109.500
2019-01-02	Worcester	311109.500
2019-01-03	Boston	305753.100
2019-01-03	Worcester	305753.100
2019-01-04	Boston	305389.667
2019-01-04	Worcester	305389.667
…	…	…
2019-01-12	Boston	309184.900
2019-01-12	Worcester	309184.900
2019-01-13	Boston	299469.375
2019-01-13	Worcester	299469.375

Beachten Sie, dass die Werte für jedes Datum gleich sind.

`EXCLUDE`-Klausel

EXCLUDE ist ein optionaler Modifikator der Rahmenklausel, um Zeilen um die CURRENT ROW auszuschließen. Das ist nützlich, wenn Sie einen Aggregatwert benachbarter Zeilen berechnen möchten, um zu sehen, wie die aktuelle Zeile damit verglichen wird.

Im folgenden Beispiel möchten wir wissen, wie die Zeit eines Athleten in einem Wettkampf im Vergleich zum Durchschnitt aller für ihren Wettkampf innerhalb von ±10 Tagen erfassten Zeiten steht:

SELECT
    event,
    date,
    athlete,
    avg(time) OVER w AS recent,
FROM results
WINDOW w AS (
    PARTITION BY event
    ORDER BY date
    RANGE BETWEEN INTERVAL 10 DAYS PRECEDING AND INTERVAL 10 DAYS FOLLOWING
        EXCLUDE CURRENT ROW
)
ORDER BY event, date, athlete;

Es gibt vier Optionen für EXCLUDE, die festlegen, wie die aktuelle Zeile behandelt wird:

CURRENT ROW – nur die aktuelle Zeile ausschließen
GROUP – die aktuelle Zeile und alle ihre „Peers“ ausschließen (Zeilen mit demselben ORDER BY-Wert)
TIES – alle Peer-Zeilen ausschließen, aber nicht die aktuelle Zeile (das erzeugt ein Loch auf beiden Seiten)
NO OTHERS – nichts ausschließen (der Standard)

Der Ausschluss ist sowohl für Fensteraggregate als auch für die Funktionen first, last und nth_value implementiert.

`WINDOW`-Klauseln

Mehrere unterschiedliche OVER-Klauseln können im selben SELECT angegeben werden, und jede wird getrennt berechnet. Oft möchten wir jedoch dasselbe Layout für mehrere Fensterfunktionen verwenden. Die WINDOW-Klausel kann verwendet werden, um ein benanntes Fenster zu definieren, das zwischen mehreren Fensterfunktionen geteilt werden kann:

SELECT "Plant", "Date",
    min("MWh") OVER seven AS "MWh 7-day Moving Minimum",
    avg("MWh") OVER seven AS "MWh 7-day Moving Average",
    max("MWh") OVER seven AS "MWh 7-day Moving Maximum"
FROM "Generation History"
WINDOW seven AS (
    PARTITION BY "Plant"
    ORDER BY "Date" ASC
    RANGE BETWEEN INTERVAL 3 DAYS PRECEDING
              AND INTERVAL 3 DAYS FOLLOWING)
ORDER BY 1, 2;

Die drei Fensterfunktionen teilen sich außerdem das Datenlayout, was die Leistung verbessert.

Mehrere Fenster können in derselben WINDOW-Klausel durch Kommas getrennt definiert werden:

SELECT "Plant", "Date",
    min("MWh") OVER seven AS "MWh 7-day Moving Minimum",
    avg("MWh") OVER seven AS "MWh 7-day Moving Average",
    max("MWh") OVER seven AS "MWh 7-day Moving Maximum",
    min("MWh") OVER three AS "MWh 3-day Moving Minimum",
    avg("MWh") OVER three AS "MWh 3-day Moving Average",
    max("MWh") OVER three AS "MWh 3-day Moving Maximum"
FROM "Generation History"
WINDOW
    seven AS (
        PARTITION BY "Plant"
        ORDER BY "Date" ASC
        RANGE BETWEEN INTERVAL 3 DAYS PRECEDING
                  AND INTERVAL 3 DAYS FOLLOWING),
    three AS (
        PARTITION BY "Plant"
        ORDER BY "Date" ASC
        RANGE BETWEEN INTERVAL 1 DAYS PRECEDING
        AND INTERVAL 1 DAYS FOLLOWING)
ORDER BY 1, 2;

Die Abfragen oben verwenden eine Reihe von Klauseln nicht, die in Select-Anweisungen üblich sind, etwa WHERE, GROUP BY usw. Für komplexere Abfragen finden Sie, wo WINDOW-Klauseln in der kanonischen Reihenfolge der SELECT-Anweisung stehen.

Ergebnisse von Fensterfunktionen mit `QUALIFY` filtern

Fensterfunktionen werden ausgeführt, nachdem die Klauseln WHERE und HAVING bereits ausgewertet wurden; es ist daher nicht möglich, diese Klauseln zu verwenden, um die Ergebnisse von Fensterfunktionen zu filtern. Die QUALIFY-Klausel erspart eine Unterabfrage oder WITH-Klausel für diese Filterung.

Box-and-Whisker-Abfragen

Alle Aggregate können als Fensterfunktionen verwendet werden, einschließlich der komplexen statistischen Funktionen. Diese Funktionsimplementierungen wurden für das Fenstern optimiert, und wir können die Fenstersyntax verwenden, um Abfragen zu schreiben, die die Daten für gleitende Box-and-Whisker-Plots erzeugen:

SELECT "Plant", "Date",
    min("MWh") OVER seven AS "MWh 7-day Moving Minimum",
    quantile_cont("MWh", [0.25, 0.5, 0.75]) OVER seven
        AS "MWh 7-day Moving IQR",
    max("MWh") OVER seven AS "MWh 7-day Moving Maximum",
FROM "Generation History"
WINDOW seven AS (
    PARTITION BY "Plant"
    ORDER BY "Date" ASC
    RANGE BETWEEN INTERVAL 3 DAYS PRECEDING
              AND INTERVAL 3 DAYS FOLLOWING)
ORDER BY 1, 2;

Fensterfunktionen

Beispiele

Syntax

Allgemeine Fensterfunktionen

cume_dist([ORDER BY ordering])

dense_rank()

fill(expr[ ORDER BY ordering])

first_value(expr[ ORDER BY ordering][ IGNORE NULLS])

lag(expr[, offset[, default]][ ORDER BY ordering][ IGNORE NULLS])

last_value(expr[ ORDER BY ordering][ IGNORE NULLS])

lead(expr[, offset[, default]][ ORDER BY ordering][ IGNORE NULLS])

nth_value(expr, nth[ ORDER BY ordering][ IGNORE NULLS])

ntile(num_buckets[ ORDER BY ordering])

percent_rank([ORDER BY ordering])

rank([ORDER BY ordering])

row_number([ORDER BY ordering])