New versions: Dev (3.14) | Latest (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, CUBRID, DB2, DERBY, FIREBIRD, H2, HANA, HSQLDB, INFORMIX, INGRES, MARIADB, MEMSQL, MYSQL, REDSHIFT, 
-- SQLDATAWAREHOUSE, SQLSERVER, 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
WIDTH_BUCKET(15, 0, 100, 10)

-- SQLITE
CASE WHEN 15 < 0 THEN 0 WHEN 15 >= 100 THEN (10 + 1) ELSE ((CAST((((15 - 0) * 10) / (100 - 0)) AS int8) - ((((15 - 0) * 10) / (100 - 0)) < CAST((((15 - 0) * 10) / (100 - 0)) AS int8))) + 1) END

-- TERADATA
WIDTH BUCKET(15, 0, 100, 10)

(These are currently generated with jOOQ 3.14, see #10141)

The jOOQ Logo