ORACLE学习之自定义聚合函数概述

一、自定义聚集函数简介

oracle提供了很多预定义好的聚集函数，比如max(), sum(), avg()，但是这些预定义的聚集函数基本上都是适应于标量数据(scalar data)，对于复杂的数据类型，比如说用户自定义的object type, clob等，是不支持的。

但是，幸运的是，用户可以通过实现oracle的extensibilityframework中的odciaggregateinterface来创建自定义聚集函数，而且自定义的聚集函数跟内建的聚集函数用法上没有差别。odci是oracle datacartridge interface 几个单词的首字母缩写。

二、overview of user-defined aggregate functions——用户自定义聚合函数概述

通过实现odciaggregaterountines来创建自定义的聚集函数。可以通过定义一个对象类型（object type），然后在这个类型内部实现odciaggregate 接口函数(routines)，可以用任何一种oracle支持的语言来实现这些接口函数，比如c/c++, java, pl/sql等。在这个object type定义之后，相应的接口函数也都在该object type body内部实现之后，就可以通过create function语句来创建自定义的聚集函数了。

每个自定义的聚集函数需要实现4个odciaggregate 接口函数，这些函数定义了任何一个聚集函数内部需要实现的操作，这些函数分别是 initialization, iteration, merging 和termination。

（1）odciaggregateinitialize这个函数用来执行初始化操作(initialization). oracle会调用这个函数来初始化自定义函数计算。初始化的聚集环境(aggregationcontext)会以对象实例(objecttype instance)传回给oracle.

（2）odciaggregateiterate这个函数用来遍历需要处理的数据，被oracle重复调用。每次调用的时候，当前的aggreation context 和新的（一组）值会作为传入参数。这个函数会处理这些传入值，然后返回更新后的aggregation context. 这个函数对每一个non-null的值都会被执行一次。null值不会被传递个聚集函数。

（3）odciaggregatemerge 这个函数用来把两个aggregationcontext整合在一起，一般用来并行计算中（当一个函数被设置成enable parallel 处理的时候）。

（4）odciaggregateterminate 这个函数是oracle调用的最后一个函数。它接收aggregationcontext作为参数，返回最后的aggregate value.

--example: 下面介绍自定义聚集函数是如何工作的
select avg(t.sales)from annualsales tgroupby t.state;
--为了完成求平均值的计算，avg函数经历下面几个步骤：
--(1) initializes： 初始化aggregationcontext:
runningsum = 0;  runningcount = 0;
--(2) iteratively 处理每个连续的输入，同时更新aggregation context:
runningsum += inputval; runningcount ++;
--(3) 【这步可选】merge 整合两个aggregationcontext 返回一个aggregation context. 如果需要这一步的话，它是在termination之前执行。
runningsum = runningsum1 + runningsum2;
runningcount = runningcount1 + runningcount2;
--(4) terminates 计算出最后的结果，通过最后的aggregation context来返回最后的aggregated value.
return (runningsum / runningcount);
--如果avg是自定义的聚集函数的话，与之相对应的对象类型(object type)需要实现对应的odciaggregate的接口函数。
--变量runningsum 和 runningcount 是对象类型中的属性(attribute). 

三、creating a user-defined aggregate——创建用户自定义聚合函数

创建一个自定义聚集函数分成两步：如下面两个例子所示：

--example: 如何实现odciaggregate接口：
create type spatialunionroutines(
	static function odciaggregateinitialize( ... ) ...,
	member function odciaggregateiterate(...) ... ,
	member function odciaggregatemerge(...) ..., 
	member function odciaggregateterminate(...)
	); 
create type body spatialunionroutines is ...end;  
--example：如何定义自定义聚集函数：
create function spatialunion(x geometry)
    return geometry aggregate using spatialunionroutines; 

注意在定义函数的时候需要通过aggregate using语句来关联对应的实现了odciaggregate接口的object type。

四、using a user-defined aggregate——使用用户自定义聚合函数

自定义的聚集函数可以像内置的聚集函数一样使用，可以用在select, order by, having语句中。下面几个例子说明如何使用上面定义的自定义函数spatialunion

--example: 用在select语句中
select spatialunion(geometry) from counties group by state;
--example： 用在having语句中，
select groupcol, myudag(col) from tabgroupby groupcol having myudag(col) > 100order by myudag(col); 
--example： 其他
select ..., myudag(col) from tabgroup by rollup(gcol1, gcol2); 

