欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-08-09 18:34:35
一、自定义聚集函数简介 oracle提供了很多预定义好的聚集函数,比如max(), sum(), avg(),但是这些预定义的聚集函数基本上都是适应于标量数据(scalar data),对于复杂的数...

一、自定义聚集函数简介

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;