Oracle WIDTH_BUCKET() Function

Oracle WIDTH_BUCKET() is a built-in function that returns the number of a bucket which the specified value falls in.

Oracle WIDTH_BUCKET() syntax

Here is the syntax for the Oracle WIDTH_BUCKET() function:

WIDTH_BUCKET(operand, low, high, count)

Parameters

operand

Required. operand.

low

Required. The minimum bound (inclusive) of all buckets.

high

Required. The maximum bounds for all buckets (exclusive).

count

Required. How many buckets you want.

All parameters must be empty NULL, or Oracle will report an error.

Return Value

The Oracle WIDTH_BUCKET() function returns the position of a specified value in some specified bucket.

The Oracle WIDTH_BUCKET() function splits the interval consisting of low and high boundaries into count equal-width buckets, and numbers each bucket, starting from 1:

  • If the operand is in a bucket, return the bucket number.
  • If the operand is less than the minimum bound low, the function will return 0.
  • If the operand is greater than or equal to the maximum bound high, the function will return count + 1.

If any parameter is NULL, WIDTH_BUCKET() will return NULL.

Oracle WIDTH_BUCKET() Examples

The following example demonstrates basic usage of the WIDTH_BUCKET() function.

SELECT
    WIDTH_BUCKET(6, 2, 8, 3)
FROM dual;

   WIDTH_BUCKET(6,2,8,3)
________________________
                       3

Here, we see that WIDTH_BUCKET(6, 2, 8, 3)returned 3. Its calculation steps are as follows:

  1. First, divide the range [2, 8) into 3 buckets with equal width:

    • [2, 4)- bucket 1
    • [4, 6)- barrel 2
    • [6, 8)- barrel 3

    Note that the low boundary here (2) is included in the bucket, while the high boundary (8) is not included in the bucket.

  2. Second, compute which bucket 6 falls in. Obviously, 6 falls in [6, 8).

  3. Finally, WIDTH_BUCKET(6, 2, 8, 3) returns 3.

We can replace some other values ​​to verify that if the above calculation is correct, for example:

SELECT
    WIDTH_BUCKET(3, 2, 8, 3),
    WIDTH_BUCKET(5, 2, 8, 3)
FROM dual;

   WIDTH_BUCKET(3,2,8,3)    WIDTH_BUCKET(5,2,8,3)
________________________ ________________________
                       1                        2

Here, the 3 operand falls in the first bucket [2, 4) and the 5 operand falls in the second bucket [4, 6).

We can also provide a value smaller than the low bound, like:

SELECT
    WIDTH_BUCKET(1, 2, 8, 3)
FROM dual;

   WIDTH_BUCKET(1,2,8,3)
________________________
                       0

Here, since the 1 operand is smaller than the lower bound 2, it does not belong to any of the buckets, so WIDTH_BUCKET(1, 2, 8, 3) returns 0.

We can also provide a value greater than the high bound, like:

SELECT
    WIDTH_BUCKET(8, 2, 8, 3),
    WIDTH_BUCKET(9, 2, 8, 3)
FROM dual;

   WIDTH_BUCKET(8,2,8,3)    WIDTH_BUCKET(9,2,8,3)
________________________ ________________________
                       4                        4

Here, it returns 4(the number of buckets plus 1) because the operand 8 is greater than or equal to the high bound and it does not fall in any of the buckets.

Conclusion

Oracle WIDTH_BUCKET() is a built-in function that returns the position of a specified value in some specified bucket.