Example: Window functions in queries

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:

  1. Partition (group) the input rows based on the value of the prod_id attribute.

  2. Within each partition, sort the rows by the ship_date attribute.

  3. 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: