Basic Algorithm of an Aggregate Function | Teradata Vantage - 17.10 - Basic Algorithm of an Aggregate Function - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL External Routine Programming

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

Vantage invokes an aggregate UDF once for each item in an aggregation group, passing the detail values of a group through the input arguments. To accumulate summary information, an aggregate function must retain context each time it is called.

You do not need to understand or worry about how to create a group or how to code an aggregate UDF to deal with groups, because Teradata takes care of that. You only need to write the basic algorithm of combining the data passed to it to produce the desired result.

The following table describes the flow of an aggregate function for a group. To see the flow of a window aggregate function, see Window Aggregate Function Body.

Phase Value of aggregation_phase Argument Description
1 AGR_INIT This is the first time Vantage invokes the aggregate UDF for an aggregation group.
The function must:
  • Allocate intermediate storage and initialize it.
  • Process the first detail passed into the function.
2 AGR_DETAIL In this phase, Vantage calls the function once for each row to be aggregated for each group.

The function must combine the function argument input data with the intermediate storage defined for the group.

3 AGR_COMBINE This phase combines results from different AMPs for a specific group.

The function must combine the data for the two intermediate storage areas passed in.

4 AGR_FINAL No more input is expected for the group. The function must produce the final result for the group.
5 AGR_NODATA This phase is only presented when there is absolutely no data to aggregate.