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;