自定义分析函数

一、目的
自定义一个分析函数，实现median(中位值)聚合函数的功能:对输入的一组数排序，当元素数量为奇数时，返回中间元素的值；当元素数量为偶数时，返回中间两个元数的平均值。

二、定义数值数组类型
create or replace type t_number_array is varray(100) of number

三、定义分析函数类型
1、头
create or replace type t_med as object ( numbers t_number_array, --初始化 static function odciaggregateinitialize(this in out t_med) return number, --遍历 member function odciaggregateiterate(self in out t_med, value number) return number, --结束 member function odciaggregateterminate(self in t_med, returnvalue out number, flags in number) return number, --合并 member function odciaggregatemerge(self in out t_med, ctx2 in t_med) return number )

2、体
CREATE OR REPLACE TYPE BODY t_med IS --初始化 STATIC FUNCTION odciaggregateinitialize(this IN OUT t_med) RETURN NUMBER IS BEGIN this := t_med(t_number_array()); RETURN odciconst.success; END; --遍历 MEMBER FUNCTION odciaggregateiterate(SELF IN OUT t_med, VALUE NUMBER) RETURN NUMBER IS --将元素按倒序,插入到数组中 --新元素将要插入的位置 i_loc1 INTEGER; --移位时的数组指针 i_loc2 INTEGER; BEGIN --空值不处理 IF VALUE IS NULL THEN RETURN odciconst.success; END IF; --假定初始的位置是最后 i_loc1 := self.numbers.count + 1; FOR i IN 1 .. self.numbers.count LOOP IF VALUE > self.numbers(i) THEN i_loc1 := i; GOTO outer1; END IF; END LOOP; <<outer1>> --数组扩充一个元素 self.numbers.extend; i_loc2 := self.numbers.count; --插入位置的元素后移 WHILE i_loc2 > i_loc1 LOOP self.numbers(i_loc2) := self.numbers(i_loc2 - 1); i_loc2 := i_loc2 - 1; END LOOP; --新元素填入 self.numbers(i_loc1) := VALUE; RETURN odciconst.success; END; --结束 MEMBER FUNCTION odciaggregateterminate(SELF IN t_med, returnvalue OUT NUMBER, flags IN NUMBER) RETURN NUMBER IS BEGIN IF self.numbers.count = 0 THEN returnvalue := NULL; ELSE IF self.numbers.count MOD 2 = 0 THEN --元素数量是偶数,返回中间两个元素的平均值 returnvalue := (self.numbers(self.numbers.count / 2 + 1) + self.numbers(self.numbers.count / 2)) / 2; ELSE --元素数量是奇数,返回中间元素 returnvalue := self.numbers((self.numbers.count - 1) / 2 + 1); END IF; END IF; RETURN odciconst.success; END; --合并 MEMBER FUNCTION odciaggregatemerge(SELF IN OUT t_med, ctx2 IN t_med) RETURN NUMBER IS BEGIN NULL; RETURN odciconst.success; END; END;

四、定义分析函数

create or replace function f_med(p_value number) return number aggregate using t_med;

五、测试
1、表
create table test1 (
user_name varchar2(30),
deposit_date date,
amount number);

2、数据
delete from test1;
insert into test1 values ('jack',trunc(sysdate),100);
insert into test1 values ('jack',trunc(sysdate),1000);
insert into test1 values ('jack',trunc(sysdate),10000);
insert into test1 values ('jack',trunc(sysdate),100000);

3、查询
SELECT user_name,
deposit_date,
amount,
median(amount) over(PARTITION BY user_name, deposit_date) med_amount,
AVG(amount) over(PARTITION BY user_name, deposit_date) avg_amount,
f_med(amount) over(PARTITION BY user_name, deposit_date) med_amount2
FROM test1;

USER_NAMEDEPOSIT_DATEAMOUNTMED_AMOUNTAVG_AMOUNTMED_AMOUNT2
1jack2010-12-161005500277755500
2jack2010-12-1610005500277755500
3jack2010-12-16100005500277755500
4jack2010-12-161000005500277755500