Functions and aggregate operators

This version of the manual is outdated. For the latest version, follow this link: http://www.jooq.org/doc/3.0/manual.

# Supporting for vendor-specific functions

jOOQ allows you to access native functions from your RDBMS. jOOQ follows two strategies:

• Implement all SQL-92, SQL:1999, SQL:2003, and SQL:2008 standard functions, aggregate operators, and window functions. Standard functions could be these functions as listed by O'Reilly.
• Take the most useful of vendor-specific functions and simulate them for other RDBMS, where they may not be supported. An example for this are Oracle Analytic Functions

# Functions

These are just a few functions in the Factory, so you get the idea:

Field<String> rpad(Field<String> field, Field<? extends Number> length);
Field<String> rpad(Field<String> field, int length);
Field<String> rpad(Field<String> field, Field<? extends Number> length, Field<String> c);
Field<String> rpad(Field<String> field, int length, char c);
Field<String> lpad(Field<String> field, Field<? extends Number> length);
Field<String> lpad(Field<String> field, int length);
Field<String> lpad(Field<String> field, Field<? extends Number> length, Field<String> c);
Field<String> lpad(Field<String> field, int length, char c);
Field<String> replace(Field<String> field, Field<String> search);
Field<String> replace(Field<String> field, String search);
Field<String> replace(Field<String> field, Field<String> search, Field<String> replace);
Field<String> replace(Field<String> field, String search, String replace);
Field<Integer> position(Field<String> field, String search);
Field<Integer> position(Field<String> field, Field<String> search);

# Aggregate functions

Aggregate functions work just like functions, even if they have a slightly different semantics. Here are some examples from Factory:

// Every-day, SQL standard aggregate functions
AggregateFunction<Integer> count(Field<?> field);
AggregateFunction<T> max(Field<T> field);
AggregateFunction<T> min(Field<T> field);
AggregateFunction<BigDecimal> sum(Field<? extends Number> field);
AggregateFunction<BigDecimal> avg(Field<? extends Number> field);


// DISTINCT keyword in aggregate functions
AggregateFunction<Integer> countDistinct(Field<?> field);
AggregateFunction<T> maxDistinct(Field<T> field);
AggregateFunction<T> minDistinct(Field<T> field);
AggregateFunction<BigDecimal> sumDistinct(Field<? extends Number> field);
AggregateFunction<BigDecimal> avgDistinct(Field<? extends Number> field);

// String aggregate functions
AggregateFunction<String> groupConcat(Field<?> field);
AggregateFunction<String> groupConcatDistinct(Field<?> field);
OrderedAggregateFunction<String> listAgg(Field<?> field);
OrderedAggregateFunction<String> listAgg(Field<?> field, String separator);

// Statistical functions
AggregateFunction<BigDecimal> median(Field<? extends Number> field);
AggregateFunction<BigDecimal> stddevPop(Field<? extends Number> field);
AggregateFunction<BigDecimal> stddevSamp(Field<? extends Number> field);
AggregateFunction<BigDecimal> varPop(Field<? extends Number> field);
AggregateFunction<BigDecimal> varSamp(Field<? extends Number> field);

A typical example of how to use an aggregate operator is when generating the next key on insertion of an ID. When you want to achieve something like this

SELECT MAX(ID) + 1 AS next_id
  FROM T_AUTHOR

create.select(max(ID).add(1).as("next_id"))
      .from(T_AUTHOR);

See also the section about Arithmetic operations

# Ordered aggregate functions

Oracle and some other databases support ordered aggregate functions. This means you can provide an ORDER BY clause to an aggregate function, which will be taken into consideration when aggregating. The best example for this is LISTAGG() (also known as GROUP_CONCAT in other dialects). The following query groups by authors and concatenates their books' titles

SELECT   LISTAGG(TITLE, ', ')
         WITHIN GROUP (ORDER BY TITLE)
FROM     BOOK
GROUP BY AUTHOR_ID

create.select(listAgg(BOOK.TITLE, ", ")
      .withinGroupOrderBy(BOOK.TITLE))
      .from(BOOK)
      .groupBy(BOOK.AUTHOR_ID)

# Window functions

Most major RDBMS support the concept of window functions. jOOQ knows of implementations in DB2, Oracle, Postgres, SQL Server, and Sybase SQL Anywhere, and supports most of their specific syntaxes. 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). They are accessible from the previously seen 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"));