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
WIDTH_BUCKET
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The WIDTH_BUCKET() function divides a numeric range into equally sized buckets and calculates which bucket number a value is in.
SELECT width_bucket(0 , 0, 100, 10), width_bucket(15, 0, 100, 10), width_bucket(99, 0, 100, 10);
create.select( widthBucket(val(0) , 0, 100, 10), widthBucket(val(15), 0, 100, 10), widthBucket(val(99), 0, 100, 10)).fetch();
The result being
+--------------+--------------+--------------+ | width_bucket | width_bucket | width_bucket | +--------------+--------------+--------------+ | 1 | 2 | 10 | +--------------+--------------+--------------+
Dialect support
This example using jOOQ:
widthBucket(val(15), 0, 100, 10)
Translates to the following dialect specific expressions:
Access
SWITCH(15 < 0, 0, 15 >= 100, (10 + 1), TRUE, ((cdec((((15 - 0) * 10) / (100 - 0))) - ((((15 - 0) * 10) / (100 - 0)) < cdec((((15 - 0) * 10) / (100 - 0))))) + 1))
ASE, Aurora MySQL, BigQuery, DB2, Derby, DuckDB, Exasol, Firebird, H2, HSQLDB, Hana, Informix, MariaDB, MemSQL, MySQL, Redshift, SQLDataWarehouse, SQLServer, SQLite, Sybase, Vertica
CASE WHEN 15 < 0 THEN 0 WHEN 15 >= 100 THEN (10 + 1) ELSE (floor((((15 - 0) * 10) / (100 - 0))) + 1) END
Aurora Postgres, ClickHouse, CockroachDB, Databricks, Oracle, Postgres, Snowflake, Teradata, Trino, YugabyteDB
width_bucket(15, 0, 100, 10)
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!