Window Aggregate UDF | SQL Operators & User-Defined Functions | Teradata Vantage - Window Aggregate UDF - Advanced SQL Engine - Teradata Database

SQL Operators and User-Defined Functions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2023-04-27
dita:mapPath
qqu1556127655717.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1210
lifecycle
previous
Product Category
Teradata Vantage™

Purpose

Allows an aggregate UDF with a window specification applied to it to operate on a specified window of rows.

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
}
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 Condition Rules" 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.