Purpose
You can call this function to create equal-width buckets based on the given value and return the bucket number into which the value of expr falls. Equal-width buckets are equiwidth 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.- When
min_value>max_value:
- The function returns
0ifexpr>min_value.- The function returns
num_buckets+1ifexpr<=min_value.
Return type
The return type is NUMERIC.
Examples
The following example creates table tbl1 and inserts test data into the table. It sets a range from 1 to 100, divides it into 10 intervals, and returns 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