我想实现一个自定义回归聚合函数,它类似于现有的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)的数字相加.

唷！