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;

 

 

注：该资料主要来源于某位大神对ORACLE文档的翻译