Consider the following query, which lists all products shipped in July and August 2005 and the cumulative shipped quantity by shipping date:
SELECT p.id, p.description, s.quantity, s.shipdate,
SUM(s.quantity) OVER (PARTITION BY productid ORDER BYs.shipdate rows between unbounded preceding andcurrent row)FROM SalesOrderItems s JOIN Products p on(s.ProductID =p.id) WHERE s.ShipDate BETWEEN '2001-05-01' and'2001-08-31' AND s.quantity > 40ORDER BY p.id;
The results from the above query:
ID description quantity ship_date sum quantity --- ----------- -------- --------- ------------ 302 Crew Neck 60 2001-07-02 60 400 Cotton Cap 60 2001-05-26 60 400 Cotton Cap 48 2001-07-05 108 401 Wool cap 48 2001-06-02 48 401 Wool cap 60 2001-06-30 108 401 Wool cap 48 2001-07-09 156 500 Cloth Visor 48 2001-06-21 48 501 Plastic Visor 60 2001-05-03 60 501 Plastic Visor 48 2001-05-18 108 501 Plastic Visor 48 2001-05-25 156 501 Plastic Visor 60 2001-07-07 216 601 Zipped Sweatshirt 60 2001-07-19 60 700 Cotton Shorts 72 2001-05-18 72 700 Cotton Shorts 48 2001-05-31 120
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 uses an inline window specification that specifies that the input rows from the join is processed as follows:
Partition (group) the input rows based on the value of the prod_id attribute.
Within each partition, sort the rows by the ship_date attribute.
For each row in the partition, evaluate the SUM() function over the quantity attribute, using a sliding window consisting of the first (sorted) row of each partition, up to and including the current row. See Figure 2-3.
An alternative construction for the query is to specify the window separate from the functions that use it. This is useful when more than one window function is specified that are based on the same window. In the case of the query using window functions, a construction that uses the window clause (declaring a window identified by cumulative) is as follows:
SELECT p.id, p.description, s.quantity, s.shipdate, SUM(s.quantity) OVER(cumulative ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW ) cumulative 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;
The window clause appears before the ORDER BY clause in the query specification. When using a window clause, the following restrictions apply:
The inline window specification cannot contain a PARTITION BY clause.
The window specified within the window clause cannot contain a window frame clause. From “Grammar rule 32”:
<WINDOW FRAME CLAUSE> ::= <WINDOW FRAME UNIT> <WINDOW FRAME EXTENT>
Either the inline window specification, or the window specification specified in the window clause, can contain a window order clause, but not both. From “Grammar rule 31”:
<WINDOW ORDER CLAUSE> ::= <ORDER SPECIFICATION>
