Basic Algorithm of a Window Aggregate Function | Teradata Vantage - 17.10 - Basic Algorithm of a Window Aggregate Function - 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)

The following table describes the flow of a window aggregate function for a group.

Phase Value of aggregation_phase Argument Description
1 AGR_INIT This phase is triggered once per partition when starting evaluation of a new aggregation group.
The function must:
  • Allocate and initialize intermediate storage for accumulating data passed in during the various aggregation phases.
  • Save the first set of data values passed in through arguments into the intermediate storage area.
2 AGR_DETAIL This phase is triggered every time the forward row progresses.

Vantage calls the function once for each row to be aggregated for each group.

The function must accumulate the argument input data into the intermediate storage defined for the specific group.

3 AGR_COMBINE This phase is not applicable for window aggregate functions, but may be included to generate an error if this phase is reached erroneously.

This phase is used for aggregate UDFs. For more information, see Aggregate Function Parameter List.

6 AGR_MOVINGTRAIL This phase is applicable for the moving window type.

This phase is triggered only by the last few rows of a moving window when the forward pointer to the window reaches the end of the partition.

The phase does not provide any new values to the function, but indicates to the function that processing has reached the end of the partition.

The function can use this phase to adjust the necessary internal count and offset values to reflect the actual size as the window diminishes towards the end of the partition.

4 AGR_FINAL This phase is invoked at the time the final evaluated result needs to be moved into the result row.

No more input is expected for the group, and the function produces the final aggregate result for the group.

5 AGR_NODATA This phase is only presented when there is absolutely no data to aggregate.

The function provides a result for a null aggregate set.

You must maintain a cache of rows corresponding to the window size. It may be useful to maintain a counter value that indicates the total rows read so far. For example, if C represents the counter, then the window of rows for evaluation would be:

if (C < window_size),
   window size would be C
else
   window size would be as indicated in function context.

The base of the window would be C - window_size and the end of the window would correspond to C.

The complexities of the various window combinations (PRECEDING/FOLLOWING/CURRENT, etc) are handled by Vantage. You only need to maintain the window cache of rows and implement the semantics of the function on this cache of rows.