Available in versions: Dev (3.21) | Latest (3.20) | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11
PERCENTILE_CONT
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The PERCENTILE_CONT() aggregate function is an ordered set function that calculates a given continuous percentile of all input values. A special kind of percentile is the MEDIAN, corresponding to the 50% percentile.
SELECT percentile_cont(0.00) WITHIN GROUP (ORDER BY ID), percentile_cont(0.25) WITHIN GROUP (ORDER BY ID), percentile_cont(0.50) WITHIN GROUP (ORDER BY ID), percentile_cont(0.75) WITHIN GROUP (ORDER BY ID), percentile_cont(1.00) WITHIN GROUP (ORDER BY ID) FROM BOOK
create.select(
percentileCont(0.00).withinGroupOrderBy(BOOK.ID),
percentileCont(0.25).withinGroupOrderBy(BOOK.ID),
percentileCont(0.50).withinGroupOrderBy(BOOK.ID),
percentileCont(0.75).withinGroupOrderBy(BOOK.ID),
percentileCont(1.00).withinGroupOrderBy(BOOK.ID))
.from(BOOK)
Producing:
+------+------+------+------+------+ | 0.00 | 0.25 | 0.50 | 0.75 | 1.00 | +------+------+------+------+------+ | 1 | 1.75 | 2.5 | 3.25 | 4 | +------+------+------+------+------+
Dialect support
This example using jOOQ:
percentileCont(0.00).withinGroupOrderBy(BOOK.ID)
Translates to the following dialect specific expressions:
Aurora Postgres, Postgres, YugabyteDB
percentile_cont(CAST(0E0 AS double precision)) WITHIN GROUP (ORDER BY BOOK.ID)
BigQuery
percentile_cont(BOOK.ID, 0E0)
Databricks, DuckDB, Exasol, MariaDB, MemSQL, Oracle, Redshift, Snowflake, Teradata
percentile_cont(0E0) WITHIN GROUP (ORDER BY BOOK.ID)
DB2, H2
percentile_cont(CAST(0E0 AS double)) WITHIN GROUP (ORDER BY BOOK.ID)
SQLDataWarehouse, SQLServer
percentile_cont(CAST(0E0 AS float)) WITHIN GROUP (ORDER BY BOOK.ID)
ASE, Access, Aurora MySQL, ClickHouse, CockroachDB, Derby, Firebird, HSQLDB, Hana, Informix, MySQL, SQLite, Sybase, Trino, Vertica
/* UNSUPPORTED */
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!