WIDTH_BUCKET

​

Syntax

​

Scalar

width_bucket(value, lower, upper, buckets)

​

Arguments

​

Returns

• Type: INT
• Possible values: 0 before the first bound, 1 through buckets inside the range, or buckets + 1 beyond the final bound.
• NULL behavior: Returns NULL for NULL arguments, nonpositive bucket count, equal bounds, non-finite numeric values/bounds, or a zero/non-finite calculated bucket width.

​

Properties

• Approximation bound: No fixed maximum error is guaranteed.

​

Usage notes

• Ascending ranges include the lower bound and exclude the upper bound. Descending ranges reverse that direction.
• Numeric calculations use DOUBLE. Interval calculations can also use floating-point ratios.
• For month/day/nanosecond intervals, bounds are compared lexicographically by months, days, then nanoseconds. If bound days and nanoseconds match, only the month components are bucketed. If bound months match, only relative days and nanoseconds are bucketed; the value month component is ignored in that branch. Other mixed-component bounds return NULL.
• Year-month intervals use their month counts; day-time intervals are converted to a duration. Floating-point conversion can lose precision for large interval magnitudes.
• INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.
• Exact result: Floating-point computation can round the mathematical result.

​

Examples

​

Value and NULL input

SELECT width_bucket(CAST(5 AS DOUBLE), CAST(0 AS DOUBLE), CAST(10 AS DOUBLE), 5) AS inside, width_bucket(CAST(-1 AS DOUBLE), CAST(0 AS DOUBLE), CAST(10 AS DOUBLE), 5) AS below, width_bucket(CAST(10 AS DOUBLE), CAST(0 AS DOUBLE), CAST(10 AS DOUBLE), 5) AS above;

​

Bucket boundaries

SELECT width_bucket(0, 0, 10, 5) AS first_bucket, width_bucket(2, 0, 10, 5) AS next_bucket, width_bucket(10, 0, 10, 5) AS above_range;

​

Limitations

• Keep buckets at most 2147483646 so the extra bucket (buckets + 1) fits in INT. Larger counts can return incorrect results or overflow; an error is not guaranteed.