width_bucket function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Returns the bucket number for a value in an equi-width histogram.

Syntax 

width_bucket(expr, minExpr, maxExpr, numBuckets)

Arguments

  • expr: An numeric or interval expression to be bucketed.

  • minExpr: A numeric or interval expression providing a lower bound for the buckets.

  • maxExpr: A numeric or interval expression providing an upper bound for the buckets.

  • numBuckets: An INTEGER expression greater than 0 specifying the number of buckets.

If expr is numeric, minExpr and maxExpr must also be numeric.

If expr is an interval, minExpr and maxExpr must be of the same interval type.

Interval is supported since: Databricks Runtime 10.1

Returns

An INTEGER.

The function divides the range between minExpr and maxExpr into numBuckets slices of equal size. The result is the slice into which expr falls.

If expr is outside of minExpr the result is 0.

If expr is outside of maxExpr the result is numbuckets + 1

minExpr can be greater than maxExpr.

Examples 

> SELECT width_bucket(5.3, 0.2, 10.6, 5);
 3
> SELECT width_bucket(-2.1, 1.3, 3.4, 3);
 0
> SELECT width_bucket(8.1, 0.0, 5.7, 4);
 5
> SELECT width_bucket(-0.9, 5.2, 0.5, 2);
 3

> SELECT width_bucket(INTERVAL '1' DAY, INTERVAL '0' DAY, INTERVAL '10' DAY, 11);
2