|
A window definition can be placed in the OVER clause of a window function. This is referred to as defining the window using inline syntax. You can also define the window separately in a WINDOW clause, and then refer to it from the function. The benefit of using the WINDOW clause is that it allows multiple functions to reference the window, and other computations to be performed on the window, since it is defined independently.
For example, the following SELECT statement shows three different functions referencing the same named window (Qty):
SELECT Products.ID, Description, Quantity, RANK() OVER Qty AS Rank_quantity, CUME_DIST() OVER Qty AS Dist_Cume, ROW_NUMBER() OVER Qty AS Qty_order FROM Products WINDOW Qty AS ( ORDER BY Quantity ASC ) ORDER BY Qty_order;
When using the WINDOW clause syntax, the following restrictions apply:
If a PARTITION BY clause is specified, it must be placed within the WINDOW clause.
If a ROWS or RANGE clause is specified, it must be placed in the OVER clause of the referencing function.
If an ORDER BY clause is specified for the window, it can be placed in either the WINDOW clause or the referencing function's OVER clause, but not both.
The WINDOW clause must precede the SELECT statement's ORDER BY clause.
The following statement queries the SQL Anywhere sample database for all products shipped in July and August 2001, and the cumulative shipped quantity by shipping date. The window is defined inline.
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;
This query returns the following results:
| 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 this example, the computation of the SUM window function occurs after the join of the two tables and the application of the query's WHERE clause. The query is processed as follows:
Partition (group) the input rows based on the value ProductID.
Within each partition, sort the rows based on the value of ShipDate.
For each row in the partition, evaluate the SUM function over the values in Quantity, using a sliding window consisting of the first (sorted) row of each partition, up to and including the current row.
An alternative construction for the above query is to use a WINDOW clause to specify the window separately from the functions that use it, and then reference the window from within the OVER clause of each function.
In this example, the WINDOW clause creates a window called Cumulative, partitioning data by ProductID, and ordering it by ShipDate. The SUM function references the window in its OVER clause, and defines its size using a ROWS clause.
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;