Aggregate support functions that the algorithm requires

To implement a user-defined aggregate, you must develop an algorithm that calculates an aggregate return value based on the aggregate-argument values.

You need to break this algorithm into the following steps.

Algorithm step	Aggregate support function
Calculations or initializations that must be done before the iterations can begin	INIT
Calculations that must be done on each aggregate argument to merge this argument into a partial result	ITER
Post-iteration tasks must be performed after all aggregate arguments were merged into a partial result	FINAL

All aggregate algorithms must include an ITER function to handle each aggregate argument. However, the INIT and FINAL support functions are optional. To determine whether your algorithm requires an INIT or FINAL function, make the following design assessments:

Are there calculations or initializations that must be done before the iterations can begin?
If the algorithm requires additional resources to perform its task (such as operating-system files or smart large objects), use the INIT function to set up these resources. The INIT function can also initialize the partial result.
Are there post-iteration tasks that must be performed after all aggregate arguments were merged into a partial result?
If the INIT function has set up resources to perform the aggregation, the FINAL function can deallocate or close resources so that they are free for other users. In addition, if the aggregation requires calculations that must be performed on the final partial result, use the FINAL function to perform these calculations.

For example, the following algorithm defines the SQSUM1 aggregate (which Table 1 describes):

(x₁ + x₂ + ... )²

where each x_i is one column value; that is, one aggregate argument. The ITER function for SQSUM1 takes a single aggregate argument and adds it to a partial sum (see Figure 1). The algorithm does not require initialization of additional resources. Therefore, no INIT function is required for this task. However, the INIT function can initialize the partial result (see Figure 1).

The SQSUM1 user-defined aggregate does require post-iteration calculations. When the last iteration is reached, the partial sum needs to be squared to obtain the aggregate return value. This final calculation is performed in a FINAL function and returned as the return value for the SQSUM1 aggregate (see Figure 1).

The SUMSQ user-defined aggregate (The SUMSQ user-defined aggregate) is an example of a user-defined aggregate that requires neither initialization nor post-iteration tasks. Therefore, it does not require the INIT and FINAL support functions.