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:
|2||AGR_DETAIL||This phase is triggered every time the forward row progresses.
Teradata Database 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 Teradata Database. You only need to maintain the window cache of rows and implement the semantics of the function on this cache of rows.