WIDTH_BUCKET

Assigns a numeric value to one of num_buckets equal-width buckets spanning a specified range and returns the bucket number. Each bucket has an inclusive lower bound and an exclusive upper bound. This is an additional function of MaxCompute V2.0.

Usage notes

Supported data types: BIGINT, INT, FLOAT, DOUBLE, DECIMAL, and DECIMAL(precision,scale) in the MaxCompute V2.0 data type edition.
Each bucket covers the interval [lower, upper): the lower bound is inclusive and the upper bound is exclusive.

Syntax

width_bucket(numeric <expr>, numeric <min_value>, numeric <max_value>, int <num_buckets>)

Parameters

Parameter	Type	Required	Description
`expr`	numeric	Yes	The numeric expression to assign to a bucket.
`min_value`	numeric	Yes	The inclusive lower bound of the bucketing range.
`max_value`	numeric	Yes	The exclusive upper bound of the bucketing range. Must be greater than `min_value`.
`num_buckets`	int	Yes	The number of equal-width buckets. Must be greater than 0. The range [`min_value`, `max_value`) is divided into `num_buckets` intervals of identical width. When `expr` falls outside the range, the function returns `0` if `expr` is less than `min_value`, or `num_buckets + 1` if `expr` is greater than or equal to `max_value`.

Return value

Returns a BIGINT value in the range [0, num_buckets + 1].

Condition	Return value
`expr < min_value`	`0`
`expr >= max_value`	`num_buckets + 1`
`expr` is NULL	NULL
Any of `min_value`, `max_value`, or `num_buckets` is NULL	NULL
Otherwise	`floor(num_buckets × (expr - min_value) / (max_value - min_value) + 1)`

Examples

Example 1: All parameters non-null

The following example divides the range [100, 500) into 5 equal-width buckets of 80 units each and assigns each row to a bucket based on the value column.

select key, value, width_bucket(value, 100, 500, 5) as value_group
from values
    (1, 99),
    (2, 100),
    (3, 199),
    (4, 200),
    (5, 499),
    (6, 500),
    (7, 501),
    (8, NULL)
as t(key, value);

Result:

+------+-------+-------------+
| key  | value | value_group |
+------+-------+-------------+
| 1    | 99    | 0           |
| 2    | 100   | 1           |
| 3    | 199   | 2           |
| 4    | 200   | 2           |
| 5    | 499   | 5           |
| 6    | 500   | 6           |
| 7    | 501   | 6           |
| 8    | NULL  | NULL        |
+------+-------+-------------+

Key observations:

Value	Condition	Bucket	Reason
99	Below range	0	Less than `min_value` (100)
100	At lower bound	1	`min_value` is inclusive
500	At upper bound	6 (`num_buckets + 1`)	`max_value` is exclusive
501	Above range	6 (`num_buckets + 1`)	Greater than `max_value`
NULL	Null input	NULL	NULL propagates

Example 2: A parameter is null

When any parameter (min_value, max_value, or num_buckets) is NULL, all rows return NULL regardless of expr.

select key, value, width_bucket(value, 100, 500, null) as value_group
from values
    (1, 99),
    (2, 100),
    (3, 199),
    (4, 200),
    (5, 499),
    (6, 500),
    (7, 501),
    (8, NULL)
as t(key, value);

Result:

+------+-------+-------------+
| key  | value | value_group |
+------+-------+-------------+
| 1    | 99    | NULL        |
| 2    | 100   | NULL        |
| 3    | 199   | NULL        |
| 4    | 200   | NULL        |
| 5    | 499   | NULL        |
| 6    | 500   | NULL        |
| 7    | 501   | NULL        |
| 8    | NULL  | NULL        |
+------+-------+-------------+

Example 3: Business scenario — price range grouping

The following example groups product prices into 4 equal-width buckets spanning 100 to 500 (for example, grouping items priced at 100–200, 200–300, 300–400, and 400–500).

select product_id, price, width_bucket(price, 100, 500, 4) as price_group
from values
    (1, 150.00),
    (2, 250.00),
    (3, 350.00),
    (4, 450.00),
    (5, 99.00),
    (6, 500.00)
as t(product_id, price);

Result:

+------------+--------+-------------+
| product_id | price  | price_group |
+------------+--------+-------------+
| 1          | 150.00 | 1           |
| 2          | 250.00 | 2           |
| 3          | 350.00 | 3           |
| 4          | 450.00 | 4           |
| 5          | 99.00  | 0           |
| 6          | 500.00 | 5           |
+------------+--------+-------------+

Related functions

WIDTH_BUCKET is a mathematical function. For more information about functions related to data computing and conversion, see Mathematical functions.