Fensterfunktionen - AWS Clean Rooms
Übersicht über die Syntax von FensterfunktionenSpezifisches Anordnen von Daten für FensterfunktionenUnterstützte FunktionenBeispieltabelle mit Beispielen von Fensterfunktionen

Die vorliegende Übersetzung wurde maschinell erstellt. Im Falle eines Konflikts oder eines Widerspruchs zwischen dieser übersetzten Fassung und der englischen Fassung (einschließlich infolge von Verzögerungen bei der Übersetzung) ist die englische Fassung maßgeblich.

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, die Gruppen von Zeilen bildet (PARTITION-Klausel)

  • Fensteranordnung, die eine Reihenfolge oder Sequenz von Zeilen innerhalb der einzelnen Partitionen definiert (ORDER BY-Klausel)

  • Fensterrahmen, die in Bezug auf die einzelnen Zeilen definiert werden, um den Satz von Zeilen weiter einzuschränken (ROWS-Spezifikation)

Fensterfunktionen sind der letzte Satz von Operationen, die in einer Abfrage ausgeführt werden, abgesehen von der abschließenden ORDER BY-Klausel. Alle Joins und alle -, - und -Klauseln werden abgeschlossen, bevor die Fensterfunktionen verarbeitet werden. Daher können Fensterfunktionen nur in der Auswahlliste oder in der ORDER BY-Klausen enthalten sein. Innerhalb einer einzelnen Abfrage können mehrere Fensterfunktionen mit unterschiedlichen Rahmenklauseln verwendet werden. Außerdem können Sie Fensterfunktionen in anderen skalaren Ausdrücken verwenden, beispielsweise CASE.

Ü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 differenziert Fensterfunktionen von anderen SQL-Funktionen.

PARTITION BY expr_list

(Optional) Die PARTITION-BY-Klausel unterteilt den Ergebnissatz in Partitionen, ähnlich wie die GROUP-BY-Klausel. 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 Rangfestlegungsfunktionen DENSE_RANK, NTILE, RANK und ROW_NUMBER erfordern einen globalen Vergleich aller Zeilen im Ergebnissatz. Wenn eine PARTITION BY-Klausel verwendet wird, kann die Abfrageoptimierung die einzelnen Aggregationen parallel ausführen, indem der Workload entsprechend den Partitionen über mehrere Slices verteilt wird. Wenn die PARTITION BY-Klausel nicht vorhanden ist, muss der Aggregationsschritt seriell für einen einzelnen Slice ausgeführt werden. Dies kann erhebliche negative Auswirkungen auf die Leistung haben, besonders für größere Cluster.

AWS Clean Rooms unterstützt keine Zeichenkettenliterale in PARTITION BY-Klauseln.

ORDER BY order_list

(Optional) Die Fensterfunktion wird auf die Zeilen innerhalb der einzelnen Partitionen angewendet, sortiert entsprechend der Reihenfolgenspezifikation in ORDER BY. Diese ORDER BY-Klausel unterscheidet sich von der ORDER BY-Klausel in der frame_clauseund ist mit dieser in keiner Weise verwandt. Die ORDER BY-Klausel kann ohne die PARTITION BY-Klausel verwendet werden.

Für Rangfestlegungsfunktionen identifiziert die ORDER BY-Klausel die Messwerte für die Rangfestlegungswerte. 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.

NULL-Werte werden als eigene Gruppe behandelt und entsprechend der Option NULLS FIRST oder NULLS LAST sortiert und angeordnet. Standardmäßig werden NULL-Werte in einer ASC-Reihenfolge an letzter Stelle sortiert und aufgeführt und in einer DESC-Reihenfolge an erster Stelle sortiert und aufgeführt.

AWS Clean Rooms unterstützt keine Zeichenkettenliterale in ORDER BY-Klauseln.

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

Anmerkung

In jedem parallel System AWS Clean Rooms, z. B. wenn eine ORDER BY-Klausel keine eindeutige und vollständige Reihenfolge der Daten erzeugt, ist die Reihenfolge der Zeilen nicht deterministisch. Das heißt, wenn der ORDER BY-Ausdruck doppelte Werte erzeugt (eine teilweise Reihenfolge), kann die Reihenfolge der Rückgabe dieser Zeilen von einem Lauf AWS Clean Rooms 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 (beispielsweise niedrig nach hoch für numerische Werte und A bis Z für Zeichenfolgen). Wenn keine Option angegeben wird, werden die Daten standardmäßig in aufsteigender Reihenfolge sortiert.

  • DESC: absteigend (beispielsweise hoch nach niedrig für numerische Werte und Z bis A für Zeichenfolgen).

NULLS FIRST | NULLS LAST

Option, die angibt, ob NULL-Werte an erster Stelle vor Nicht-Null-Werten oder an letzter Stelle nach Nicht-Null-Werten aufgelistet werden sollen. Standardmäßig werden NULL-Werte in einer ASC-Reihenfolge an letzter Stelle sortiert und aufgeführt und in einer DESC-Reihenfolge an erster Stelle sortiert und aufgeführt.

frame_clause

Die Rahmenklausel gibt für Aggregationsfunktionen den Satz von Zeilen im Fenster einer Funktion bei Verwendung von ORDER BY noch genauer an. Sie ermöglicht das Ein- oder Ausschließen von Sätzen von Zeilen innerhalb des geordneten Ergebnisses. Die Rahmenklausel besteht aus dem Schlüsselwort ROWS und verknüpften Spezifikatoren.

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

Wenn keine ORDER-BY-Klausel angegeben ist, ist der implizierte Rahmen unbegrenzt, äquivalent zu 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 }

UNBOUNDED PRECEDING zeigt an, dass das Fenster an der ersten Zeile der Partition beginnt; offset PRECEDING zeigt an, dass das Fenster um eine Zahl von Reihen vor der aktuellen Zeile beginnt, die dem Offset-Wert entspricht. UNBOUNDED PRECEDING ist der Standardwert.

CURRENT ROW zeigt an, dass das Fenster an der aktuellen Zeile beginnt oder endet.

UNBOUNDED FOLLOWING zeigt an, dass das Fenster an der letzten Zeile der Partition endet; offset FOLLOWING zeigt an, dass das Fenster um eine Zahl von Reihen nach der aktuellen Zeile endet, die dem Offset-Wert 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. Beispielsweise wird bei 5 FOLLOWING der Rahmen fünf Zeilen nach der aktuellen Zeile beendet.

Wenn BETWEEN nicht angegeben ist, wird der Rahmen implizit von der aktuellen 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 spezifische und globale Anordnung der Daten generiert, ist die Reihenfolge der Zeilen nicht deterministisch. Wenn der ORDER-BY-Ausdruck duplizierte Werte generiert (eine partielle Anordnung), kann sich die Rückgabereihenfolge dieser Zeilen zwischen verschiedenen Ausführungen unterscheiden. 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, da order by dateid keine spezifische Reihenfolge der Daten für die SUM-Fensterfunktion erzeugt. 

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 Hinzufügen einer zweiten ORDER-BY-Spalte zur Fensterfunktion das Problem lösen. 

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

AWS Clean Rooms unterstützt zwei Arten von Fensterfunktionen: Aggregat- und Rangfunktionen.

Die folgenden Aggregationsfunktionen werden unterstützt:

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 WINSALES verwendet, die 11 Zeilen enthält, wie in der folgenden Tabelle 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