Purpose
WIDTH_BUCKET() creates equal-width buckets based on the given value and returns the bucket number into which the value of expr falls. Equal-width buckets are equal-width histograms, in which the histogram range is divided into intervals that have an identical size.
Syntax
WIDTH_BUCKET
(expr, min_value, max_value, num_buckets)
Parameters
| Parameter | Description |
|---|---|
| expr | The expression for which the histogram is created. This expression must be a numeric or datetime value or a value that can be implicitly converted to a numeric or datetime value. |
| min_value | The left end point of the acceptable range for expr. It is of the same data type as expr and cannot be NULL. |
| max_value | The right-end point of the acceptable range for expr. It is of the same data type as expr and cannot be NULL. |
| num_buckets | The number of buckets in the range of min_value to max_value. This expression must evaluate to a positive integer. |
Note
- When
min_value<max_value:- The function returns
0ifexpr<min_value. - The function returns
num_buckets+1ifexpr>=min_value.
- The function returns
- When
min_value>max_value:- The function returns
0ifexpr>min_value. - The function returns
num_buckets+1ifexpr<=min_value.
- The function returns
Return type
The return type is NUMERIC.
Examples
Create the tbl1 table and insert test data into the table. Set the range to 1 to 100, divide the range into 10 intervals, and return the bucket number into which the value of col2 falls.
obclient> CREATE TABLE tbl1 (col1 INT,col2 NUMBER(10,2));
Query OK, 0 rows affected
obclient> INSERT INTO tbl1 VALUES(1,0.5),(2,1),(3,9),(4,10),(5,11),(6,55),(7,100),(8,101),(9,'');
Query OK, 9 rows affected
Records: 9 Duplicates: 0 Warnings: 0
obclient> SELECT col1,col2,WIDTH_BUCKET(col2,1,100,10) FROM tbl1;
+------+------+-----------------------------+
| COL1 | COL2 | WIDTH_BUCKET(COL2,1,100,10) |
+------+------+-----------------------------+
| 1 | .5 | 0 |
| 2 | 1 | 1 |
| 3 | 9 | 1 |
| 4 | 10 | 1 |
| 5 | 11 | 2 |
| 6 | 55 | 6 |
| 7 | 100 | 11 |
| 8 | 101 | 11 |
| 9 | NULL | NULL |
+------+------+-----------------------------+
9 rows in set