The SUMSQ user-defined aggregate

It has the following algorithm:

x12 + x22 + ... 

where each x_i is one column value; that is, one aggregate argument.

To determine the aggregate state for SUMSQ, examine what information needs to be available for each iteration of the aggregate. To perform one iteration of SUMSQ, the ITER function must:

1. Square the aggregate argument.

   The ITER function has access to the aggregate argument because the database server passes it in. Therefore, ITER does not require additional information to perform this step.

2. Add the squared argument to the partial sum of previous squared values.

   To add in the squared argument, the aggregate must keep a partial sum of the previous squared values. For the ITER function to have access to the partial sum from the previous iterations, the aggregate state must contain it.

The SUMSQ has a simple state because the data type of the partial sum is the same as that of the aggregate argument. For example, when the SUMSQ aggregate receives INTEGER values, this partial sum is also an INTEGER value. Therefore, SUMSQ can allow the database server to manage this state, which has the following effect on the design of the aggregate support functions:

• The INIT support function does not need to perform state management.

  An aggregate with a simple state does not need to explicitly handle the allocation and deallocation of the aggregate state. Instead, the database server automatically allocates the aggregate state and initializes it to NULL. Therefore, the INIT function does not require other INIT-function tasks (see Initialization tasks for the INIT aggregate support function). Therefore, this support function can safely be omitted from the aggregate definition.

• The COMBINE support function can be the same as its ITER function.

  No special processing is required to merge two partial states. The ITER function can adequately perform this merge.

The following code fragment shows the required aggregate support functions that handle an INTEGER argument for the SUMSQ user-defined aggregate.

/* SUMSQ ITER support function on INTEGER */
mi_integer iter_sumsq(state, value, fparam)
   mi_integer state;
   mi_integer value;
   MI_FPARAM *fparam;
{
   /* If 'state' is NULL, this is the first invocation. 
    * Just return square of 'value'.
    */
   if ( mi_fp_argisnull(fparam, 0) )
      return (value * value);
   else /* add 'state' and square of 'value' together */
      return (state + (value * value));
}

/* SUMSQ COMBINE support function on INTEGER */
mi_integer combine_sumsq(state1, state2)
   mi_integer state1, state2;
{
   /* Return the new partial sum from two parallel partial
    * sums
    */
   return (iter_sumsq(state1, state2));
}

The following SQL statement registers the SUMSQ user-defined aggregate in the database:

CREATE AGGREGATE sumsq
   WITH (ITER = iter_sumsq,
       COMBINE = combine_sumsq);

The following code fragment shows the CREATE FUNCTION statements that register the aggregate support functions for SUMSQ to handle INTEGER aggregate arguments.

CREATE FUNCTION iter_sumsq(state INTEGER, one_value INTEGER)
   RETURNS INTEGER
   WITH (HANDLESNULLS)
   EXTERNAL NAME '/usr/udrs/aggs/sums/sumsq.so'
   LANGUAGE C;

CREATE FUNCTION combine_sumsq(state1 INTEGER, state2 INTEGER)
   RETURNS INTEGER
   EXTERNAL NAME '/usr/udrs/aggs/sums/sumsq.so'
   LANGUAGE C;

For the tab1 table, which A table with a complexnum_t column defines, the following query uses the new SUMSQ aggregate function on the INTEGER column, col3:

SELECT SUMSQ(col3) FROM tab1;