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.
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.
Window aggregate functions Window aggregate functions return a value for a specified set of rows in the input. The supported window aggregate functions are listed below:
For more information about window aggregate functions, see Window aggregate functions.
Window ranking functions Window ranking functions return the rank of a row relative to the other rows in a partition. The supported window ranking functions are listed below:
For more information about window ranking functions, see Window ranking functions.
Row numbering functions Row numbering functions uniquely number the rows in a partition. In SQL Anywhere, the ROW_NUMBER function is an ANSI standard-compliant function that permits much of the same functionality as the SQL Anywhere NUMBER(*) function.
For more information about using this function in the window context, see ROW_NUMBER function.
Defining a window
Window aggregate functions
Window ranking functions
Row numbering functions
Mathematical formulas for the aggregate functions
| Send feedback about this page via email or DocCommentXchange | Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0 |