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;