当前位置:文档之家› Oracle自定义聚合函数-分析函数

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

自定义聚合函数,分析函数
--from GTA Aaron
最近做一数据项目要用到连乘的功能,而Oracle数据库里没有这样的预定义聚合函数,当然利用数据库已有的函数进行数学运算也可以达到这个功能,如:
select exp(sum(ln(field_name))) from table_name;
不过今天的重点不是讲这个数学公式,而是如何自己创建聚合函数,实现自己想要的功能。

很幸运Oracle 允许用户自定义聚合函数,提供了相关接口,LZ研究了下,留贴共享。

首先介绍聚合函数接口:
用户可以通过实现Oracle的Extensibility Framework中的ODCIAggregate interface 来创建自定义聚合函数,而且自定义的聚合函数跟内建的聚合函数用法上没有差别。

通过实现ODCIAggregate rountines来创建自定义的聚合函数。

可以通过定义一个对象类型(Object Type),然后在这个类型内部实现ODCIAggregate 接口函数(routines),可以用任何一种Oracle支持的语言来实现这些接口函数,比如C/C++, JAVA, PL/SQL等。

在这个Object Type定义之后,相应的接口函数也都在该Object Type Body内部实现之后,就可以通过CREATE FUNCTION语句来创建自定义的聚合函数了。

每个自定义的聚合函数需要实现4个ODCIAggregate 接口函数,这些函数定义了任何一个聚合函数内部需要实现的操作:
1. 自定义聚合函数初始化操作,从这儿开始一个聚合函数。

初始化的聚合环境
(aggregation context)会以对象实例(object type instance)传回给oracle.
static function ODCIAggregateInitialize(var IN OUT agg_type ) return number 2. 自定义聚合函数,最主要的步骤,这个函数定义我们的聚合函数具体做什么操作,self 为
当前聚合函数的指针,用来与前面的计算结果进行关联。

这个函数用来遍历需要处理的
数据,被oracle重复调用。

每次调用的时候,当前的aggreation context 和新的(一组)值会作为传入参数。

这个函数会处理这些传入值,然后返回更新后的aggregation context. 这个函数对每一个NON-NULL的值都会被执行一次。

NULL值不会被传递个聚合函数。

member function ODCIAggregateIterate(self IN OUT agg_type ,value IN varchar2) return number
3. 用来合并两个聚合函数的两个不同的指针对应的结果,用户合并不同结果结的数据,特别
是处理并行(parallel)查询聚合函数的时候.
这个函数用来把两个aggregation context整合在一起,一般用来并行计算中(当一个函数被设置成enable parallel 处理的时候)。

member function ODCIAggregateMerge (self IN OUT agg_type, value IN agg_type) return number
4. 终止聚合函数的处理,返回聚合函数处理的结果.
这个函数是Oracle调用的最后一个函数。

它接收aggregation context作为参数,返回最后的aggregate value.
member function OCDIAggregateTerminate(self IN agg_type,returnValue OUT varchar2,flags IN number)
下一步我们就根据这些接口来创建连乘聚合函数,
首先创建OBJECT TYPE:
CREATE OR REPLACE TYPE sermult_context AS OBJECT(
multvalue NUMBER, -- 保存连乘后的值
--(该步骤是必须的)初始化函数,必须要实现的方法,用于在聚合运算的最开始部分,初始化上下文环境
static FUNCTION ODCIAggregateInitialize(serm IN OUT sermult_context) RETURN NUMBER,
--(该步骤是必须的)迭代运算函数,oracle依据该函数进行迭代运算,第一个参数为聚合运算的上下文,
--第二个参数为当前需要处理的值,可以为number varchar2等类型,
--在迭代过程中,如果当前值为null,则忽略该次迭代
member FUNCTION ODCIAggregateIterate(self IN OUT sermult_context,currvalue IN NUMBER) RETURN NUMBER,
--(该步骤是必须的,但是在执行过程中,oracle会有选择的执行该步骤)该函数用于合并两个上下文到一个上下文中,
--在并行和串行环境下均有可能发挥作用
member FUNCTION ODCIAggregateMerge(self IN OUT sermult_context, value IN sermult_context)RETURN NUMBER,
--(该步骤是必须的)该函数在聚合运算的最后一步运行,用于对结果进行处理并返回处理结果,
--第一个参数为上下文,第二个参数为返回值,可以为number,varchar2等类型
--第三个参数为标识位
MEMBER FUNCTION ODCIAggregateTerminate(self IN sermult_context,returnValue OUT NUMBER,flags IN NUMBER) RETURN NUMBER )
接下来实现接口函数功能,
create or replace type body sermult_context is
static function ODCIAggregateInitialize(serm IN OUT sermult_context) return number is
begin
serm := sermult_context(1);
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT sermult_context,currvalue IN NUMBER) return number is
begin
self.multvalue := self.multvalue*currvalue;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT sermult_context, value IN sermult_context) return number is
BEGIN
self.multvalue := self.multvalue*value.multvalue;
RETURN ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self IN sermult_context, returnValue OUT number, flags IN number) return number is
begin
returnValue := self.multvalue;
return ODCIConst.Success;
end;
END;
最后一步激动人心的时刻,创建连乘聚合函数,
CREATE FUNCTION SerMult (input NUMBER) RETURN NUMBER PARALLEL_ENABLE AGGREGATE USING sermult_context;
到此连乘聚合函数SerMult创建完成,下一步我们来进行测试,
WITH T1 AS
(
SELECT 5.5 A,'b' as b FROM DUAL
UNION ALL
SELECT 2.2 A,'b' as b FROM DUAL
UNION ALL
SELECT 3.3 A,'c' as b FROM DUAL
UNION ALL
SELECT 4.4 A,'c' as b FROM DUAL
)
SELECT b, SerMult(A) as SerM FROM T1 group by b
没有问题,我们再来用exp(sum(ln(field_name))) 来计算看下结果,
SELECT b, exp(sum(ln(a))) as SerM FROM T1 GROUP BY b
可见这种算法存在一定的精度问题。

前面提过自定义的聚合函数和内建的用法上差不多,那也就是说自定义的聚合函数也可作为分析函数,那么我们测试一下是否可行,
SELECT A,b,SerMult(A) OVER (PARTITION BY b) AS SerM FROM T1
看样子是没有问题的,一举两得哈。

相关主题