New versions: Dev (3.15) | Latest (3.14) | 3.13 | 3.12 | 3.11 | 3.10 | 3.9 | 3.8 | Old versions: 3.7 | 3.6 | 3.5 | 3.4 | 3.3 | 2.6
Window functions
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Most major RDBMS support the concept of window functions.
As previously discussed, any org.jooq.AggregateFunction can be transformed into a window function using the over() method. See the chapter about aggregate functions for details. In addition to those, there are also some more window functions supported by jOOQ, as declared in the DSL:
// Ranking functions
WindowOverStep<Integer> rowNumber();
WindowOverStep<Integer> rank();
WindowOverStep<Integer> denseRank();
WindowOverStep<BigDecimal> percentRank();
// Windowing functions
<T> WindowIgnoreNullsStep<T> firstValue(Field<T> field);
<T> WindowIgnoreNullsStep<T> lastValue(Field<T> field);
<T> WindowIgnoreNullsStep<T> nthValue(Field<T> field, int nth);
<T> WindowIgnoreNullsStep<T> nthValue(Field<T> field, Field<Integer> nth);
<T> WindowIgnoreNullsStep<T> lead(Field<T> field);
<T> WindowIgnoreNullsStep<T> lead(Field<T> field, int offset);
<T> WindowIgnoreNullsStep<T> lead(Field<T> field, int offset, T defaultValue);
<T> WindowIgnoreNullsStep<T> lead(Field<T> field, int offset, Field<T> defaultValue);
<T> WindowIgnoreNullsStep<T> lag(Field<T> field);
<T> WindowIgnoreNullsStep<T> lag(Field<T> field, int offset);
<T> WindowIgnoreNullsStep<T> lag(Field<T> field, int offset, T defaultValue);
<T> WindowIgnoreNullsStep<T> lag(Field<T> field, int offset, Field<T> defaultValue);
// Statistical functions
WindowOverStep<BigDecimal> cumeDist();
WindowOverStep<Integer> ntile(int number);
// Inverse distribution functions
OrderedAggregateFunction<BigDecimal> precentileCont(Number number);
OrderedAggregateFunction<BigDecimal> precentileCont(Field<? extends Number> number);
OrderedAggregateFunction<BigDecimal> precentileDisc(Number number);
OrderedAggregateFunction<BigDecimal> precentileDisc(Field<? extends Number> number);
SQL distinguishes between various window function types (e.g. "ranking functions"). Depending on the function, SQL expects mandatory PARTITION BY or ORDER BY clauses within the OVER() clause. jOOQ does not enforce those rules for two reasons:
- Your JDBC driver or database already checks SQL syntax semantics
- Not all databases behave correctly according to the SQL standard
If possible, however, jOOQ tries to render missing clauses for you, if a given SQL dialect is more restrictive.
Some examples
Here are some simple examples of window functions with jOOQ:
SELECT
-- Sample uses of ROW_NUMBER()
ROW_NUMBER() OVER (),
ROW_NUMBER() OVER (ORDER BY BOOK.ID),
ROW_NUMBER() OVER (PARTITION BY BOOK.AUTHOR_ID ORDER BY BOOK.ID),
-- Sample uses of FIRST_VALUE
FIRST_VALUE(BOOK.ID) OVER(),
FIRST_VALUE(BOOK.ID IGNORE NULLS) OVER(),
FIRST_VALUE(BOOK.ID RESPECT NULLS) OVER()
FROM BOOK
create.select(
// Sample uses of rowNumber()
rowNumber().over(),
rowNumber().over().partitionBy(BOOK.AUTHOR_ID),
rowNumber().over().partitionBy(BOOK.AUTHOR_ID).orderBy(BOOK.ID),
// Sample uses of firstValue()
firstValue(BOOK.ID).over(),
firstValue(BOOK.ID).ignoreNulls().over(),
firstValue(BOOK.ID).respectNulls().over())
.from(BOOK).fetch();
An advanced window function example
Window functions can be used for things like calculating a "running total". The following example fetches transactions and the running total for every transaction going back to the beginning of the transaction table (ordered by booked_at). Window functions are accessible from the previously seen org.jooq.AggregateFunction type using the over() method:
SELECT booked_at, amount,
SUM(amount) OVER (PARTITION BY 1
ORDER BY booked_at
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS total
FROM transactions
create.select(t.BOOKED_AT, t.AMOUNT,
sum(t.AMOUNT).over().partitionByOne()
.orderBy(t.BOOKED_AT)
.rowsBetweenUnboundedPreceding()
.andCurrentRow().as("total")
.from(TRANSACTIONS.as("t"))
.fetch();
Window functions created from ordered-set aggregate functions
In the previous chapter about aggregate functions, we have seen the concept of "ordered-set aggregate functions", such as Oracle's LISTAGG(). These functions have a window function / analytical function variant, as well. For example:
SELECT LISTAGG(TITLE, ', ')
WITHIN GROUP (ORDER BY TITLE)
OVER (PARTITION BY BOOK.AUTHOR_ID)
FROM BOOK
create.select(listAgg(BOOK.TITLE, ", ")
.withinGroupOrderBy(BOOK.TITLE)
.over().partitionBy(BOOK.AUTHOR_ID))
.from(BOOK)
.fetch();
Window functions created from Oracle's FIRST and LAST aggregate functions
In the previous chapter about aggregate functions, we have seen the concept of "FIRST and LAST aggregate functions". These functions have a window function / analytical function variant, as well. For example:
SUM(BOOK.AMOUNT_SOLD) KEEP(DENSE_RANK FIRST ORDER BY BOOK.AUTHOR_ID) OVER(PARTITION BY 1)
sum(BOOK.AMOUNT_SOLD) .keepDenseRankFirstOrderBy(BOOK.AUTHOR_ID) .over().partitionByOne();
Window functions created from user-defined aggregate functions
User-defined aggregate functions also implement org.jooq.AggregateFunction, hence they can also be transformed into window functions using over(). This is supported by Oracle in particular. See the manual's section about user-defined aggregate functions for more details.
