用户定义聚合函数
适用于 ✅ 开源版 ✅ 专业版 ✅ 企业版
一些数据库支持用户自定义聚合函数，这些函数可以与 GROUP BY 子句 一起使用，或者作为 窗口函数 使用。Oracle 就是这样一个数据库。使用 Oracle，您可以定义以下 OBJECT 类型（该示例取自 Oracle 11g 文档）
CREATE TYPE U_SECOND_MAX AS OBJECT
(
MAX NUMBER, -- highest value seen so far
SECMAX NUMBER, -- second highest value seen so far
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT U_SECOND_MAX) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT U_SECOND_MAX, value IN NUMBER) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate(self IN U_SECOND_MAX, returnValue OUT NUMBER, flags IN NUMBER) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT U_SECOND_MAX, ctx2 IN U_SECOND_MAX) RETURN NUMBER
);
CREATE OR REPLACE TYPE BODY U_SECOND_MAX IS
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT U_SECOND_MAX)
RETURN NUMBER IS
BEGIN
SCTX := U_SECOND_MAX(0, 0);
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT U_SECOND_MAX, 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 U_SECOND_MAX, returnValue OUT NUMBER, flags IN NUMBER) RETURN NUMBER IS
BEGIN
RETURNVALUE := SELF.SECMAX;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT U_SECOND_MAX, ctx2 IN U_SECOND_MAX) 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;
上面的 OBJECT 类型可以这样用于函数声明
CREATE FUNCTION SECOND_MAX (input NUMBER) RETURN NUMBER PARALLEL_ENABLE AGGREGATE USING U_SECOND_MAX;
使用生成的聚合函数
jOOQ 的 代码生成器 将检测到此类聚合函数，并以不同于常规 用户自定义函数 的方式生成它们。它们实现了 org.jooq.AggregateFunction 类型，如手册中关于 聚合函数 的部分所述。以下是如何使用 jOOQ 中的 SECOND_MAX() 聚合函数
-- Get the second-latest publishing date by author SELECT SECOND_MAX(PUBLISHED_IN) FROM BOOK GROUP BY AUTHOR_ID
// Routines.secondMax() can be static-imported
create.select(secondMax(BOOK.PUBLISHED_IN))
.from(BOOK)
.groupBy(BOOK.AUTHOR_ID)
.fetch();
反馈
您对此页面有任何反馈吗？ 我们很乐意听取您的意见！