WIDTH_BUCKET 101

Had an issue the other day where we needed to display counts per year range. The output needed to be something like:

[0-1] x dollars
[1-2] y dollars
[2-3] z dollars

The existing logic made redundant SQL calls to determine the min and max year vals. Very inefficient. Very unneccessary.

I immediately thought of using WIDTH_BUCKET. WIDTH_BUCKET is a numeric function that first appeared in Oracle 9i over ten years ago.

By definition, the Oracle WIDTH_BUCKET function is used to construct equiwidth histograms, in which the histogram range is divided into intervals that have identical size. For a given expression, the WIDTH_BUCKET function returns the bucket number into which the value of this expression would fall after being evaluated.

It's actually quite simple. Here's the syntax:

WIDTH_BUCKET(value, min_value, max_value, number_of_buckets);

I know what you're thinking, what if you have values that are below min_value or above max_value ?? Well, there's a convenient underflow and overflow bucket created automatically referenced by 0 and number_of_buckets +1, respectively.

Our journey continues...