There are three ways to define a window:
inline (within the OVER clause of a window function)
in a WINDOW clause
partially inline and partially in a WINDOW clause
However, some approaches have restrictions, as noted in the following sections.
A window definition can be placed in the OVER clause of a window function. This is referred to as defining the window inline.
For example, the following statement queries the 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:
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;
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.
You can inline part of a window definition and then define the rest in the WINDOW clause. For example:
AVG() OVER ( windowA ORDER BY expression )... ... WINDOW windowA AS ( PARTITION BY expression )
When splitting the window definition in this manner, the following restrictions apply:
You cannot use a PARTITION BY clause in the window function syntax.
You can use an ORDER BY clause in either the window function syntax or in the WINDOW clause, but not in both.
You cannot include a RANGE or ROWS clause in the WINDOW clause.
Discuss this page in DocCommentXchange.
|Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1|