Übersicht über die Syntax von Fensterfunktionen Spezifisches Anordnen von Daten für Fensterfunktionen Unterstützte Funktionen Beispieltabelle mit Beispielen von Fensterfunktionen

Fensterfunktionen

Mit Fensterfunktionen können Sie analytische geschäftliche Abfragen effizienter erstellen. Fensterfunktionen werden für eine Partition bzw. ein „Fenster“ eines Ergebnissatzes ausgeführt und geben für jede Zeile in diesem Fenster einen Wert zurück. Funktionen ohne Fenster führen ihre Berechnungen dagegen für alle Zeilen des Ergebnissatzes aus. Im Gegensatz zu Gruppenfunktionen, die die Ergebniszeilen aggregieren, behalten Fensterfunktionen alle Zeilen im Tabellenausdruck bei.

Die zurückgegebenen Werte werden mithilfe von Werten aus den Sätzen von Zeilen in diesem Fenster berechnet. Das Fenster definiert für jede Zeile in der Tabelle einen Satz von Zeilen, der für die Verarbeitung zusätzlicher Attribute verwendet wird. Ein Fenster wird mithilfe einer Fensterspezifikation (der OVER Klausel) definiert und basiert auf drei Hauptkonzepten:

Fensterpartitionierung, bei der Gruppen von Zeilen gebildet werden (PARTITIONKlausel)
Fensterreihenfolge, die eine Reihenfolge oder Reihenfolge von Zeilen innerhalb jeder Partition definiert (ORDERBY-Klausel)
Fensterrahmen, die relativ zu jeder Zeile definiert werden, um die Anzahl der Zeilen weiter einzuschränken (ROWSSpezifikation)

Fensterfunktionen sind die letzten Operationen, die in einer Abfrage ausgeführt werden, mit Ausnahme der ORDER abschließenden BY-Klausel. Alle Verknüpfungen und alleWHERE, GROUP BY- und HAVING Klauseln werden abgeschlossen, bevor die Fensterfunktionen verarbeitet werden. Daher können Fensterfunktionen nur in der Auswahlliste oder ORDER in der BY-Klausel vorkommen. Innerhalb einer einzelnen Abfrage können mehrere Fensterfunktionen mit unterschiedlichen Rahmenklauseln verwendet werden. Sie können Fensterfunktionen auch in anderen skalaren Ausdrücken verwenden, z. CASE

Fensterfunktionen können nicht verschachtelt werden. Beispielsweise SUM kann eine Aggregatfunktion in einer Fensterfunktion vorkommenSUM, eine Fensterfunktion SUM jedoch nicht in einer anderen FensterfunktionSUM. Folgendes wird nicht unterstützt, da eine Fensterfunktion in einer anderen Fensterfunktion verschachtelt ist.


SELECT SUM(SUM(selectcol) OVER (PARTITION BY ordercol)) OVER (Partition by ordercol) FROM t;

Übersicht über die Syntax von Fensterfunktionen

Fensterfunktionen folgen einer Standardsyntax, die wie folgt lautet.


function (expression) OVER (
[ PARTITION BY expr_list ]
[ ORDER BY order_list [ frame_clause ] ] )

Hier ist function eine der in diesem Abschnitt beschriebenen Funktionen.

Die expr_list lautet wie folgt.


expression | column_name [, expr_list ]

Die order_list lautet wie folgt.


expression | column_name [ ASC | DESC ] 
[ NULLS FIRST | NULLS LAST ]
[, order_list ]

Die frame_clause lautet wie folgt.


ROWS
{ UNBOUNDED PRECEDING | unsigned_value PRECEDING | CURRENT ROW } |

{ BETWEEN
{ UNBOUNDED PRECEDING | unsigned_value { PRECEDING | FOLLOWING } | CURRENT ROW}
AND
{ UNBOUNDED FOLLOWING | unsigned_value { PRECEDING | FOLLOWING } | CURRENT ROW }}

Argumente

Funktion

Die Fensterfunktion. Details finden Sie in den Beschreibungen der einzelnen Funktionen.

OVER

Die Klausel, die die Fensterspezifikation definiert. Die OVER Klausel ist für Fensterfunktionen obligatorisch und unterscheidet Fensterfunktionen von anderen SQL Funktionen.

PARTITIONVON expr_list

(Optional) Die PARTITION BY-Klausel unterteilt die Ergebnismenge ähnlich wie die BY-Klausel in Partitionen. GROUP Wenn eine Partitionsklausel vorhanden ist, wird die Funktion für die Zeilen in den einzelnen Partitionen berechnet. Wenn keine Partitionsklausel angegeben ist, enthält eine einzige Partition die gesamte Tabelle und die Funktion wird für die gesamte Tabelle berechnet.

