Defining a window

SQL windowing extensions allow you to configure the bounds of a window, as well as the partitioning and ordering of the input rows. Logically, as part of the semantics of computing the result of a query specification, partitions are created after the groups defined by the GROUP BY clause are created, but before the evaluation of the final SELECT list and the query's ORDER BY clause. Consequently, the order of evaluation of the clauses within a SQL statement is:

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. WINDOW
  6. DISTINCT
  7. ORDER BY

One implication of the evaluation ordering is that predicates on window functions would require the window function to appear in a derived table.

Because window partitioning follows a GROUP BY operator, the result of any aggregate function, such as SUM, AVG, or VARIANCE, is available to the computation done for a partition. Hence, windows provide another opportunity to perform grouping and ordering operations in addition to a query's GROUP BY and ORDER BY clauses.

When you define the window over which a window function operates, you specify one or more of the following:

  • Partitioning   Defines how the input rows are partitioned (or grouped) using the PARTITION BY clause. If omitted, the entire input is treated as a single partition. A partition can be one, several, or all input rows, depending on what you specify. Data from two partitions is never mixed. That is, when a window reaches the boundary between two partitions, it completes processing the data in one partition, before beginning on the data in the next partition. This means that the window size may vary at the beginning and end of a partition, depending on how the bounds are defined for the window.

  • Ordering   Defines how the input rows are ordered, prior to being processed by the window function. The ORDER BY clause is required only if you are specifying the bounds using a RANGE clause, or if a ranking function references the window. Otherwise, the ORDER BY clause is optional. If omitted, the input rows are processed in whatever manner the database server considers most efficient.

  • Bounds   Defines the bounds of the window either in terms of a range of data values offset from the value in the current row (RANGE clause), or in terms of the number of rows offset from the current row (ROWS clause). The window size can be one, many, or all rows of a partition.

    Within the ROWS and RANGE clauses, you specify the start and end rows of the window, relative to the current row, using a combination of optional PRECEDING, BETWEEN, and FOLLOWING clauses. These clauses take expressions, as well as the keywords UNBOUNDED and CURRENT ROW.

    If no bounds are defined for a window, the size of the window defaults as follows:

    • If the window specification contains an ORDER BY clause, the window's start point is UNBOUNDED PRECEDING, and its end point is CURRENT ROW.
    • If the window specification does not contain an ORDER BY clause, the window's start point is UNBOUNDED PRECEDING, and the end point is UNBOUNDED FOLLOWING.

    As a best practice measure, always define a window's bounds to make the window specification explicit.

    Do not specify window bounds when using a ranking or a row-numbering function.

Sizing the window
Defining a window inline, or using a WINDOW clause

Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0