oracle 自定义聚合函数(MAX_O3_8HOUR_ND) 计算最大的臭氧8小时滑动平均值
create or replace function MAX_O3_8HOUR_ND(value NUMBER) return NUMBER parallel_enable aggregate using MAX_O3_8HOUR;
CREATE OR REPLACE TYPE MAX_O3_8HOUR as object ( --聚合函数的实质就是一个对象 num NUMBER, var_array type_array, static function ODCIAggregateInitialize(v_self in out MAX_O3_8HOUR) return number, --对象初始化 member function ODCIAggregateIterate(self in out MAX_O3_8HOUR, value in number) return number, --聚合函数的迭代方法(这是最重要的方法) member function ODCIAggregateMerge(self in out MAX_O3_8HOUR, v_next in MAX_O3_8HOUR) return number, --当查询语句并行运行时,才会使用该方法,可将多个并行运行的查询结果聚合 member function ODCIAggregateTerminate(self in MAX_O3_8HOUR, return_value out number ,v_flags in number) return number --终止聚集函数的处理,返回聚集函数处理的结果. )
create or replace type body MAX_O3_8HOUR is static function ODCIAggregateInitialize(v_self in out MAX_O3_8HOUR) return number is begin --对象初始化 v_self := MAX_O3_8HOUR(0,type_array(0,0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0,0,0)); return ODCICONST.Success; end; member function ODCIAggregateIterate(self in out MAX_O3_8HOUR, value in number) return number is begin --聚合函数的迭代方法(这是最重要的方法) self.num:=self.num+1; self.var_array(self.num):=value; return ODCICONST.Success; end; member function ODCIAggregateMerge(self in out MAX_O3_8HOUR, v_next in MAX_O3_8HOUR) return number is begin --当查询语句并行运行时,才会使用该方法,可将多个并行运行的查询结果聚合 return ODCICONST.Success; end; member function ODCIAggregateTerminate(self in MAX_O3_8HOUR, return_value out number ,v_flags in number) return number is n number; re number; hour8 number; begin --终止聚集函数的处理,返回聚集函数处理的结果. --oracle数组序列从1开始 n:=self.num-7; re:=0; for i in 1..n loop hour8:=0; for j in i..(i+7) loop hour8:=hour8+self.var_array(j); end loop; IF re<hour8 THEN re:=hour8; END IF; end loop; return_value:=ceil(re/8); return ODCICONST.Success; end; end;