WIDTH_BUCKET scalar function

The WIDTH_BUCKET function is used to create equal-width histograms.

The schema is SYSIBM.

expression

An expression that specifies the value to be assigned into a bucket. The expression must return a value that is a built-in numeric, CHAR, or VARCHAR data type. In a Unicode database, the expression can also be a GRAPHIC or VARGRAPHIC data type. CHAR, VARCHAR, GRAPHIC, and VARGRAPHIC are supported by using implicit casting. If the data type is DECFLOAT, the value must not be a special value such as NaN or INFINITY (SQLSTATE 42815).

bound1

An expression that specifies the left end point. The expression must return a value that is a built-in numeric, CHAR, or VARCHAR data type. In a Unicode database, the expression can also be a GRAPHIC or VARGRAPHIC data type. CHAR, VARCHAR, GRAPHIC, and VARGRAPHIC are supported by using implicit casting. If the data type is DECFLOAT, the value must not be a special value such as NaN or INFINITY (SQLSTATE 42815).

bound2

An expression that specifies the right end point. The expression must return a value that is a built-in numeric, CHAR, or VARCHAR data type. In a Unicode database, the expression can also be a GRAPHIC or VARGRAPHIC data type. CHAR, VARCHAR, GRAPHIC, and VARGRAPHIC are supported by using implicit casting. If the data type is DECFLOAT, the value must not be a special value such as NaN or INFINITY (SQLSTATE 42815). bound1 must not be equal to bound2 (SQLSTATE 2201G).

num-buckets

An expression that specifies the number of buckets between bound1 and bound2. The expression must return a value that is a SMALLINT, INTEGER, BIGINT, DECIMAL, DECFLOAT, CHAR, or VARCHAR data type. In a Unicode database, the expression can also be a GRAPHIC or VARGRAPHIC data type. CHAR, VARCHAR, GRAPHIC, and VARGRAPHIC are supported by using implicit casting. If the expression is a CHAR, VARCHAR, a GRAPHIC, or VARGRAPHIC, it is cast to DECFLOAT(34) before the function is evaluated. If the value is a DECIMAL or DECFLOAT, it is truncated to zero places to the left of the decimal point. The value must be greater than 0 (SQLSTATE 2201G). If the data type is DECFLOAT, the value must not be a special value such as NaN or INFINITY (SQLSTATE 42815).

The data type of the result is based on the data type of num-buckets.

Table 1. Data type of the result
Data type of num-buckets	Data type of result
SMALLINT	SMALLINT
INTEGER	INTEGER
BIGINT	BIGINT
DECIMAL(p,s)	DECIMAL(MIN(31, p-s+1), 0)
DECFLOAT(n)	DECFLOAT(n)

This function returns the bucket number that expression falls into given bound1, bound2, and num-buckets. The range from bound1 to bound2 is divided into num-buckets buckets starting from bucket 1 to bucket num-buckets.

If any argument can be null, the result can be null. If any argument is null, the result is the null value.

Notes

If bound1 is less than bound2, each bucket is a left-closed, right-open interval on the real line. If expression is less than bound1, the result is 0, which represents an underflow bucket. If expression is greater than or equal to bound2, the result is num-buckets + 1, which represents an overflow bucket.
If bound1 is greater than bound2, each bucket is a left-closed, right-open interval on the real line. If expression is greater than bound1, the result is 0, which represents an underflow bucket. If expression is less than or equal to bound2, the result is num-buckets + 1, which represents an overflow bucket.
When num-buckets is the maximum value for the data type, an error is returned if the result is num-buckets + 1 (SQLSTATE 22003).
Several arithmetic operations are used to compute the result. If any of these arithmetic operations result in an overflow, an error is returned (SQLSTATE 22003).

Example

Using the EMPLOYEE table, assign a bucket to each employee's salary using a range of 35000 to 100000 divided into 13 buckets.

   SELECT EMPNO, SALARY, WIDTH_BUCKET(SALARY, 35000, 100000, 13)
      FROM EMPLOYEE ORDER BY EMPNO

15 buckets are assigned with the following ranges:

Bucket 0: salary < 35000
Bucket 1: 35000 <= salary < 40000
Bucket 2: 40000 <= salary < 45000
Bucket 3: 45000 <= salary < 50000
Bucket 4: 50000 <= salary < 55000
Bucket 5: 55000 <= salary < 60000
Bucket 6: 60000 <= salary < 65000
Bucket 7: 65000 <= salary < 70000
Bucket 8: 70000 <= salary < 75000
Bucket 9: 75000 <= salary < 80000
Bucket 10: 80000 <= salary < 85000
Bucket 11: 85000 <= salary < 90000
Bucket 12: 90000 <= salary < 95000
Bucket 13: 95000 <= salary < 100000
Bucket 14: salary >= 100000

The query has the following output:

EMPNO  SALARY      3          
------ ----------- -----------
000010   152750.00          14
000020    94250.00          12
000030    98250.00          13
000050    80175.00          10
000060    72250.00           8
000070    96170.00          13
000090    89750.00          11
000100    86150.00          11
000110    66500.00           7
000120    49250.00           3
000130    73800.00           8
000140    68420.00           7
000150    55280.00           5
000160    62250.00           6
000170    44680.00           2
000180    51340.00           4
000190    50450.00           4
000200    57740.00           5
000210    68270.00           7
000220    49840.00           3
000230    42180.00           2
000240    48760.00           3
000250    49180.00           3
000260    47250.00           3
000270    37380.00           1
000280    36250.00           1
000290    35340.00           1
000300    37750.00           1
000310    35900.00           1
000320    39950.00           1
000330    45370.00           3
000340    43840.00           2
200010    46500.00           3
200120    39250.00           1
200140    68420.00           7
200170    64680.00           6
200220    69840.00           7
200240    37760.00           1
200280    46250.00           3
200310    35900.00           1
200330    35370.00           1
200340    31840.00           0

  42 record(s) selected.