ORACLE分析函数(7)----自定义聚合函数

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

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

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

1：首先创建一个OBJECT TYPE

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

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：创建聚合函数

CREATE FUNCTION SecMax (input NUMBER) RETURN NUMBER PARALLEL_ENABLE AGGREGATE USING secmax_context;

测试结果：

SQL> select secmax(rownum) from costs;

SECMAX(ROWNUM)
--------------
	 82111

SQL> select max(rownum) from costs;

MAX(ROWNUM)
-----------
      82112

并行执行聚合函数：

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

注意：我们可以将自定义聚合函数作为分析函数使用，但是为了提高分析函数的执行效率，我们可以对自定义聚合函数做一些处理，详细内容可以参考《Data Cartridge Developer's Guide 》