This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Just like the GROUP BY clause, the
PARTITION BY clause divides the data set into mutually exclusive groups / partitions. This is best illustrated by example, where we fetch all the books and the total number of books per author of a book:
SELECT BOOK.ID, BOOK.AUTHOR_ID, count(*) OVER (PARTITION BY BOOK.AUTHOR_ID) FROM BOOK
create.select( BOOK.ID, BOOK.AUTHOR_ID, count().over(partitionBy(BOOK.AUTHOR_ID))) .from(BOOK) .fetch();
+----+-----------+-------+ | id | author_id | count | +----+-----------+-------+ | 1 | 1 | 2 | | 2 | 1 | 2 | | 3 | 2 | 2 | | 4 | 2 | 2 | +----+-----------+-------+
Just like ordinary aggregate functions that aggregate all data per
GROUP produced by the GROUP BY clause, the
PARTITION BY clause allows for grouping data, but without transforming the entire result set. As such, this kind of grouping can be much more convenient, especially in reporting, as the original data set isn't modified, only a new column is added.
If you omit the
PARTITION BYclause, then the entire data set forms a single partition.
This example using jOOQ:
Translates to the following dialect specific expressions:
-- AURORA_POSTGRES, BIGQUERY, COCKROACHDB, DB2, DUCKDB, EXASOL, FIREBIRD, H2, HANA, INFORMIX, MARIADB, MEMSQL, MYSQL, -- ORACLE, POSTGRES, REDSHIFT, SNOWFLAKE, SQLDATAWAREHOUSE, SQLITE, SQLSERVER, SYBASE, TERADATA, TRINO, VERTICA, YUGABYTEDB count(*) OVER (PARTITION BY BOOK.AUTHOR_ID) -- ACCESS, ASE, AURORA_MYSQL, DERBY, HSQLDB /* UNSUPPORTED */