Allows an aggregate UDF with a window specification applied to it to operate on a specified window of rows.
ANSI Compliance
This statement is ANSI SQL:2011 compliant, but includes non-ANSI Teradata extensions.
The requirement that parentheses appear when the argument list of an aggregate UDF is empty is a Teradata extension to preserve compatibility with existing applications.
In the presence of an ORDER BY clause and the absence of a ROWS or ROWS BETWEEN clause, ANSI SQL:2011 window aggregate functions use ROWS UNBOUNDED PRECEDING as the default aggregation group, whereas Teradata SQL window aggregate functions use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
The RESET WHEN clause is a Teradata extension to the ANSI SQL standard.
Required Privileges
You must have EXECUTE FUNCTION privileges on the function or on the database containing the function.
To invoke an aggregate UDF that takes a UDT argument or returns a UDT, you must have the UDTUSAGE privilege on the SYSUDTLIB database or on the specified UDT.
Syntax
udf_name ( [ argument [,...]] ) window
- window
OVER ( [ partition_by_clause ] [ order_by_clause ] [ rows_clause ] )
- partition_by_clause
PARTITION BY column_reference [,...]- order_by_clause
ORDER BY value_expression [,...] [ ASC | DESC ] [ RESET WHEN condition ]
- rows_clause
{ ROWS rows_clause_spec_1 | ROWS BETWEEN { UNBOUNDED PRECEDING AND rows_clause_spec_2 | value PRECEDING AND rows_clause_spec_2 | CURRENT ROW AND { UNBOUNDED | value} FOLLOWING } }- rows_clause_spec_1
{ { UNBOUNDED | value } PRECEDING | CURRENT ROW }- rows_clause_spec_2
{ UNBOUNDED FOLLOWING | value { PRECEDING | FOLLOWING } | CURRENT ROW }
Syntax Elements
- udf_name
- The name of the aggregate UDF on which the window specification is applied.
- argument
- A valid SQL expression.
- OVER
How values are grouped, ordered, and considered when computing the cumulative, group, or moving function.
Values are grouped according to the PARTITION BY and RESET WHEN clauses, sorted according to the ORDER BY clause, and considered according to the aggregation group within the partition.
- PARTITION BY
In its column_reference, or comma-separated list of column references, the group, or groups, over which the function operates.
PARTITION BY is optional. If there are no PARTITION BY or RESET WHEN clauses, then the entire result set, delivered by the FROM clause, constitutes a single group, or partition.
PARTITION BY clause is also called the window partition clause.
- ORDER BY
- In its value_expression, the order in which the values in a group, or partition, are sorted.
- ASC
- Ascending sort order. The default is ASC.
- DESC
- Descending sort order.
- RESET WHEN
The group or partition, over which the function operates, depending on the evaluation of the specified condition. If the condition evaluates to TRUE, a new dynamic partition is created inside the specified window partition.
RESET WHEN is optional. If there are no RESET WHEN or PARTITION BY clauses, then the entire result set, delivered by the FROM clause, constitutes a single partition.
If RESET WHEN is specified, then the ORDER BY clause must be specified also.
- condition
A conditional expression used to determine conditional partitioning. The condition in the RESET WHEN clause is equivalent in scope to the condition in a QUALIFY clause with the additional constraint that nested ordered analytical functions cannot specify a RESET WHEN clause. In addition, you cannot specify SELECT as a nested subquery within the condition.
The condition is applied to the rows in all designated window partitions to create sub-partitions within the particular window partitions.
For more information, see RESET WHEN in Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145 and the QUALIFY clause in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
- ROWS
The starting point for the aggregation group within the partition. The aggregation group end is the current row.
The aggregation group of a row R is a set of rows, defined relative to R in the ordering of the rows within the partition.
If there is no ROWS or ROWS BETWEEN clause, the default aggregation group is ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
- ROWS BETWEEN
The aggregation group start and end, which defines a set of rows relative to the current row in the ordering of the rows within the partition.
The row specified by the group start must precede the row specified by the group end.
If there is no ROWS or ROWS BETWEEN clause, the default aggregation group is ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
- UNBOUNDED PRECEDING
- The entire partition preceding the current row.
- UNBOUNDED FOLLOWING
- The entire partition following the current row.
- CURRENT ROW
- The start or end of the aggregation group as the current row.
- value PRECEDING
The number of rows preceding the current row.
The value for is always a positive integer literal.
The maximum number of rows in an aggregation group is 4096 when value PRECEDING appears as the group start or group end.
- value FOLLOWING
The number of rows following the current row.
The value for value is always a positive integer literal.
The maximum number of rows in an aggregation group is 4096 when value FOLLOWING appears as the group start or group end.