Fensterdefinition: Inlining unter Verwendung der Klauseln OVER und WINDOW

Es gibt drei Methoden, ein Fenster zu definieren:

Inline (innerhalb der OVER-Klausel einer Fensterfunktion)
In einer WINDOW-Klausel
Teilweise inline und teilweise in einer WINDOW-Klausel

Einige Methoden unterliegen allerdings Beschränkungen, wie in den folgenden Abschnitten beschrieben.

Inline-Definition (innerhalb der OVER-Klausel einer Fensterfunktion)

Eine Fensterdefinition kann in die OVER-Klausel einer Fensterfunktion eingefügt werden. Dies wird als Inline-Definieren des Fensters bezeichnet.

Beispiel: Die folgende Anweisung fragt aus der SQL Anywhere-Beispieldatenbank alle Produkte ab, die im Juli und August 2001 ausgeliefert wurden, und die kumulativen Auslieferungsmengen nach Lieferdatum. Das Fenster wird inline definiert.

SELECT p.ID, p.Description, s.Quantity, s.ShipDate,
   SUM( s.Quantity ) OVER ( PARTITION BY s.ProductID
       ORDER BY s.ShipDate
       ROWS BETWEEN UNBOUNDED PRECEDING
       AND CURRENT ROW ) AS Cumulative_qty
FROM SalesOrderItems s JOIN Products p 
   ON ( s.ProductID = p.ID )
WHERE s.ShipDate BETWEEN '2001-07-01' AND '2001-08-31'
ORDER BY p.ID;

Diese Abfrage liefert folgende Ergebnisse:

	ID	Description	Quantity	ShipDate	Cumulative_qty
1	301	V-neck	24	2001-07-16	24
2	302	Crew Neck	60	2001-07-02	60
3	302	Crew Neck	36	2001-07-13	96
4	400	Cotton Cap	48	2001-07-05	48
5	400	Cotton Cap	24	2001-07-19	72
6	401	Wool Cap	48	2001-07-09	48
7	500	Cloth Visor	12	2001-07-22	12
8	501	Plastic Visor	60	2001-07-07	60
9	501	Plastic Visor	12	2001-07-12	72
10	501	Plastic Visor	12	2001-07-22	84
11	601	Zipped Sweatshirt	60	2001-07-19	60
12	700	Cotton Shorts	24	2001-07-26	24

In diesem Beispiel erfolgt die Berechnung der Fensterfunktion SUM nach dem Join der beiden Tabellen und der Anwendung der WHERE-Klausel der Abfrage. Die Abfrage wird folgendermaßen verarbeitet:

Die Eingabezeilen werden aufgrund des Wertes in "ProductID" partitioniert (gruppiert).
Innerhalb jeder Partition werden die Zeilen aufgrund des Wertes in "ShipDate" sortiert.
Für jede Zeile in der Partition wird die SUM-Funktion auf die Mengenwerte angewendet, wobei ein verschiebbares Fenster benutzt wird, das die Zeilen von der ersten (sortierten) Zeile jeder Partition bis zur aktuellen Zeile enthält.

WINDOW-Klausel-Definition

Eine alternative Methode für die obige Abfrage besteht in der Verwendung einer WINDOW-Klausel, um das Fenster getrennt von den Funktionen, die es benutzen, zu definieren, und im Verweisen des Fensters aus der OVER-Klausel jeder Funktion.

In diesem Beispiel erstellt die WINDOW-Klausel ein Fenster namens "Cumulative", partitioniert die Daten nach "ProductID" und sortiert sie nach "ShipDate". Die SUM-Funktion verweist in ihrer OVER-Klausel auf das Fenster und definiert seine Größe mit einer ROWS-Klausel.

SELECT p.ID, p.Description, s.Quantity, s.ShipDate,
    SUM( s.Quantity ) OVER ( Cumulative
    ROWS BETWEEN UNBOUNDED PRECEDING
    AND CURRENT ROW ) AS cumulative_qty
FROM SalesOrderItems s 
JOIN Products p ON ( s.ProductID = p.ID )
WHERE s.ShipDate BETWEEN '2001-07-01' AND '2001-08-31'
WINDOW Cumulative AS ( PARTITION BY s.ProductID ORDER BY s.ShipDate )
ORDER BY p.ID;

Wenn die Syntax der WINDOW-Klausel verwendet wird, gelten folgende Einschränkungen:

Falls eine PARTITION BY-Klausel angegeben wird, muss sie sich innerhalb der WINDOW-Klausel befinden.
Falls eine ROWS- oder RANGE-Klausel angegeben wird, muss sie sich innerhalb der OVER-Klausel der referenzierenden Funktion befinden.
Falls eine ORDER BY-Klausel für das Fenster angegeben wird, kann sie sich entweder in der WINDOW-Klausel oder in der OVER-Klausel der referenzierenden Funktion befinden, nicht jedoch in beiden.
Die WINDOW-Klausel muss sich vor der ORDER BY-Klausel der SELECT-Anweisung befinden.

Kombination aus Inline- und WINDOW-Klausel-Definition

Sie können einen Teil der Fensterdefinition inline setzen und dann den Rest in der WINDOW-Klausel definieren. Zum Beispiel:

AVG() OVER ( windowA
             ORDER BY expression )...
...
WINDOW windowA AS ( PARTITION BY expression )

Wenn Sie die Fensterdefinition auf diese Weise aufteilen, gelten die folgenden Beschränkungen:

Sie können keine PARTITION BY-Klausel in der Fensterfunktionssyntax verwenden.
Sie können eine ORDER BY-Klausel entweder in der Fensterfunktionssyntax oder in der WINDOW-Klausel verwenden, aber nicht in beiden.
Sie können keine RANGE- oder ROWS-Klausel in die WINDOW-Klausel aufnehmen.

Siehe auch

Kommentieren Sie diese Seite in DocCommentXchange.