17.10 - Supported Window Types - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1147-171K
Language
English (United States)

You can apply a window specification to an aggregate function. The following window types are supported for aggregate UDFs:

Window Type Aggregation Group Partitioning Strategy
Reporting window ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING Hash partitioning
Cumulative window ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

or

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

Hash partitioning
Moving window
  • ROWS BETWEEN value PRECEDING AND CURRENT ROW
  • ROWS BETWEEN CURRENT ROW AND value FOLLOWING
  • ROWS BETWEEN value PRECEDING AND value FOLLOWING
  • ROWS BETWEEN value PRECEDING AND value PRECEDING
  • ROWS BETWEEN value FOLLOWING AND value FOLLOWING
Hash partitioning and value partitioning

The following window types are not supported for aggregate UDFs:

Window Type Aggregation Group
Moving window
  • ROWS BETWEEN UNBOUNDED PRECEDING AND value FOLLOWING
  • ROWS BETWEEN value PRECEDING AND UNBOUNDED FOLLOWING

The partitioning strategy helps to avoid hot AMP situations where the values of the columns of the PARTITION BY clause result in the distribution of too many rows to the same partition or AMP. You should make sure that the method uses the right set of columns for the PARTITION BY clause to avoid potential skew situations for the reporting or cumulative aggregate cases. For more information, see Teradata Vantageā„¢ - SQL Functions, Expressions, and Predicates, B035-1145.