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 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);
}

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:

1. Use CREATE OPAQUE TYPE to register the opaque type that holds the opaque-type state.
2. Use CREATE AGGREGATE to register the aggregate.
3. 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 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.

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

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;

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;