Oracle自定义聚合函数-分析函数

自定义聚合函数，分析函数--fromGTAAaron最近做一数据项目要用到连乘的功能，而Oracle数据库里没有这样的预定义聚合函数，当然利用数据库已有的函数进行数学运算也可以达到这个功能，如：selectexp(sum(ln(field_name)))fromtable_name;不过今天的重点不是讲这个数学公式，而是如何自己创建聚合函数，实现自己想要的功能。很幸运Oracle允许用户自定义聚合函数，提供了相关接口，LZ研究了下，留贴共享。首先介绍聚合函数接口：用户可以通过实现Oracle的ExtensibilityFramework中的ODCIAggregateinterface来创建自定义聚合函数，而且自定义的聚合函数跟内建的聚合函数用法上没有差别。通过实现ODCIAggregaterountines来创建自定义的聚合函数。可以通过定义一个对象类型（ObjectType），然后在这个类型内部实现ODCIAggregate接口函数(routines)，可以用任何一种Oracle支持的语言来实现这些接口函数，比如C/C++,JAVA,PL/SQL等。在这个ObjectType定义之后，相应的接口函数也都在该ObjectTypeBody内部实现之后，就可以通过CREATEFUNCTION语句来创建自定义的聚合函数了。每个自定义的聚合函数需要实现4个ODCIAggregate接口函数，这些函数定义了任何一个聚合函数内部需要实现的操作：1.自定义聚合函数初始化操作,从这儿开始一个聚合函数。初始化的聚合环境(aggregationcontext)会以对象实例(objecttypeinstance)传回给oracle.staticfunctionODCIAggregateInitialize(varINOUTagg_type)returnnumber2.自定义聚合函数,最主要的步骤,这个函数定义我们的聚合函数具体做什么操作，self为当前聚合函数的指针,用来与前面的计算结果进行关联。这个函数用来遍历需要处理的数据，被oracle重复调用。每次调用的时候，当前的aggreationcontext和新的（一组）值会作为传入参数。这个函数会处理这些传入值，然后返回更新后的aggregationcontext.这个函数对每一个NON-NULL的值都会被执行一次。NULL值不会被传递个聚合函数。memberfunctionODCIAggregateIterate(selfINOUTagg_type,valueINvarchar2)returnnumber3.用来合并两个聚合函数的两个不同的指针对应的结果,用户合并不同结果结的数据,特别是处理并行(parallel)查询聚合函数的时候.这个函数用来把两个aggregationcontext整合在一起，一般用来并行计算中（当一个函数被设置成enableparallel处理的时候）。memberfunctionODCIAggregateMerge(selfINOUTagg_type,valueINagg_type)returnnumber4.终止聚合函数的处理,返回聚合函数处理的结果.这个函数是Oracle调用的最后一个函数。它接收aggregationcontext作为参数，返回最后的aggregatevalue.memberfunctionOCDIAggregateTerminate(selfINagg_type,returnValueOUTvarchar2,flagsINnumber)下一步我们就根据这些接口来创建连乘聚合函数，首先创建OBJECTTYPE:CREATEORREPLACETYPEsermult_contextASOBJECT(multvalueNUMBER,--保存连乘后的值--（该步骤是必须的）初始化函数，必须要实现的方法，用于在聚合运算的最开始部分，初始化上下文环境staticFUNCTIONODCIAggregateInitialize(sermINOUTsermult_context)RETURNNUMBER,--（该步骤是必须的）迭代运算函数，oracle依据该函数进行迭代运算，第一个参数为聚合运算的上下文，--第二个参数为当前需要处理的值,可以为numbervarchar2等类型，--在迭代过程中，如果当前值为null，则忽略该次迭代memberFUNCTIONODCIAggregateIterate(selfINOUTsermult_context,currvalueINNUMBER)RETURNNUMBER,--（该步骤是必须的，但是在执行过程中，oracle会有选择的执行该步骤）该函数用于合并两个上下文到一个上下文中，--在并行和串行环境下均有可能发挥作用memberFUNCTIONODCIAggregateMerge(selfINOUTsermult_context,valueINsermult_context)RETURNNUMBER,--（该步骤是必须的）该函数在聚合运算的最后一步运行，用于对结果进行处理并返回处理结果，--第一个参数为上下文，第二个参数为返回值，可以为number，varchar2等类型--第三个参数为标识位MEMBERFUNCTIONODCIAggregateTerminate(selfINsermult_context,returnValueOUTNUMBER,flagsINNUMBER)RETURNNUMBER)接下来实现接口函数功能，createorreplacetypebodysermult_contextisstaticfunctionODCIAggregateInitialize(sermINOUTsermult_context)returnnumberisbeginserm:=sermult_context(1);returnODCIConst.Success;end;memberfunctionODCIAggregateIterate(selfINOUTsermult_context,currvalueINNUMBER)returnnumberisbeginself.multvalue:=self.multvalue*currvalue;returnODCIConst.Success;end;memberfunctionODCIAggregateMerge(selfINOUTsermult_context,valueINsermult_context)returnnumberisBEGINself.multvalue:=self.multvalue*value.multvalue;RETURNODCIConst.Success;end;memberfunctionODCIAggregateTerminate(selfINsermult_context,returnValueOUTnumber,flagsINnumber)returnnumberisbeginreturnValue:=self.multvalue;returnODCIConst.Success;end;END;最后一步激动人心的时刻，创建连乘聚合函数，CREATEFUNCTIONSerMult(inputNUMBER)RETURNNUMBERPARALLEL_ENABLEAGGREGATEUSINGsermult_context;到此连乘聚合函数SerMult创建完成，下一步我们来进行测试，WITHT1AS(SELECT5.5A,'b'asbFROMDUALUNIONALLSELECT2.2A,'b'asbFROMDUALUNIONALLSELECT3.3A,'c'asbFROMDUALUNIONALLSELECT4.4A,'c'asbFROMDUAL)SELECTb,SerMult(A)asSerMFROMT1groupbyb没有问题，我们再来用exp(sum(ln(field_name)))来计算看下结果，SELECTb,exp(sum(ln(a)))asSerMFROMT1GROUPBYb可见这种算法存在一定的精度问题。前面提过自定义的聚合函数和内建的用法上差不多，那也就是说自定义的聚合函数也可作为分析函数，那么我们测试一下是否可行，SELECTA,b,SerMult(A)OVER(PARTITIONBYb)ASSerMFROMT1看样子是没有问题的，一举两得哈。