oracle+interval+second,Oracle自定义聚合函数之Interval To Second平均值

由于项目需要，在网上查了很多资料，没有找到有关求Interval To Second类型字段平均值的文章，于是我自己动手实现了这个功能。

一、创建Oracle Type: day_to_second_avg_type

create or replace type day_to_second_avg_type as object

( total interval day(9) to second(2), --参数1，用于做Sum运算

dataCount number, --参数2，统计数据库记录的总数

--初始化函数，用于初始化上下文环境

static function ODCIAggregateinitialize(sctx in out day_to_second_avg_type) return number,

--迭代函数，聚合操作正是在此进行

member function ODCIAggregateIterate(self IN OUT day_to_second_avg_type,value IN total%type) return number,

--Merge函数，用于并行和串行环境，合并两个环境的上下文信息

member function ODCIAggregateMerge(self IN OUT day_to_second_avg_type,ctx2 IN day_to_second_avg_type) return number,

--返回值的函数，在此做最后的加工处理

member function ODCIAggregateTerminate(self IN day_to_second_avg_type,returnValue OUT total%type,flags IN number) return number

);

/

--实现：初始化函数，用于初始化上下文环境

create or replace type body day_to_second_avg_type is

static function ODCIAggregateInitialize(sctx IN OUT day_to_second_avg_type) return number is

begin

sctx:= day_to_second_avg_type(numtodsinterval( 0,'SECOND'), 0); --初始化

return ODCIConst.Success;

end;

--实现：迭代函数，聚合操作正是在此进行

member function ODCIAggregateIterate(self IN OUT day_to_second_avg_type,value IN total%type) return number is

begin

self.total:=self.total+value; --字段值累加

self.dataCount:= self.dataCount+1; --统计字段个数

return ODCIConst.Success;

end;

--实现：Merge函数，用于并行和串行环境，合并两个环境的上下文信息

member function ODCIAggregateMerge(self IN OUT day_to_second_avg_type,ctx2 IN day_to_second_avg_type) return number is

begin

self.total:=self.total+ctx2.total; --合并

self.dataCount:= self.dataCount+ctx2.dataCount; --合并

return ODCIConst.Success;

end;

--实现：返回值的函数，在此做最后的加工处理

member function ODCIAggregateTerminate(self IN day_to_second_avg_type,returnValue OUT total%type,flags IN number) return number is

begin

returnValue:=self.total / self.dataCount; --interval day(9) to second(2)类型字段的统计Sum / 该字段个数

return ODCIConst.Success;

end;

end;

二、创建一个测试用视图

CREATE OR REPLACE VIEW view_test_interval AS

SELECT INTERVAL '2 01:23:16.3312' DAY(9) TO SECOND AS interval_col FROM DUAL union

SELECT INTERVAL '6 02:12:14.3312' DAY(9) TO SECOND AS interval_col FROM DUAL union

SELECT INTERVAL '3 04:20:23.3312' DAY(9) TO SECOND AS interval_col FROM DUAL union

SELECT INTERVAL '1 02:16:22.3312' DAY(9) TO SECOND AS interval_col FROM DUAL union

SELECT INTERVAL '9 02:12:11.3312' DAY(9) TO SECOND AS interval_col FROM DUAL;

三、创建一个函数并测试使用

CREATE OR REPLACE FUNCTION ds_avg(input view_test_interval.interval_col%type) RETURN view_test_interval.interval_col%type

PARALLEL_ENABLE AGGREGATE USING day_to_second_avg_type;

/

select ds_avg(interval_col) from view_test_interval;