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.

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