Die Rankingfunktionen DENSE _ RANKNTILE,RANK, und ROW _ NUMBER erfordern einen globalen Vergleich aller Zeilen in der Ergebnismenge. Wenn eine PARTITION BY-Klausel verwendet wird, kann der Abfrageoptimierer jede Aggregation parallel ausführen, indem er die Arbeitslast entsprechend den Partitionen auf mehrere Bereiche verteilt. Wenn die PARTITION BY-Klausel nicht vorhanden ist, muss der Aggregationsschritt seriell auf einem einzelnen Slice ausgeführt werden, was sich insbesondere bei großen Clustern erheblich negativ auf die Leistung auswirken kann.

Amazon Redshift unterstützt keine Zeichenkettenliterale in PARTITION BY-Klauseln.

ORDERVON order_list

(Optional) Die Fensterfunktion wird auf die Zeilen innerhalb jeder Partition angewendet, sortiert nach der Reihenfolgenspezifikation in ORDER BY. Diese ORDER BY-Klausel unterscheidet sich von den BY-Klauseln in der frame_clause und hat keinerlei Bezug zu ORDER diesen. Die ORDER BY-Klausel kann ohne die BY-Klausel verwendet werden. PARTITION

Bei Rangfunktionen identifiziert die ORDER BY-Klausel die Kennzahlen für die Rangfolge der Werte. Für Aggregationsfunktionen müssen die partitionierten Zeilen angeordnet werden, bevor die jeweilige Aggregationsfunktion für die einzelnen Rahmen berechnet wird. Weitere Informationen zu den Arten von Windowsfunktionen finden Sie unter Fensterfunktionen.

In der Reihenfolgenliste werden Spaltenbezeichner oder Ausdrücke, die zu Spaltenbezeichnern ausgewertet werden, benötigt. Konstanten oder Konstantenausdrücke können nicht als Ersatz für Spaltennamen verwendet werden.

NULLSWerte werden als eigene Gruppe behandelt und entsprechend der NULLS LAST Option NULLS FIRST oder sortiert und geordnet. Standardmäßig werden NULL Werte sortiert und in der Reihenfolge an letzter Stelle sowie sortiert und in der ASC Reihenfolge an erster Stelle gereiht. DESC

Amazon Redshift unterstützt keine Zeichenkettenliterale in ORDER BY-Klauseln.

Wenn die ORDER BY-Klausel weggelassen wird, ist die Reihenfolge der Zeilen nicht deterministisch.

Anmerkung

In jedem parallel System wie Amazon Redshift ist die Reihenfolge der Zeilen nicht deterministisch, wenn eine ORDER BY-Klausel keine eindeutige und vollständige Reihenfolge der Daten erzeugt. Das heißt, wenn der ORDER BY-Ausdruck doppelte Werte erzeugt (eine teilweise Reihenfolge), kann die Rückgabereihenfolge dieser Zeilen von einem Lauf von Amazon Redshift zum nächsten variieren. In diesem Fall können Fensterfunktionen unerwartete oder inkonsistente Ergebnisse zurückgeben. Weitere Informationen finden Sie unter Spezifisches Anordnen von Daten für Fensterfunktionen.

column_name

Der Name einer Spalte, nach der die Partitionierung oder Anordnung erfolgen soll.

ASC | DESC

Eine Option, die die Sortierreihenfolge für den Ausdruck wie folgt definiert:

ASC: aufsteigend (z. B. niedrig bis hoch für numerische Werte und „A“ bis „Z“ für Zeichenketten). Wenn keine Option angegeben wird, werden die Daten standardmäßig in aufsteigender Reihenfolge sortiert.
DESC: absteigend (hoch bis niedrig für numerische Werte; 'Z' bis 'A' für Zeichenketten).

NULLS FIRST | NULLS LAST

Option, die angibt, ob zuerst, vor Werten, die nicht Null sind, oder zuletzt, nach Werten, die nicht Null sind, sortiert werden NULLS soll. Standardmäßig NULLS werden sie sortiert und in der Reihenfolge an letzter Stelle sortiert und in der ASC Reihenfolge an erster Stelle sortiert. DESC

frame_clause

Bei Aggregatfunktionen verfeinert die Frame-Klausel die Anzahl der Zeilen im Fenster einer Funktion weiter, wenn ORDER BY verwendet wird. Sie ermöglicht das Ein- oder Ausschließen von Sätzen von Zeilen innerhalb des geordneten Ergebnisses. Die Frame-Klausel besteht aus dem ROWS Schlüsselwort und den zugehörigen Spezifizierern.

