Defining a window inline, or using a WINDOW clause

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.
Inline window definition example

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:

  1. Partition (group) the input rows based on the value ProductID.
  2. Within each partition, sort the rows based on the value of ShipDate.
  3. 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.
WINDOW clause window definition example

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;
Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0