The PERCENT_GTR user-defined aggregate

Suppose you want to create a user-defined aggregate that determines the percentage of values greater than some user-specified value and returns this percentage as a fixed-point number in the range 0 - 100. The implementation of this UDA uses the following aggregate features:

Uses a setup argument to allow the end user to specify the value to compare against
Uses an opaque-type state to hold the state information and initialize the state in the INIT support function
Uses a COMBINE function that must do more than just call the ITER support function
Returns an aggregate result whose data type is different from that of the aggregate argument
Handles NULL values as aggregate arguments

The PERCENT_GTR user-defined aggregate needs the following state information:

The user-specified setup argument
The current number of values greater than the setup argument
The current number of values processed

Therefore, it uses the following C structure, named percent_state_t, to hold the aggregate state:

typedef struct percent_state
   {
   mi_integer gtr_than;
   mi_integer total_gtr;
   mi_integer total;
   } percent_state_t;

Because the size of the percent_state_t structure never exceeds the maximum opaque-type size, PERCENT_GTR can use an opaque-type state to hold its aggregate state. The following code shows the INIT aggregate support function that handles an INTEGER argument for the PERCENT_GTR aggregate:

/* PERCENT_GTR INIT support function for INTEGER */
percent_state_t *init_percentgtr(dummy_arg, gtr_than, fparam)
   mi_integer dummy_arg;
   mi_integer gtr_than;
   MI_FPARAM *fparam;
{
   percent_state_t *state;

   /* Allocate PER_ROUTINE memory for state and initialize it */
   state = mi_alloc(sizeof(percent_state_t));

   /* Check for a NULL-valued setup argument */
   if ( mi_fp_argisnull(fparam, 1) )
      state->gtr_than = 0;
   else
      state->gtr_than = gtr_than;
   state->total_gtr = 0;
   state->total = 0;

   return (state);
}

This INIT function performs the following tasks:

Handles a setup argument
This setup argument is the value that the end user specifies so that the aggregate knows which value to compare the aggregate arguments against. If the end user provides a NULL value for the setup argument, PERCENT_GTR checks for values greater than zero.
Allocates PER_ROUTINE memory for the opaque-type state
The INIT function does not need to allocate memory for an opaque-type state because the database server can perform the state management. However, because PERCENT_GTR already requires an INIT function to handle the setup argument, INIT allocates a PER_ROUTINE percent_state_t structure so that it can initialize the opaque-type state.

The following code implements the ITER aggregate support function that handles an INTEGER argument for the PERCENT_GTR aggregate:

/* PERCENT_GTR ITER support function for INTEGER */
percent_state_t *iter_percentgtr(curr_state, agg_arg, fparam)
   percent_state_t *curr_state;
   mi_integer agg_arg;
   MI_FPARAM *fparam;
{
   if ( mi_fp_argisnull(fparam, 1) == MI_TRUE )
      agg_arg = 0;

   if ( agg_arg > curr_state->gtr_than )
      curr_state->total_gtr += 1;

   curr_state->total += 1;

   return (curr_state);
}

The PERCENT_GTR aggregate is defined to handle NULL values (see Figure 1). This ITER function must check for a possible NULL aggregate argument. The function converts any NULL value to a zero so that the numeric comparison can occur.

The following COMBINE aggregate support function handles an INTEGER argument for the PERCENT_GTR aggregate:

/* PERCENT_GTR COMBINE support function for INTEGER */
percent_state_t *combine_percentgtr(state1, state2)
   percent_state_t *state1;
   percent_state_t *state2;
{
   state1->total += state2->total;
   state1->total_gtr += state2->total_gtr;

   mi_free(state2);

   return(state1);
}

Because PERCENT_GTR does not have a simple state, its COMBINE function must explicitly perform the merging of two parallel threads, as follows:

It adds the two partial sums (total and total_gtr).
It deallocates the PER_COMMAND memory for the second parallel thread (merging of the two states was done “in-place” in state1).

The following code shows the FINAL aggregate support function that handles an INTEGER argument for the PERCENT_GTR aggregate:

