width_bucket

Description

Construct equi-width histogram by dividing up the range between min and max with num_buckets buckets, and compute an integer bucket ID for each value. The value range is left-inclusive and right-exclusive, which is usually represented as [min, max). The bucket IDs would start from 1 and end with num_buckets.

For out-of-bound values, special bucket IDs are returned: if a value is less than min, its bucket ID is 0. If a value is greater than or equal to max, its bucket ID is num_buckets + 1.

The function returns null if any input is null or when min is greater than or equal to max (the value range is empty).

Return type

int64

Domain

This is a scalar function (calculates a single output value for a single input row.)

Categories

• Numeric

Usage

width_bucket(value, min, max, num_buckets)

Argument	Type	Optional	Repeatable	Restrictions
value	numeric	no	no	none
min	numeric	no	no	none
max	numeric	no	no	none
num_buckets	int64	no	no	none

Examples

make_col bucket:width_bucket(value, 1000, 2000, 10)
statsby count(1), group_by(bucket)

Compute the histogram by assigning the value to 10 buckets ranging from 1000 to 2000.

make_col bucket:width_bucket(value, window(min(value)), window(max(value)), 10)
statsby count(1), group_by(bucket)

Compute the histogram using the dynamic range determined by the min and max values. Note that the bucket value can go from 1 to 11, because the values that match the max will be assigned to bucket 11.