Purpose
This function creates equal-width intervals based on the input parameters and returns the interval number in which expr falls. That is, it constructs an equal-width histogram, where the histogram range is divided into intervals of the same size.
Syntax
WIDTH_BUCKET
(expr, min_value, max_value, num_buckets)
Parameters
Parameter |
Description |
|---|---|
| expr | The expression to create equal-width intervals. It 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 endpoint of the range in which expr falls. It must be of the same data type as expr and cannot be NULL. |
| max_value | The right endpoint of the range in which expr falls. It must be of the same data type as expr and cannot be NULL. |
| num_buckets | The number of intervals between min_value and max_value. The result of this expression must be a positive integer. |
Note
- When
min_value<max_value:- If
expr<min_value, the function returns0. - If
expr>=min_value, the function returnsnum_bucketsplus1.
- If
- When
min_value>max_value:- If
expr>min_value, the function returns0. - If
expr<=min_value, the function returnsnum_bucketsplus1.
- If
Return type
NUMERIC
Examples
Create a table tbl1 and insert test data. Set the range to 1 to 100 and divide it into 10 intervals. Return the interval number in which 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