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. Ifexpr
evaluates tonull
, then the expression returnsnull
.min_value
andmax_value
are expressions that resolve to the end points of the acceptable range forexpr
. Both of these expressions must also evaluate to numeric or datetime values, and neither can evaluate tonull
.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 is12
. - 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:
Values | Bucket |
---|---|
1, 2, 3, 4 | Bucket 1 |
5, 6, 7, 8 | Bucket 2 |
9, 10, 11, 12 | Bucket 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:
Values | Bucket |
---|---|
1, 2, 3 | Bucket 1 |
4, 5, 6 | Bucket 2 |
7, 8, 9 | Bucket 3 |
10, 11, 12 | Bucket 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