Usage Notes - Advanced SQL Engine - Teradata Database

SQL Date and Time Functions and Expressions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
xmd1556127764262.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1211
lifecycle
previous
Product Category
Teradata Vantageā„¢
TD_SEQUENCED_AVG is a table function that takes three arguments. The arguments passed to the function are the specified columns in a subtable derived from using the WITH Request Modifier as follows:
  • The first argument is one or more grouping columns, not including the Period column. You must specify this argument as a dynamic UDT, where each column is an attribute of the UDT. For more information, see "NEW VARIANT_TYPE" in Teradata Vantageā„¢ - Data Types and Literals, B035-1143.
  • The second argument is a numeric column on which AVG() is requested. All numeric data types are supported. You must specify this argument as a dynamic UDT where the column is an attribute of the UDT.
  • The third argument is the Period column where you want to find the Period values that overlap or meet.
Input to the table function must be columns that are hash-redistributed on the grouping columns and sorted by the grouping columns and the Period values as follows:
  • You must specify a LOCAL ORDER BY clause that includes all of the grouping columns and the Period column in the same order that was specified in the input arguments. The sort order must be ascending.
  • You must include a HASH BY clause with at least one of the grouping columns. The HASH BY clause cannot include the Period column or any columns that are not part of the grouping columns.
You must invoke the function with a RETURNS clause that specifies the output columns as follows:
  • The output columns must include all of the grouping columns with the same data type and in the same order as the input columns.
  • You must include a numeric output column to contain the average result value. The data type of this column can be FLOAT or the same data type as the corresponding input column; however, to avoid possible rounding of the result value, it is recommended that you use FLOAT. To prevent a possible overflow error, you can use the CAST function to convert the data type of the input column to a larger numeric data type.
  • A Period column with the same Period data type as the input Period column.