Die Rahmenklausel kann nicht auf Rangfestlegungsfunktionen angewendet werden. Außerdem ist die Frame-Klausel nicht erforderlich, wenn in der Klausel für eine Aggregatfunktion keine ORDER OVER BY-Klausel verwendet wird. Wenn eine ORDER BY-Klausel für eine Aggregatfunktion verwendet wird, ist eine explizite Rahmenklausel erforderlich.

Wenn keine ORDER BY-Klausel angegeben ist, ist der implizite Frame unbegrenzt, was entspricht. ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

ROWS

Diese Klausel definiert den Fensterrahmen durch Angabe eines physischen Offsets von der aktuellen Zeile.

Diese Klausel gibt die Zeilen im aktuellen Fenster oder in der aktuellen Partition an, mit denen der Wert in der aktuellen Zeile kombiniert werden soll. Sie verwendet Argumente, die die Zeilenposition angeben. Diese kann sich vor oder nach der aktuellen Zeile befinden. Der Referenzpunkt für alle Fensterrahmen ist die aktuelle Zeile. Alle Zeilen werden nacheinander zur aktuellen Zeile, während der Fensterrahmen in der Partition vorwärts gleitet.

Beim Rahmen kann es sich um einen einfachen Satz von Zeilen bis zur und einschließlich der aktuellen Zeile handeln.


{UNBOUNDED PRECEDING | offset PRECEDING | CURRENT ROW}

Es kann sich auch um einen Satz von Zeilen zwischen zwei Grenzen handeln.


BETWEEN
{ UNBOUNDED PRECEDING | offset { PRECEDING | FOLLOWING } | CURRENT ROW }
AND
{ UNBOUNDED FOLLOWING | offset { PRECEDING | FOLLOWING } | CURRENT ROW }

UNBOUNDEDPRECEDINGgibt an, dass das Fenster in der ersten Zeile der Partition beginnt; Offset PRECEDING gibt an, dass das Fenster mit einer Anzahl von Zeilen beginnt, die dem Wert des Offsets vor der aktuellen Zeile entspricht. UNBOUNDEDPRECEDINGist die Standardeinstellung.

CURRENTROWgibt an, dass das Fenster in der aktuellen Zeile beginnt oder endet.

UNBOUNDEDFOLLOWINGgibt an, dass das Fenster in der letzten Zeile der Partition endet; Offset FOLLOWING gibt an, dass das Fenster mit einer Anzahl von Zeilen endet, die dem Wert von Offset nach der aktuellen Zeile entspricht.

offset bezeichnet eine physische Anzahl von Zeilen vor oder nach der aktuellen Zeile. In diesem Fall muss offset eine Konstante sein, der zu einem positiven numerischen Wert ausgewertet wird. Zum Beispiel FOLLOWING beendet 5 den Frame fünf Zeilen nach der aktuellen Zeile.

Wo nicht angegeben, BETWEEN wird der Frame implizit durch die aktuelle Zeile begrenzt. Beispielsweise ist ROWS 5 PRECEDING gleich ROWS BETWEEN 5 PRECEDING AND CURRENT ROW. Ebenso ist ROWS UNBOUNDED FOLLOWING gleich ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.

Anmerkung

Sie können keinen Rahmen angeben, in dem die Startgrenze größer als die Endgrenze ist. Sie können beispielsweise keinen der folgenden Rahmen angeben.


between 5 following and 5 preceding
between current row and 2 preceding
between 3 following and current row

Spezifisches Anordnen von Daten für Fensterfunktionen

Wenn eine ORDER BY-Klausel für eine Fensterfunktion keine eindeutige und vollständige Reihenfolge der Daten erzeugt, ist die Reihenfolge der Zeilen nicht deterministisch. Wenn der ORDER BY-Ausdruck doppelte Werte erzeugt (eine teilweise Reihenfolge), kann die Reihenfolge, in der diese Zeilen zurückgegeben werden, bei mehreren Durchläufen variieren. In diesem Fall geben Fensterfunktionen möglicherweise unerwartete oder inkonsistente Ergebnisse zurück.

Beispielsweise gibt die folgende Abfrage in verschiedenen Ausführen unterschiedliche Ergebnisse zurück. Diese unterschiedlichen Ergebnisse treten auf, weil dadurch order by dateid keine eindeutige Reihenfolge der Daten für die SUM Fensterfunktion erreicht wird.


