Oracle自定义聚合函数之Interval To Second平均值

来源：互联网发布：相貌评分软件结果编辑：程序博客网时间：2024/05/19 22:47

由于项目需要，在网上查了很多资料，没有找到有关求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 unionSELECT INTERVAL '6 02:12:14.3312' DAY(9) TO SECOND AS interval_col FROM DUAL unionSELECT INTERVAL '3 04:20:23.3312' DAY(9) TO SECOND AS interval_col FROM DUAL unionSELECT INTERVAL '1 02:16:22.3312' DAY(9) TO SECOND AS interval_col FROM DUAL unionSELECT 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;

0 0