As to its purpose see this paragraph in particular, and the last part of it for the concept in a nutshell, so essentially it is like a bin (case) statement but with the following differences, you might also want to look at the plsql docs on width_bucket.
Note the following differences between the BIN and WIDTH_BUCKET functions:
- Unlike the BIN function, the WIDTH_BUCKET function is not treated as a new dimensional attribute for the purposes of aggregation. Instead, the WIDTH_BUCKET function is applied on top of the query result similar to the other display functions such as RANK, TOPN, BOTTOMN, NTILE, PERCENTILE, MAVG, and MEDIAN.
- The BY clause of the BIN function defines the grain at which the binned expression is evaluated prior to binning. If the binned expression is a measure, then the measure is grouped at the grain specified in the BY clause before being binned.
- The BY clause of the WIDTH_BUCKET function defines the groups in the query result set over which the WIDTH_BUCKET calculation is applied. The buckets within different groups are calculated independently.
- The BY clause of the BIN function is mandatory if the binned expression is a measure. Otherwise, for non-measure expressions, the BY clause is optional.
- The BY clause is always optional in the WIDTH_BUCKET function. If the BY clause is omitted from the WIDTH_BUCKET function, then the function operates over the entire result set.
- Use the BIN function when you want to compute a set of discrete buckets on top of a continuous valued attribute or measure and you want to treat that new set of discrete buckets as if it were a new dimension attribute that is intended to be included in the GROUP BY clause of other base measures in the query.
- Use the WIDTH_BUCKET function when you want to compute a discrete set of buckets on top of an already aggregated query result set.
It makes sense now.
Not sure how it is useful to me yet, but you never know!!!!!