select dateid, pricepaid,
sum(pricepaid) over(order by dateid rows unbounded preceding) as sumpaid
from sales
group by dateid, pricepaid;

dateid | pricepaid |   sumpaid
--------+-----------+-------------
1827 |   1730.00 |     1730.00
1827 |    708.00 |     2438.00
1827 |    234.00 |     2672.00
...

select dateid, pricepaid,
sum(pricepaid) over(order by dateid rows unbounded preceding) as sumpaid
from sales
group by dateid, pricepaid;

dateid | pricepaid |   sumpaid
--------+-----------+-------------
1827 |    234.00 |      234.00
1827 |    472.00 |      706.00
1827 |    347.00 |     1053.00
...

In diesem Fall kann das Problem ORDER durch Hinzufügen einer zweiten BY-Spalte zur Fensterfunktion gelöst werden.


select dateid, pricepaid,
sum(pricepaid) over(order by dateid, pricepaid rows unbounded preceding) as sumpaid
from sales
group by dateid, pricepaid;

dateid | pricepaid | sumpaid
--------+-----------+---------
1827 |    234.00 |  234.00
1827 |    337.00 |  571.00
1827 |    347.00 |  918.00
...

Unterstützte Funktionen

Amazon Redshift unterstützt zwei Arten von Fensterfunktionen: Aggregation und Rangfestlegung.

Die folgenden Aggregationsfunktionen werden unterstützt:

AVGFenster-Funktion
COUNTFensterfunktion
CUMEDIST_-Fenster-Funktion
DENSE_ RANK Fenster-Funktion
FIRSTVALUE_-Fenster-Funktion
LAGFensterfunktion
LASTFunktion VALUE „_ Fenster“
LEADFensterfunktion
LISTAGGFensterfunktion
MAXFenster-Funktion
MEDIANFensterfunktion
MINFensterfunktion
NTH_ VALUE Fenster-Funktion
PERCENTILE_ CONT Fenster-Funktion
PERCENTILEDISC_-Fenster-Funktion
RATIOFensterfunktion _TO_ REPORT
STDDEVFensterfunktionen _ SAMP und STDDEV _ POP(STDDEV_ SAMP und STDDEV sind Synonyme)
SUMFensterfunktion
VARSAMPPOPFensterfunktionen VAR _ und _(VAR_ SAMP und VARIANCE sind Synonyme)

Die folgenden Rangfestlegungsfunktionen werden unterstützt:

Beispieltabelle mit Beispielen von Fensterfunktionen

Zu jeder Funktionsbeschreibung gehören spezifische Fensterfunktionsbeispiele. In einigen Beispielen wird eine Tabelle mit dem Namen verwendetWINSALES, die 11 Zeilen enthält, wie im Folgenden dargestellt.

SALESID	DATEID	SELLERID	BUYERID	QTY	QTY_SHIPPED
30001	8/2/2003	3	B	10	10
10001	12/24/2003	1	C	10	10
10005	12/24/2003	1	A	30
40001	1/9/2004	4	A	40
10006	1/18/2004	1	C	10
20001	2/12/2004	2	B	20	20
40005	2/12/2004	4	A	10	10
20002	2/16/2004	2	C	20	20
30003	4/18/2004	3	B	15
30004	4/18/2004	3	B	20
30007	9/7/2004	3	C	30

Das folgende Skript erstellt und füllt die WINSALES Beispieltabelle.


CREATE TABLE winsales(
  salesid int,
  dateid date,
  sellerid int,
  buyerid char(10),
  qty int,
  qty_shipped int);

INSERT INTO winsales VALUES
  (30001, '8/2/2003', 3, 'b', 10, 10),
  (10001, '12/24/2003', 1, 'c', 10, 10),
  (10005, '12/24/2003', 1, 'a', 30, null),	
  (40001, '1/9/2004', 4, 'a', 40, null),	
  (10006, '1/18/2004', 1, 'c', 10, null),	
  (20001, '2/12/2004', 2, 'b', 20, 20),
  (40005, '2/12/2004', 4, 'a', 10, 10),
  (20002, '2/16/2004', 2, 'c', 20, 20),
  (30003, '4/18/2004', 3, 'b', 15, null),
  (30004, '4/18/2004', 3, 'b', 20, null),	
  (30007, '9/7/2004', 3, 'c', 30, null);

Warnung JavaScript ist in Ihrem Browser nicht verfügbar oder deaktiviert.

Zur Nutzung der AWS-Dokumentation muss JavaScript aktiviert sein. Weitere Informationen finden auf den Hilfe-Seiten Ihres Browsers.

Dokumentkonventionen

TO_VARBYTE

AVG