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 ...
Get SQL in a Nutshell, 3rd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.