是否可以将Oracle的用户定义的聚合函数定义为与两列一起使用? [英] Can Oracle's user-defined aggregation function be defined for use with two columns?
问题描述
我想实现一个自定义的回归聚合函数,该函数类似于现有的REGR_SLOPE
.
I'd like to implement a custom regression aggregate function, that is similar to the existing REGR_SLOPE
.
我要定义的函数需要获取两列作为参数,例如
The function I want to define needs to get two columns as a parameter, e.g.
select
T.EMPLOYEE_ID,
CUSTOM_REGR_SLOPE(T.DATE, T.SALARY) as SALARY_TREND
from (...) T
group by T.EMPLOYEE_ID;
Oracle的文档表明这不可能,但是我可能不擅长阅读这两行之间的内容;-).
The Oracle's documentation suggests it may not be possible, but I may be bad at reading between the lines ;-) .
我们使用Oracle 12.
We use Oracle 12.
推荐答案
是的,如果您确实想要/需要,它是可能的.您可以执行以下操作:
Yes, its possible if you really want/need to. You can do something like this:
首先,创建一个对象类型:
First, create an object type:
create or replace type two_nums_t as object
(
num1 number,
num2 number
);
然后创建您的自定义规范:
Then create your custom spec:
CREATE OR REPLACE TYPE TotalSumPair
AS OBJECT (
runningSum1 number,
runningCnt1 number,
runningSum2 number,
runningCnt2 number,
STATIC FUNCTION ODCIAggregateInitialize
( actx IN OUT TotalSumPair
) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate
( self IN OUT TotalSumPair,
val IN two_nums_t
) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate
( self IN TotalSumPair,
returnValue OUT NUMBER, -- return
flags IN NUMBER
) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge
(self IN OUT TotalSumPair,
ctx2 IN TotalSumPair
) RETURN NUMBER
);
和自定义正文:
CREATE OR REPLACE TYPE BODY TotalSumPair AS
STATIC FUNCTION ODCIAggregateInitialize
( actx IN OUT TotalSumPair
) RETURN NUMBER IS
BEGIN
IF actx IS NULL THEN
actx := TotalSumPair(0,0,0,0);
ELSE
actx.runningSum1 := 0;
actx.runningCnt1 := 0;
actx.runningSum2 := 0;
actx.runningCnt2 := 0;
END IF;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateIterate
( self IN OUT TotalSumPair,
val IN two_nums_t
) RETURN NUMBER IS
BEGIN
self.runningSum1 := self.runningSum1 + nvl(val.num1,0);
self.runningSum2 := self.runningSum2 + nvl(val.num2,0);
self.runningCnt1 := self.runningCnt1 + 1;
self.runningCnt2 := self.runningCnt2 + 1;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateTerminate
( self IN TotalSumPair,
ReturnValue OUT NUMBER,
flags IN NUMBER
) RETURN NUMBER IS
BEGIN
--if (runningCnt1 <> 0) then
returnValue := (self.runningSum1 + self.runningSum2);
--else
-- returnValue := self.runningSum1;
--end if;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateMerge
(self IN OUT TotalSumPair,
ctx2 IN TotalSumPair
) RETURN NUMBER IS
BEGIN
self.runningSum1 := self.runningSum1 + ctx2.runningSum1;
self.runningCnt1 := self.runningCnt1 + ctx2.runningCnt1;
self.runningSum2 := self.runningSum2 + ctx2.runningSum2;
self.runningCnt2 := self.runningCnt2 + ctx2.runningCnt2;
RETURN ODCIConst.Success;
END;
END;
定义您的功能:
CREATE OR REPLACE FUNCTION total_sum_pair( x two_nums_t)
RETURN number PARALLEL_ENABLE
AGGREGATE USING TotalSumPair;
现在这样称呼:
with x as (
select 'X' as id, 1 as num1, 2 as num2 from dual
union all
select 'X' as id, 3 as num1, 4 as num2 from dual
union all
select 'Z' as id, 5 as num1, 6 as num2 from dual
)
select id, total_sum_pair(two_nums_t(num1, num2)) sum
from x
group by id;
输出:
ID SUM
X 10
Z 11
这将每个X行(1 + 2 + 3 + 4)和每个Y行(5 + 6)的数字相加.
This sums both numbers for each X rows (1+2+3+4), and each Y rows (5+6).
Ph! ;)
这篇关于是否可以将Oracle的用户定义的聚合函数定义为与两列一起使用?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!