Analytic Functions

Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic_clause.

Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins, WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. The final ORDER BY clause is used to order the result of analytic functions. Analytic functions can appear in the select list of a query or subquery and in the ORDER BY clause.

Analytic functions allow you to divide query result sets into groups of rows called partitions. You can define partitions on columns or expressions. You can partition a query result set into just one partition holding all rows, a few large partitions or many small partitions holding just a few rows each.

You can define a sliding window for each row in the partition. This window determines the range of rows used to perform the calculations for the current row. Window sizes are based on a physical number of rows. The window has a starting row and an ending row and the window may move at one or both ends. For example, a window defined for a cumulative sum function would have its starting row fixed at the first row of the partition and the ending rows would slide from the start point to the last row of the partition. In contrast, a window defined for a moving average would have both the start point and end point slide.

You can set the window as large as all the rows in the partition or as small as one row within a partition.

You can specify multiple ordering expressions within each function. This is useful when using functions that rank values because the second expression can resolve ties between identical values for the first expression.

Analytic functions are commonly used to compute cumulative, moving, centered, and reporting aggregates.

Restrictions:

• Analytic functions are not supported in materialized views.

The list of analytic functions follows. Functions followed by an asterisk (*) support the WindowingClause.

• AVG *

• COUNT *

• DENSE_RANK

• FIRST_VALUE *

• LAST_VALUE *

• MAX *

• MIN *

• RANK

• ROW_NUMBER

• SUM *

SQL Syntax

Analytic function syntax:

AnalyticFunctionName ([arguments]) OVER ([AnalyticClause])

AnalyticClause::= QueryPartitionClause [ORDER BY OrderByClause [,...]
                                          [WindowingClause]] |
                  ORDER BY OrderByClause [,...] [WindowingClause]

QueryPartitionClause::= PARTITION BY { Expression[,Expression]... |
                                      (Expression [,Expression]...) 
                                     }

OrderByClause::= Expression [ASC|DESC] [NULLS {FIRST|LAST}]

WindowingClause::= ROWS { BETWEEN StartPoint AND EndPoint |
                          StartPoint
                        }

StartPoint::= UNBOUNDED PRECEDING | CURRENT ROW | PosNumConstantExpr 
                                                    { PRECEDING | FOLLOWING }

EndPoint::= UNBOUNDED FOLLOWING | CURRENT ROW | PosNumConstantExpr
                                                   { PRECEDING | FOLLOWING }

Parameters

Parameter	Description
AnalyticFunctionName	Name of analytic function.
arguments	Arguments for the analytic function. Number of arguments depends on the analytic function. Refer to the particular function for specific information on the arguments to the function.
OVER ( [ AnalyticClause ] )	Indicates that the function is an analytic function. This clause is computed after the FROM, WHERE, GROUP BY, and HAVING clauses. If you do not specify the AnalyticClause, then the analytic function is evaluated over the entire result set without partitioning, ordering, or using a window.
QueryPartitionClause	Optional clause used in AnalyticClause. Denoted by the PARTITION BY clause. If specified, the query result set is partitioned into groups based on the Expression list. If you omit this clause, then the function treats all rows of the query result set as a single group. You can specify multiple analytic functions in the same query using either the same or different PARTITION keys. Valid values for Expression are constants, columns, non-analytic functions or function expressions.
ORDER BY OrderByClause	Optional clause used in AnalyticClause. Use this clause to specify how data is ordered within the partition. Expression cannot be a column alias or position. You can order the values in a partition on multiple keys each defined by Expression and each qualified by an ordering sequence. Analytic functions operate in the order specified in this clause. However this clause does not guarantee the order of the result. Use the ORDER BY clause of the query to guarantee the final result ordering. If you specify the ORDER BY OrderByClause and you do not specify either a QueryPartitionClause or a WindowingClause, then the default window is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. If you do not specify the ORDER BY OrderByClause, then the order is indeterminate.
ASC | DESC	Specifies the ordering sequence (ascending or descending). ASC is the default. Clause is optional.
NULLS FIRST | NULLS LAST	Specifies whether rows that contain NULL values are specified first or last in the ordering sequence. NULLS LAST is the default for ascending order. NULLS FIRST is the default for descending order. Clause is optional.
WindowingClause	Clause is denoted by the ROWS keyword. Specifies for each row a window expressed in physical units (rows). The window is used for calculating the function result. The function is applied to all the rows in the window. The window moves through the query result set or partition from top to bottom. You cannot specify the WindowingClause if you have not specified the ORDER BY OrderByClause. The value returned by the analytic function may produce nondeterministic results unless the ordering sequence results in unique ordering. In this case, specify multiple columns in the OrderByClause to achieve unique ordering. For the list of functions that allow the WindowingClause, see "Analytic Functions".
BETWEEN...AND	Use the BETWEEN...AND clause to specify a start point (StartPoint) and end point (EndPoint) for the window. If you omit the BETWEEN...AND clause and attempt to specify one end point, then TimesTen considers this end point the start point and the end point defaults to the current row.
StartPoint	Valid values are UNBOUNDED PRECEDING, CURRENT ROW, PosNumConstantExpr PRECEDING or PosNumConstantExpr FOLLOWING. PosNumConstantExpr must be either a constant positive numeric value or an expression that evaluates to a constant positive numeric value.
EndPoint	Valid values are UNBOUNDED FOLLOWING, CURRENT ROW, PosNumConstantExpr PRECEDING or PosNumConstantExpr FOLLOWING. PosNumConstantExpr must be either a constant positive numeric value or an expression that evaluates to a constant positive numeric value.
UNBOUNDED PRECEDING	Use UNBOUNDED PRECEDING to indicate that the window starts at the first row of the partition. Cannot be used as the end point.
UNBOUNDED FOLLOWING	Use UNBOUNDED FOLLOWING to indicate that the window ends at the last row of the partition. Cannot be used as the start point.
CURRENT ROW	As a start point, CURRENT ROW specifies that the window begins at the current row. In this case, the end point cannot be PosNumConstantExpr PRECEDING. As an end point, CURRENT ROW specifies that the window ends at the current row. In this case, the start point cannot be PosNumConstantExpr FOLLOWING.
PosNumConstantExpr {PRECEDING | FOLLOWING }	If PosNumConstantExpr FOLLOWING is the start point, then the end point must be PosNumConstantExpr FOLLOWING or UNBOUNDED FOLLOWING. If PosNumConstantExpr PRECEDING is the end point, then the start point must be PosNumConstantExpr PRECEDING or UNBOUNDED PRECEDING. The end point PosNumConstantExpr must be greater or equal to the start point PosNumConstantExpr. PosNumConstantExpr must be either a constant positive numeric value or an expression that evaluates to a constant positive numeric value.