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
PARTITION BY
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();
Producing:
+----+-----------+-------+ | 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 BY clause, then the entire data set forms a single partition.
Dialect support
This example using jOOQ:
count().over(partitionBy(BOOK.AUTHOR_ID))
Translates to the following dialect specific expressions:
Aurora Postgres, BigQuery, ClickHouse, CockroachDB, DB2, DuckDB, Exasol, Firebird, H2, Hana, Informix, MariaDB, MemSQL, MySQL, Oracle, Postgres, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Sybase, Teradata, Trino, Vertica, YugabyteDB
count(*) OVER (PARTITION BY BOOK.AUTHOR_ID)
ASE, Access, Aurora MySQL, Derby, HSQLDB
/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!