ORACLE分析函数----自定义聚合函数

oracle为我们提供了非常丰富的聚合函数，如SUM\AVG\MAX等。除此之外，我们还可以编写自己的聚合函数，当然，自定义聚合函数也可以作为分析函数来使用。

自定义聚合函数与oracle内置聚合函数并无太大区别，而且它可以使用任何oracle支持的语言来编写，如PL/SQL\C\C++\JAVA。在本文中，我们以PLSQL为开发语言，尝试编制我们自己的聚合函数。

编制ORACLE支持的自定义聚合函数，自然要依据oracle指定的规则来编写，我们不妨称之为ODCIAggregate规则。下面我们以编写获取组中第二大值的的聚合函数为例，看一下自定义聚合函数的编制过程。

        1：首先创建一个OBJECT TYPE
[sql] 
CREATE or REPLACE type secmax_context AS object(  
  firmax NUMBER, -- 保存最大值，这部分内容根据聚合函数操作的不同，有用户自行设置  
  secmax NUMBER, -- 保存第二大值，这部分内容根据聚合函数操作的不同，有用户自行设置  
    
  --（该步骤是必须的）初始化函数，必须要实现的方法，用于在聚合运算的最开始部分，初始化上下文环境  
  static FUNCTION ODCIAggregateInitialize(sctx IN OUT secmax_context) RETURN NUMBER,   
    
  --（该步骤是必须的）迭代运算函数，oracle依据该函数进行迭代运算，第一个参数为聚合运算的上下文，  
  --第二个参数为当前需要处理的值,可以为number varchar2等类型，  
  --在迭代过程中，如果当前值为null，则忽略该次迭代  
  member FUNCTION ODCIAggregateIterate(self  IN OUT secmax_context,value IN NUMBER) RETURN NUMBER,   
    
  --（该步骤是必须的，但是在执行过程中，oracle会有选择的执行该步骤）该函数用于合并两个上下文到一个上下文中，在并行和串行环境下均有可能发挥作用  
  member FUNCTION ODCIAggregateMerge(self IN OUT secmax_context, ctx2 IN secmax_context)RETURN NUMBER,  
    
  --（该步骤是必须的）该函数在聚合运算的最后一步运行，用于对结果进行处理并返回处理结果，  
  --第一个参数为上下文，第二个参数为返回值，可以为number，varchar2等类型  
  --第三个参数为标识位  
  member FUNCTION ODCIAggregateTerminate(self IN secmax_context,returnValue OUT NUMBER,flags IN NUMBER) RETURN NUMBER  
);  
2：实现该OBJECT TYPE
[sql] 
create or replace type body secmax_context is   
  
static function ODCIAggregateInitialize(sctx IN OUT secmax_context) return number is   
begin  
  sctx := secmax_context(0, 0);  
  return ODCIConst.Success;  
end;  
  
member function ODCIAggregateIterate(self IN OUT secmax_context, value IN number) return number is  
begin  
  if value > self.firmax then  
    self.secmax := self.firmax;  
    self.firmax := value;  
  elsif value > self.secmax then  
    self.secmax := value;  
  end if;  
  return ODCIConst.Success;  
end;  
  
member function ODCIAggregateTerminate(self IN secmax_context, returnValue OUT number, flags IN number) return number is  
begin  
  returnValue := self.secmax;  
  return ODCIConst.Success;  
end;  
  
member function ODCIAggregateMerge(self IN OUT secmax_context, ctx2 IN secmax_context) return number is  
begin  
  if ctx2.firmax > self.firmax then  
    if ctx2.secmax > self.firmax then   
      self.secmax := ctx2.secmax;  
          else  
      self.secmax := self.firmax;  
    end if;  
    self.firmax := ctx2.firmax;  
  elsif ctx2.firmax > self.secmax then  
    self.secmax := ctx2.firmax;  
  end if;  
  return ODCIConst.Success;  
end;  
end;  
        3：创建聚合函数
[sql] 
CREATE FUNCTION SecMax (input NUMBER) RETURN NUMBER PARALLEL_ENABLE AGGREGATE USING secmax_context;  
        测试结果：
[sql] 
SQL> select secmax(rownum) from costs;  
  
SECMAX(ROWNUM)  
--------------  
     82111  
  
SQL> select max(rownum) from costs;  
  
MAX(ROWNUM)  
-----------  
      82112

并行执行聚合函数：

与内置函数一样，自定义聚合函数也可以并行执行。并指执行时，首先是各个slave执行聚合运算，然后将结果进行合并，合并是通过ODCIAggregateMerge实现的。下图展示了这个过程：

注意：我们可以将自定义聚合函数作为分析函数使用，但是为了提高分析函数的执行效率，我们可以对自定义聚合函数做一些处理.