Oracle聚合函数不能单独使用,Oracle的用户定义聚合函数是否可以定义为使用两列？...

是的,如果你真的想要/需要它可能.你可以这样做：

首先,创建一个对象类型：

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

唷！ 😉