Windowing

A major feature of the ANSI SQL extensions for OLAP is a construct called a window. This windowing extension lets users divide result sets of a query (or a logical partition of a query) into groups of rows called partitions and determine subsets of rows to aggregate with respect to the current row.

You can use three classes of window functions with a window: ranking functions, the row numbering function, and window aggregate functions.

<WINDOWED TABLE FUNCTION TYPE> ::=
  <RANK FUNCTION TYPE> <LEFT PAREN> <RIGHT PAREN>
  | ROW_NUMBER <LEFT PAREN> <RIGHT PAREN>
  | <WINDOW AGGREGATE FUNCTION>

Windowing extensions specify a window function type over a window name or specification and are applied to partitioned result sets within the scope of a single query expression. A window partition is a subset of rows returned by a query, as defined by one or more columns in a special OVER clause:

olap_function() OVER (PARTITION BY col1, col2...)

Windowing operations let you establish information such as the ranking of each row within its partition, the distribution of values in rows within a partition, and similar operations. Windowing also lets you compute moving averages and sums on your data, enhancing the ability to evaluate your data and its impact on your operations.

An OLAP window’s three essential parts

The OLAP windows comprise three essential aspects: window partitioning, window ordering, and window framing. Each has a significant impact on the specific rows of data visible in a window at any point in time. Meanwhile, the OLAP OVER clause differentiates OLAP functions from other analytic or reporting functions with three distinct capabilities:

Defining window partitions (PARTITION BY clause).
Ordering rows within partitions (ORDER BY clause).
Defining window frames (ROWS/RANGE specification).

To specify multiple windows functions, and to avoid redundant window definitions, you can specify a name for an OLAP window specifications. In this usage, the keyword, WINDOW, is followed by at least one window definition, separated by commas. A window definition includes the name by which the window is known in the query and the details from the windows specification, which lets you to define window partitioning, ordering, and framing:

<WINDOW CLAUSE> ::= <WINDOW DEFINITION LIST>

<WINDOW DEFINITION LIST> ::=
  <WINDOW DEFINITION> [ { <COMMA> <WINDOW DEFINITION>
  } . . . ]

<WINDOW DEFINITION> ::=
  <NEW WINDOW NAME> AS <WINDOW SPECIFICATION>

<WINDOW SPECIFICATION DETAILS> ::=
  [ <EXISTING WINDOW NAME> ]
  [ <WINDOW PARTITION CLAUSE> ]
  [ <WINDOW ORDER CLAUSE> ]
  [ <WINDOW FRAME CLAUSE> ]

For each row in a window partition, users can define a window frame, which may vary the specific range of rows used to perform any computation on the current row of the partition. The current row provides the reference point for determining the start and end points of the window frame.

Window specifications can be based on either a physical number of rows using a window specification that defines a window frame unit of ROWS or a logical interval of a numeric value, using a window specification that defines a window frame unit of RANGE.

Within OLAP windowing operations, you can use the following functional categories:

Ranking functions
Windowing aggregate functions
Statistical aggregate functions
Distribution functions

Window Partitioning
Window partitioning is the division of user-specified result sets (input rows) using a PARTITION BY clause.
Window Ordering
Window ordering is the arrangement of results (rows) within each window partition using a window order clause, which contains one or more value expressions separated by commas.
Window Framing
For nonranking aggregate OLAP functions, you can define a window frame with a window frame clause, which specifies the beginning and end of the window relative to the current row.
ROWS
The window frame unit ROWS defines a window in the specified number of rows before or after the current row, which serves as the reference point that determines the start and end of a window.
RANGE
Range-based window frames—The SQL/OLAP syntax supports another kind of window frame whose limits are defined in terms of a value-based—or range-based—set of rows, rather than a specific sequence of rows.
Explicit and Inline Window Clauses
SQL OLAP provides two ways of specifying a window in a query:
Ranking Functions
Ranking functions let you compile a list of values from the data set in ranked order, as well as compose single-statement SQL queries that fulfil requests such as, “Name the top 10 products shipped this year by total sales,” or “Give the top 5% of salespersons who sold orders to at least 15 different companies.”
Ranking Examples
These are some of the ranking functions examples:
Windowing Aggregate Functions
Windowing aggregate functions let you manipulate multiple levels of aggregation in the same query.
Statistical Aggregate Functions
The ANSI SQL/OLAP extensions provide a number of additional aggregate functions that permit statistical analysis of numeric data. This support includes functions to compute variance, standard deviation, correlation, and linear regression.
Inter-Row Functions
The inter-row functions, LAG and LEAD, provide access to previous or subsequent values in a data series, or to multiple rows in a table.
Distribution Functions
SQL/OLAP defines several functions that deal with ordered sets.

Parent topic: Analytical Functions