自定义聚集函数可以跟all, distinct一起使用，亦可以用在group by的扩展语句中，像rollup, cube, grouping sets.

五、evaluating user-defined aggregates in parallel——并行评估用户自定义聚集函数

跟内置的聚集函数一样，自定义的聚集函数也可以并行来处理，需要注意的是，自定义的聚集函数需要声明为parallel-enabled, 如下：

create function myudag(...)
    return...parallel_enable aggregate using myaggrroutines; 

六、user-defined aggregates and analytic functions——用户自定义聚合和分析函数

自定义的聚集函数可以被当做analytic函数来用：

select account_number,trans_date,trans_amount,myavg(trans_amount)over partition by account_number 
    order by trans_date range interval'7' day preceding as mavg_7day from ledger;

七、reusing the aggregation context for analytic function——分析函数复用聚合文本

当一个自定义的聚集函数被用来做analytic function的时候，对每条记录对应的window都会计算一次aggregate。一般的说来，每个连续的窗口包含大部分相同的数据集合。

可以通过实现odciaggregatedelete接口函数来让oracle更有效地复用aggregation context.

八、an complete example forcreating and using a user-defined aggregate function——创建和使用用户自定义聚合函数的完整示例

secondmax()返回一组数里面第二大的那个值。

①、实现类型secondmaximpl，该类型包含了odciaggregate接口函数，

create type secondmaximpl as object(
        --保存最大值，这部分内容根据聚合函数操作的不同，有用户自行设置
        max number,
        --保存第二大的值，这部分内容根据聚合函数操作的不同，有用户自行设置        
        secmax number,
        --（该步骤是必须的）初始化函数，必须要实现的方法，用于在聚合运算的最开始部分，初始化上下文环境  
        static function odciaggregateinitialize(sctxin out secondmaximpl) return number, 
        --（该步骤是必须的）迭代运算函数，oracle依据该函数进行迭代运算，第一个参数为聚合运算的上下文，  
        --第二个参数为当前需要处理的值,可以为number varchar2等类型，  
        --在迭代过程中，如果当前值为null，则忽略该次迭代  
	member function odciaggregateiterate(self in out secondmaximpl,value in number) return number, 
        --（该步骤是必须的，但在执行中，oracle会有选择执行该步骤）该函数用于合并两个上下文到一个上下文中，在并行和串行环境下均有可能发挥作用 
	member function odciaggregateterminate(selfin secondmaximpl,returnvalue out number, flags in number) return number,
        --（该步骤是必须的）该函数在聚合运算的最后一步运行，用于对结果进行处理并返回处理结果，  
        --第一个参数为上下文，第二个参数为返回值，可以为number，varchar2等类型  
        --第三个参数为标识位  
        member function odciaggregatemerge(self in out secondmaximpl,ctx2 in secondmaximpl) return number
	);

②实现类型secondmaximpl的body

create or replace type body secondmaximplis

   static function odciaggregateinitialize(sctx in out secondmaximpl) return number is 
        begin  
	    sctx := secondmaximpl(0,0);  
	    return odciconst.success;
	end; 
		 
     member function odciaggregateiterate(self in out secondmaximpl, value in number)return number is 
         begin  
             if value >self.max then    
	         self.secmax :=self.max;    
	         self.max := value;  
	     else if value > self.secmax then    
	         self.secmax := value;  
	     end if; 
		 return odciconst.success;
          end; 
		
     member function odciaggregateterminate(self in secondmaximpl,return value out number, flags in number) return number is 
         begin 
             return value := self.secmax; 
	     return odciconst.success;
	 end; 
		
      member function odciaggregatemerge(self in out secondmaximpl, ctx2 in secondmaximpl) return number is 
          begin  
	        if ctx2.max > self.max then    
	            if ctx2.secmax > self.secmax then       
		        self.secmax := ctx2.secmax;    
		    else 
		        self.secmax := self.max;    
		    end if;    
		    self.max := ctx2.max;  
		else if ctx2.max > self.secmax then    
		    self.secmax := ctx2.max;  
		end if; 
		return odciconst.success;
	   end;
end; 

③创建自定义聚集函数secondmax()

create function secondmax (input number) return number parallel_enable aggregate using secondmaximpl; 

④使用自定义聚集函数secondmax()

select secondmax(salary),department_id from employees group by department_id having secondmax(salary) > 9000;