请教oracle一个自定义聚合函数 aggregate function 的问题，十分感谢！

[blackkettle]

请教各位神侠，

我想实现一个自定义函数，函数类型为 f_max(列名 column_name, 序号 val)，

要求此函数实现 求得列名为 column_name 的这个列的第 val个最大的值。

比如有一个表如下

CREATE TABLE t (id VARCHAR2(2), n INT);
INSERT INTO t VALUES ('A',25);
INSERT INTO t VALUES ('A',30);
INSERT INTO t VALUES ('A',55);
INSERT INTO t VALUES ('B',77);
INSERT INTO t VALUES ('C',7176);
INSERT INTO t VALUES ('C',5428);
INSERT INTO t VALUES ('C',7820);

要求

select f_max(n,1) from t 的结果为 7820, 对应为最大的 n,


select f_max(n,2) from t 的结果为 7176, 对应为第二个最大的 n,

...

如能帮助，万分感谢！！！

[cosio]

--不用自己定义吧!
--sql1:

 SELECT N,row_number() over(ORDER BY n DESC) rn FROM t

--result:
7820	1
7176	2
5428	3
77	4
55	5
30	6
25	7

--sql2

 SELECT N
 FROM 
 ( SELECT N,row_number() over(ORDER BY n DESC) rn FROM t)
 WHERE rn=1

--result
7820


 SELECT N
 FROM 
 ( SELECT N,row_number() over(ORDER BY n DESC) rn FROM t)
 WHERE rn=2

--result:
7176

[007-x]

楼主要实现的功能应该不能在自定义聚合函数中实现,因为首先要把所有值排序才能得到第N大的值,这就需要多次循环,而聚合函数只有一次循环.

[我心飞翔]

实测请参考：
查询所有：


查询第一大的


查询第三大的

[scrack]

row_number() over(ORDER BY n DESC)

[blackkettle]

Creating SecondMax()

Implement the type SecondMaxImpl to contain the ODCIAggregate routines.

create type SecondMaxImpl as object
(
max NUMBER, -- highest value seen so far
secmax NUMBER, -- second highest value seen so far
static function ODCIAggregateInitialize(sctx IN OUT SecondMaxImpl)
return number,
member function ODCIAggregateIterate(self IN OUT SecondMaxImpl,
value IN number) return number,
member function ODCIAggregateTerminate(self IN SecondMaxImpl,
returnValue OUT number, flags IN number) return number,
member function ODCIAggregateMerge(self IN OUT SecondMaxImpl,
ctx2 IN SecondMaxImpl) return number
);
/

Implement the type body for SecondMaxImpl.

create or replace type body SecondMaxImpl is
static function ODCIAggregateInitialize(sctx IN OUT SecondMaxImpl)
return number is
begin
sctx := SecondMaxImpl(0, 0);
return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT SecondMaxImpl, value IN number)
return number is
begin
if value > self.max then
self.secmax := self.max;
self.max := value;
elsif value > self.secmax then
self.secmax := value;
end if;
return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN SecondMaxImpl, returnValue OUT
number, flags IN number) return number is
begin
returnValue := self.secmax;
return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT SecondMaxImpl, ctx2 IN
SecondMaxImpl) return number is
begin
if ctx2.max > self.max then
if ctx2.secmax > self.secmax then
self.secmax := ctx2.secmax;
else
self.secmax := self.max;
end if;
self.max := ctx2.max;
elsif ctx2.max > self.secmax then
self.secmax := ctx2.max;
end if;
return ODCIConst.Success;
end;
end;
/

Create the user-defined aggregate.

CREATE FUNCTION SecondMax (input NUMBER) RETURN NUMBER
PARALLEL_ENABLE AGGREGATE USING SecondMaxImpl;

Using SecondMax()

SELECT SecondMax(salary), department_id
FROM employees
GROUP BY department_id
HAVING SecondMax(salary) > 9000;

[blackkettle]

各位大侠，

其实我想要的就是上面这个自定义聚合函数的扩充版本。

希望能仿照上面的方式自定义一个...

有了这个以后，还可以改写成其他的 min, 分位数函数等等。


万分感谢！