Supported Window Types - Analytics Database - Teradata Vantage

SQL External Routine Programming

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
iiv1628111441820.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1147
lifecycle
latest
Product Category
Teradata Vantageā„¢

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.