WIDTH_BUCKET() Function in Oracle

by

In Oracle, the WIDTH_BUCKET() function allows you to construct equiwidth histograms, in which the histogram range is divided into intervals that have identical size.

For a given expression, WIDTH_BUCKET() returns the bucket number into which the value of this expression would fall after being evaluated.

Syntax

The syntax goes like this:

WIDTH_BUCKET(expr, min_value, max_value, num_buckets)

Where:

  • expr is the expression for which the histogram is being created. This expression must evaluate to a numeric or datetime value or to a value that can be implicitly converted to a numeric or datetime value. If expr evaluates to null, then the expression returns null.
  • min_value and max_value are expressions that resolve to the end points of the acceptable range for expr. Both of these expressions must also evaluate to numeric or datetime values, and neither can evaluate to null.
  • num_buckets is an expression that resolves to a constant indicating the number of buckets. This expression must evaluate to a positive integer.

Example

Here’s an example to demonstrate how it works.

SELECT 
  WIDTH_BUCKET(3, 1, 12, 3) AS r1,
  WIDTH_BUCKET(5, 1, 12, 3) AS r2,
  WIDTH_BUCKET(9, 1, 12, 3) AS r3
FROM DUAL;

Result:

   R1    R2    R3 
_____ _____ _____ 
    1     2     3

Here’s an explanation. Let’s examine each argument, starting from the last one and working backwards to the first one.

  • Fourth argument: I specify three buckets. I do this by using 3 as the fourth argument.
  • Second and third arguments: I specify that the range is between 1 and 12. In this case, my second argument is 1 and third argument is 12.
  • First argument: This value is compared against the second and third arguments, so as to know which of the three buckets it should be assigned to. In my example I call WIDTH_BUCKET() three times in order to illustrate the concept better. I do this so that I can provide three different values as the first argument, each of which is assigned to a different bucket.

The following table provides another way of visualising this:

ValuesBucket
1, 2, 3, 4Bucket 1
5, 6, 7, 8Bucket 2
9, 10, 11, 12Bucket 3

So we can see that the first bucket accepts values from between 1 and 4, the second bucket between 5 and 8, and the third bucket is for values between 9 and 12.

If I were to change it so that there were four buckets, my code might look something like this:

SELECT 
  WIDTH_BUCKET(3, 1, 12, 4) AS r1,
  WIDTH_BUCKET(5, 1, 12, 4) AS r2,
  WIDTH_BUCKET(9, 1, 12, 4) AS r3
FROM DUAL;

And the table would look like this:

ValuesBucket
1, 2, 3Bucket 1
4, 5, 6Bucket 2
7, 8, 9Bucket 3
10, 11, 12Bucket 4

Here’s another example in which only the bucket sizes change:

SELECT 
  WIDTH_BUCKET(5, 1, 12, 3) AS r1,
  WIDTH_BUCKET(5, 1, 12, 6) AS r2,
  WIDTH_BUCKET(5, 1, 12, 10) AS r3
FROM DUAL;

Result:

   R1    R2    R3 
_____ _____ _____ 
    2     3     4

Out of Range

If the input is outside the range of the bucket, you’ll get either 0 or num_buckets+1, depending on whether the input is below the range or above it. In such cases, Oracle Database creates an underflow bucket numbered 0 and an overflow bucket numbered num_buckets+1.

Example:

SELECT 
  WIDTH_BUCKET(-3, 1, 12, 3),
  WIDTH_BUCKET(20, 1, 12, 3)
FROM DUAL;

Result:

   WIDTH_BUCKET(-3,1,12,3)    WIDTH_BUCKET(20,1,12,3) 
__________________________ __________________________ 
                         0                          4