# WIDTH_BUCKET

## Purpose

This scalar function, based on 2 boundaries (`bound1` and `bound2`) and the amount of equi-width buckets (`bucket_count`) between them, assigns a value (`operand`) to a corresponding bucket.

width_bucket::=

## Usage Notes

• `operand`, `bound1` and `bound2` must be numeric

• `bucket_count` must be exact numeric with scale = 0

• Result is calculated by the following rules:

1. if `bound1 < bound2`, then

• if `operand < bound1`, then 0

• if `operand` >= `bound2`, then `bucket_count + 1`

• otherwise `floor((operand - bound1) / (bound2 - bound1) * bucket_count + 1)`

2. if `bound1 > bound2`, then

• if `operand > bound1`, then 0

• if `operand <= bound2`, then `bucket_count + 1`

• otherwise return `floor((operand - bound1) / (bound2 - bound1) * bucket_count + 1)`

## Examples

``SELECT WIDTH_BUCKET(-0.1, 0, 1, 2) E1,       WIDTH_BUCKET(0, 0, 1, 2) E2,       WIDTH_BUCKET(0.49, 0, 1, 2) E3,       WIDTH_BUCKET(0.5, 0, 1, 2) E4,       WIDTH_BUCKET(0.9, 0, 1, 2) E5,       WIDTH_BUCKET(1, 0, 1, 2) E6,       WIDTH_BUCKET(1.1, 1, 0, 2) E7,       WIDTH_BUCKET(1, 1, 0, 2) E8,       WIDTH_BUCKET(0.5, 1, 0, 2) E9,       WIDTH_BUCKET(0.1, 1, 0, 2) E10,       WIDTH_BUCKET(0, 1, 0, 2) E11,       WIDTH_BUCKET(null, 0, 1, 2) E12;       E1 E2 E3 E4 E5 E6 E7 E8 E9 E10 E11 E12    -- -- -- -- -- -- -- -- -- --- --- ------ 0  1  1  2  2  3  0  1  2  2   3   (null)``
``CREATE TABLE T(val INT);INSERT INTO T VALUES (0), (1), (2), (3), (4), (4), (5), (6);SELECT val, WIDTH_BUCKET(val, 1, 5, 5) FROM T;VAL WIDTH_BUCKET(T.VAL,1,5,5) --- ------------------------- 0   0                         1   1                         2   2                         3   3                         4   4                         4   4                         5   6                         6   6``