Aggregating User-Defined Functions

The Sql Engine allows user-defined functions (UDF) to perform custom aggregation, i.e. functions used with the SQL group by operator. These user-defined functions can be used like SQL functions sum(), count(), max() etc. (Please see page eXtremeSQL User Defined Functions for further details.)

For an overview see page eXtremeSQL Functions

To use user-defined aggregating functions the following steps must be performed:

• Use the SQL set statement to set parameter implicit_aggregates to enable using aggregating functions:

     
    set implicit_aggregates=true;
     

• Write the UDF code in C++, Lua or Python that has exactly 2 arguments: state and ops of type array(T) that returns an array(T) where T is function type. (Parameter state is expected to be an internal state variable to accumulate temporary results and ops is an array of function arguments.
• Call the UDF in the following sequence:
  • First call with NULL as the state value and ops is non-NULL. Here the UDF is expected to create a state array and use ops as the first set of values. The state array is returned as function result.
  • Next call the UDF with a non-NULL state value and non-NULL value for ops to process the next set of ops values.
  • When dataset has been processed, call the UDF with NULL for ops to cause the function to perform final calculations and return its result as an array.

Note that even if the UDF is expected to take a scalar argument and return single scalar value, the aggregating UDF must pass array arguments and return an array.

Lua Example

The UDF could be created with Lua code like the following:

     
    create function my_avg(state array(int), ops array(int)) returns array(int) in 'lua' as '
        require("test_udf")
        return my_avg_impl(state, ops)
        end
        ';
     

The function my_avg_body() could be implemented as follows:

     
    function my_avg_impl(state, ops)
        if ops ~= nil then
            -- Aggregating part
            v = ops[1]
            if state == nil then
                state = {v, 1}
            else
                state[1] = state[1] + v
                state[2] = state[2] + 1
            end
            return state
        else
            -- Finalizing part
            local avg = {state[1] / state[2]}
            return avg
        end
    end
     

Consider table Employee initialized with the following data:

     
    create table Employee( id integer, dept_no integer, salary integer);
    insert into Employee values(1, 1      90000);
    insert into Employee values(2, 1      100000);
    insert into Employee values(3, 1      110000);
    insert into Employee values(4, 2      100000);
    insert into Employee values(5, 2      120000);
    insert into Employee values(6, 2      130000);
     

Then the UDF would be invoked with SQL statements like the following:

 
    set implicit_aggregates=true;
    select dept_no, my_avg([null]) from Employee group by dept_no;
    select dept_no, my_avg([salary]) from Employee group by dept_no;
    select dept_no, my_avg([null]) from Employee group by dept_no;
     

and would return the following result set:

 
    dept_no #2
    --------------------------------------------------------
    1       [100000]
    2       [120000]
     

C++ Example

For a C++ UDF, the SQL function registration might look like the following:

 
    create function bx_wt_avg(state array(double), ops array(double)) returns array(double)
        as 'api-sql-09-functions-aggregate',  'bx_wt_avg';
 

The corresponding C++ function prototype could be as follows:

 
    McoSql::Value* bx_wt_avg(McoSql::Runtime* runtime, McoSql::Vector<McoSql::Value>* params);
     

The actual function implementation can be found in the SDK sample source file "samples/native/sql/api/sql-09-functions/aggregate.udf.cpp".

Then the UDF would be invoked with SQL statements like the following:

 
    create table t(x double,y double);
    insert into t values (1,10),(2,20),(3,30);
    select bx_wt_avg([x,y]) from t;
     

and would return the following result set:

 
     #1
    --------------------------------------------------------
    [2.33333333333333, 0.014, 140, 60, 3, 6.95335580371252e-310, 360]