Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » OLAP Support

Detecting placeholder NULLs using the GROUPING function Next Page

Window functions


OLAP functionality includes the concept of a sliding window that moves down through the input rows as they are processed. Additional calculations can be performed on the data in the window as it moves, allowing further analysis in a manner that is more efficient than using semantically equivalent self-join queries, or correlated subqueries.

You configure the bounds of the window based on the information you are trying to extract from the data. A window can be one, many, or all of the rows in the input data, which has been partitioned according to the grouping specifications provided in the window definition. The window moves down through the input data, incorporating the rows needed to perform the requested calculations.

The movement of the window as input rows are processed is shown in the following diagram. The data partitions reflect the grouping of input rows specified in the window definition. If no grouping is specified, all input rows are considered one partition. The length of the window (that is, the number of rows it includes), and the offset of the window compared to the current row, reflect the bounds specified in the window definition.

Diagram of a 3-row window moving over partitioned input rows
Window functions in SQL Anywhere

Functions that allow you to perform analytic operations over a set of input rows are referred to as window functions. For example, all of the ranking functions, and almost all of the aggregate functions, are window functions. You can use them to perform additional analysis on your data. This is achieved by partitioning and sorting the input rows, prior to their being processed, and then processing the rows in a configurable-sized window that moves through the input.

There are three types of window functions: window aggregate functions, window ranking functions, and row numbering functions.


Defining a window
Window aggregate functions
Window ranking functions
Row numbering functions
Mathematical formulas for the aggregate functions