SQL in a Nutshell, 3rd Edition

Name

WIDTH_BUCKET

The WIDTH_BUCKET function assigns values to buckets (individual segments) in an equiwidth histogram.

ANSI SQL Standard Syntax

In the following syntax, expression represents a value to be assigned to a bucket. You would typically base expression on one or more columns returned by a query:

WIDTH_BUCKET( expression, min, max, buckets)

The buckets argument specifies the number of buckets to create over the range defined by min through max. min is inclusive, whereas max is not. The value from expression is assigned to one of those buckets, and the function then returns the corresponding bucket number. When expression falls outside the range of buckets, the function returns either 0 or max + 1, depending on whether expression is lower than min or greater than or equal to max.

MySQL and SQL Server

MySQL and SQL Server do not support WIDTH_BUCKET.

Oracle and PostgreSQL

Oracle and PostgreSQL support the ANSI SQL syntax for WIDTH_BUCKET.

Examples

The following example divides the integer values 1 through 10 into two buckets:

SELECT x, WIDTH_BUCKET(x,1,10,2)
FROM pivot;
         X WIDTH_BUCKET(X,1,10,2)
---------- ----------------------
         1                      1
         2                      1
         3                      1
         4                      1
         5                      1
         6                      2
         7                      2
         8                      2
         9                      2
        10                      3

This next example is more interesting. It divides 11 values (from 1 through 10) into three buckets and illustrates the distinction between min being inclusive and max being noninclusive:

SELECT x, WIDTH_BUCKET(x,1,10,3)
FROM pivot; X WIDTH_BUCKET(X,1,10,3) ---------- ---------------------- 1 1 2 1 3 1 4 2 5 2 6 2 7 3 8 3 ...