WIDTH_BUCKET function [Numerical]

Function

For a given expression, the WIDTH_BUCKET function returns the bucket number that the result of this expression will be assigned after it is evaluated.

Syntax

WIDTH_BUCKETexpression, min_value, max_value, num_buckets ) 

Parameters

expression 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. If expr evaluates to null, then the expression returns null.

min_value An expression that resolves to the end points of the acceptable range for expr. Must also evaluate to numeric or datetime values and cannot evaluate to null.

max_value An expression that resolves to the end points of the acceptable range for expr. Must also evaluate to numeric or datetime values and cannot evaluate to null.

num_buckets Is an expression that resolves to a constant indicating the number of buckets. This expression must evaluate to a positive integer.

Examples

The following example creates a ten-bucket histogram on the credit_limit column for customers in Massachusetts in the sample table and returns the bucket number (“Credit Group”) for each customer. Customers with credit limits greater than the maximum value are assigned to the overflow bucket, 11:

select EmployeeID, Surname, Salary, WIDTH_BUCKET(Salary, 29000, 60000, 4) "Wages" from Employees where State = 'FL' order by "Wages"
EMPLOYEEID   SURNAME               SALARY         Wages
----------   -------               ------         -----
888          Charlton           28300.000             0
1390         Litton             58930.000             4
207          Francis            53870.000             4
266          Gowda              59840.000             4
445          Lull               87900.000             5
1021         Sterling           64900.000             5
902          Kelly              87500.000             5
1576         Evans              68940.000             5

When the bounds are reversed, the buckets are open-closed intervals. For example: WIDTH_BUCKET (credit_limit, 5000, 0, 5). In this example, bucket number 1 is (4000, 5000], bucket number 2 is (3000, 4000], and bucket number 5 is (0, 1000]. The overflow bucket is numbered 0 (5000, +infinity), and the underflow bucket is numbered 6 (-infinity, 0].

Usage

You can generate equiwidth histograms with the WIDTH_BUCKET function. Equiwidth histograms divide data sets into buckets whose interval size (highest value to lowest value) is equal. The number of rows held by each bucket will vary. A related function, NTILE, creates equiheight buckets.

Equiwidth histograms can be generated only for numeric, date or datetime data types; therefore, the first three parameters should be all numeric expressions or all date expressions. Other types of expressions are not allowed. If the first parameter is NULL, the result is NULL. If the second or the third parameter is NULL, an error message is returned, as a NULL value cannot denote any end point (or any point) for a range in a date or numeric value dimension. The last parameter (number of buckets) should be a numeric expression that evaluates to a positive integer value; 0, NULL, or a negative value will result in an error.

Buckets are numbered from 0 to (n+1). Bucket 0 holds the count of values less than the minimum. Bucket(n+1) holds the count of values greater than or equal to the maximum specified value.

Standards and compatibility

See also

“NTILE function [Analytical]”, which creates equiheight histograms.

Chapter 2, “Using OLAP” in the System Administration Guide: Volume 2