Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
User-defined aggregate functions
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Some databases support user-defined aggregate functions, which can then be used along with GROUP BY clauses or as window functions. An example for such a database is Oracle. With Oracle, you can define the following OBJECT
type (the example was taken from the Oracle 11g documentation):
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;
The above OBJECT
type is then available to function declarations as such:
CREATE FUNCTION SECOND_MAX (input NUMBER) RETURN NUMBER PARALLEL_ENABLE AGGREGATE USING U_SECOND_MAX;
Using the generated aggregate function
jOOQ's code generator will detect such aggregate functions and generate them differently from regular user-defined functions. They implement the org.jooq.AggregateFunction
type, as mentioned in the manual's section about aggregate functions. Here's how you can use the SECOND_MAX()
aggregate function with jOOQ:
-- 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();
Feedback
Do you have any feedback about this page? We'd love to hear it!