以 Lua 编写的 UDAF：示例 2

此示例创建名为 max2 的分析函数（即，使用 OVER 子句调用的函数），该函数返回表中某一列的最大值所在行中另一列的值。

代码

将以下代码放入 /tmp/udaf_example2.nzl 文件：

--[[-------------------------------------------------------------------
This UDAF returns the value in a column that is in the same row as the maximum 
value of another column in the same table.

For example "select max2(x,y) from table1" returns the value in column x that 
is in the row that contains the maximum value in column y.

This UDAF takes, as input, two arguments of type char(255) and returns a 
single value of type char(255). You can use this UDAF as a template to create 
additional UDAFs for other data types. To do this, modify the definitions of 
the arguments, state variables, and result that are defined by the getArgs(), 
getState(), and getResult() methods.
------------------------------------------------------------------------------]]

local X=1
local Y=2

function accumulate(s, x, y)
    if x == null or y == null then
        return null
    end

    if s[X] == null then
        s[X] = x
        s[Y] = y
        return s
    end

    -- returning null leaves the state variables unchanged
    if s[Y] > y then
        return null
    end

    if s[Y] < y then
        s[X] = x
        s[Y] = y
        return s
    end

    if s[X] < x then
        s[X] = x
        return s
    end

    -- returning null leaves the state variables unchanged
    return null
end

function merge(s1, s2)

    -- returning null leaves the state variables unchanged
    if s1[Y] > s2[Y] then
        return null
    end

    if s1[Y] < s2[Y] then
        return s2
    end

    if s1[X] < s2[X] then
        return s2
    end

    -- returning null leaves the state variables unchanged
    return null
end

function finalResult(s)
    return s[X]
end

function getName()
    return "max2"
end

function getType()
    return "uda"
end

function getArgs()
    args={}
    args[X] = { "x", char(255) }
    args[Y] = { "y", char(255) }
    return args
end

function getState()
    state={}
    state[X] = { "", char(255) }
    state[Y] = { "", char(255) }
    return state
end

function getResult()
    return char(255)
end

压缩

将 /tmp/udaf_example2.nzl 文件压缩到名为 /tmp/udaf_example2.tar 的文件中。

部署

通过发出以下 CLPPlus 命令，将 UDX 部署到 Db2® 实例：

SQL> IDA DEPLOYUDX SRC /tmp/udaf_example2.tar LANGUAGE lua PROJECT example2

正在运行

表 table1 中的内容如下所示：

SELECT * FROM table1

COL1         COL2         COL3                
------------ ------------ -------
Harry        Potter       9                
Hermione     Granger      6                       
Ronald       Weasley      9
Albus        Dumbledore   3   
Tom          Riddle       2

调用函数：

SELECT max2(col1,col2) FROM table1

MAX2                                                                                                                                                                                                              
-----------------------------------------------------------------
Ronald

在列 col2 中，最大值（基于其 ASCII 代码）为 Weasley。在 Weasley 所在的行中，列 col1 的值是 Ronald。