UDAF in Lua: Example 1

This example creates a function called dp_avg that calculate an average for a double precision value. This UDA performs the same function as the SQL AVG aggregate function, but only for the DOUBLE data type.

Code

Put the following code into the file /tmp/udaf_example1.nzl:

SUM=1
COUNT=2

function initState(state)
    state[SUM] = 0
    state[COUNT] = 0
    return state
end

function accumulate(state, value)
    if value != nil then
        state[SUM]   = state[SUM] + value
        state[COUNT] = state[COUNT] + 1
    end
    return state
end

function merge(state1, state2)
    state1[SUM]   = state1[SUM]   + state2[SUM]
    state1[COUNT] = state1[COUNT] + state2[COUNT]
    return state1
end

function finalResult(state)
    return state[SUM] / state[COUNT]
end

function getState()
    state = {}
    state[1] = { "sum", double }
    state[2] = { "count", double }
    return state
end

function getType()
    return "uda"
end

function getName()
    return "dp_avg"
end

function getArgs()
    args = {}
    args[1] = { "value", double }
    return args
end

function getResult()
    return double
end

Compression

Compress the file /tmp/udaf_example1.nzl into a file with the name /tmp/udaf_example1.tar.

Deployment

Deploy the UDX to a Db2® instance by using the web console, or by issuing the following CLPPlus command:

SQL> IDA DEPLOYUDX SRC /tmp/udaf_example1.tar LANGUAGE lua PROJECT example1

Running

Invoke the function:

Running

The table table1 has the following contents:

SELECT * FROM table1

COL1 
---------- 
        10
        25 
        15
        50 
         5

Invoke the function:

SELECT dp_avg(col1) FROM table1

DP_AVG
------------------------
  +2.10000000000000E+001