/* PERCENT_GTR FINAL support function for INTEGER */
mi_decimal *final_percentgtr(final_state)
   percent_state_t *final_state;
{
   mi_double_precision quotient;
   mi_decimal return_val;
   mi_integer ret;

   quotient = 
      ((mi_double_precision)(final_state->total_gtr)) / 
      ((mi_double_precision)(final_state->total)) * 100;

   if ( (ret = deccvdbl(quotient, &return_val)) < 0 )
      ret = deccvasc("0.00", 4, &return_val);
      
   return (&return_val);
}

The PERCENT_GTR aggregate returns a data type different from the aggregate state. The FINAL function must convert the final state from the aggregate-state data type (percent_state_t) to the aggregate-result data type (DECIMAL).

After you have successfully compiled and linked the aggregate support functions, you can define the PERCENT_GTR aggregate in the database. For a user-defined aggregate that uses an opaque-type state, this definition includes the following steps:

Use CREATE OPAQUE TYPE to register the opaque type that holds the opaque-type state.
Use CREATE AGGREGATE to register the aggregate.
Use CREATE FUNCTION to register the aggregate support functions.

The PERCENT_GTR aggregate uses a fixed-length opaque type, percent_state_t, to hold its opaque-type state. The following CREATE OPAQUE TYPE statement registers this opaque type:

CREATE OPAQUE TYPE percent_state_t (INTERNALLENGTH = 12);

The INTERNALLENGTH modifier specifies the size of the fixed-length C data structure, percent_state_t, that holds the opaque-type state.

The following code fragment shows the CREATE AGGREGATE statement that defines the PERCENT_GTR user-defined aggregate. This statement specifies the registered SQL names of the required aggregate support functions. It also includes the HANDLESNULLS modifier to indicate that the PERCENT_GTR aggregate does process NULL values as aggregate arguments. By default, the database server does not pass a NULL value to an aggregate.

Figure 1. Registering the PERCENT_GTR user-defined aggregate

CREATE AGGREGATE percent_gtr
   WITH (INIT = init_percent_gtr,
      ITER = iter_percent_gtr,
      COMBINE = combine_percent_gtr,
      FINAL = final_percent_gtr,
      HANDLESNULLS);

Suppose that the INIT, ITER, COMBINE, and FINAL aggregate support functions for the PERCENT_GTR aggregate are compiled and linked into a shared-object module named percent.

On UNIX or Linux, the executable code for the PERCENT_GTR aggregate support functions would be in a shared library named percent.so.

The following CREATE FUNCTION statements register the PERCENT_GTR aggregate support functions for an aggregate argument of the INTEGER data type:

CREATE FUNCTION init_percent_gtr(dummy INTEGER, gtr_val INTEGER)
RETURNING percent_state_t
WITH (HANDLESNULLS)
EXTERNAL NAME '/usr/udrs/aggs/percent/percent.so(init_percentgtr)'
LANGUAGE C;

CREATE FUNCTION iter_percent_gtr(state percent_state_t, one_value INTEGER)
RETURNS percent_state_t
WITH (HANDLESNULLS)
EXTERNAL NAME '/usr/udrs/aggs/percent/percent.so(iter_percentgtr)'
LANGUAGE C;

CREATE FUNCTION combine_percent_gtr(state1 percent_state_t, 
   state2 percent_state_t)
RETURNS percent_state_t
WITH (HANDLESNULLS)
EXTERNAL NAME '/usr/udrs/aggs/percent/percent.so(combine_percentgtr)'
LANGUAGE C;

CREATE FUNCTION final_percent_gtr (state percent_state_t)
RETURNS DECIMAL(5,2)
WITH (HANDLESNULLS)
EXTERNAL NAME '/usr/udrs/aggs/percent/percent.so(final_percentgtr)'
LANGUAGE C;

These CREATE FUNCTION statements register an SQL name for each of the aggregate support functions that you have written in C. They must all include the HANDLESNULLS routine modifier because the PERCENT_GTR aggregate handles NULL values.

The following query uses the PERCENT_GTR aggregate function on the INTEGER column, col3, to determine the percentage of values greater than 25:

SELECT PERCENT_GTR(col3, 20) FROM tab1;

With the rows that Figure 1 has inserted, the preceding query yields a DECIMAL(5,2) value of 33.33 percent: 2 of the 6 values are greater than 20 (24 and 31).