Oracle的用户定义聚合函数是否可以定义为使用两列？

我想实现一个自界说回归聚合函数,它相同于现有的REGR_SLOPE.

我想要界说的函数必要得到两列作为参数,譬喻

select 
   T.EMPLOYEE_ID,CUSTOM_REGR_SLOPE(T.DATE,T.SALARY) as SALARY_TREND
from (...) T
group by T.EMPLOYEE_ID;

Oracle的文档表白这也许是不行能的,但我也许不擅长阅读这些内容;-).

我们行使Oracle 12.

办理要领

起首,建设一个工具范例：

create or replace type two_nums_t as object
(
num1 number,num2 number
);

然后建设自界说类型：

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);
    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)的数字相加.

唷！

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!