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.ship_date, SUM(s.quantity) OVER (PARTITION BY prod_id ORDER BY s.ship_date rows between unbounded preceding and current row) FROM alt_sales_order_items s JOIN product p on (s.prod_id = p.id) WHERE s.ship_date BETWEEN '2001-05-01' and '2001-08-31' AND s.quantity > 40 ORDER BY p.id;
The following are 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 in-line 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 4-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.ship_date,
SUM(s.quantity) OVER(cumulative
ROWS BETWEEN UNBOUNDED PRECEDING
and CURRENT ROW
) AS cumulative qty
FROM sales_order_items s JOIN product p On (s.prod_id = p.id)
WHERE s.ship_date BETWEEN ‘2005-07-01’ and ‘2005-08-31’
Window cumulative as (PARTITION BY s.prod_id ORDER BY s.ship date)
ORDER BY p.id
Note how the window clause appears before the ORDER BY clause in the query specification. When using a window clause, the following restrictions apply:
The in-line window specification cannot contain a PARTITION BY clause.
The window specified within the window clause cannot contain a window frame clause. For example, from “Grammar rule 32”:
<WINDOW FRAME CLAUSE> ::= <WINDOW FRAME UNIT> <WINDOW FRAME EXTENT>
Either the in-line window specification, or the window specification specified in the window clause, can contain a window order clause, but not both. For example, from “Grammar rule 31”:
<WINDOW ORDER CLAUSE> ::= <ORDER SPECIFICATION>
