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

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, CockroachDB, Oracle, Postgres, Snowflake, Teradata, Trino, YugabyteDB

width_bucket(15, 0, 100, 10)

(These are currently generated with jOOQ 3.20, see #10141), or translate your own on our website

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo