WIDTH_BUCKET

Assign a value to a bucket in an equi-width histogram spanning a given range.

Category: numericReturns: INTEGERDialect: PostgreSql

Syntax

WIDTH_BUCKET(value, low, high, count)

Description

## Overview Returns the bucket number to which the input value belongs in a histogram with the specified number of equal-width buckets spanning [low, high). Bucket numbers start at 1 and run to count. Values below low return 0 (underflow), and values at or above high return count + 1 (overflow). This mirrors the SQL standard WIDTH_BUCKET definition and is the fastest way to construct an equi-width histogram directly in SQL. WIDTH_BUCKET is ideal when the range of values is known in advance. For data-driven bucketing where the bucket boundaries should be determined from percentiles or quantiles, combine with NTILE or QUANTILE aggregates instead. ## Behavior - Accepts any numeric type for value, low, and high. count must be a positive INTEGER. - Returns an INTEGER in the range [0, count + 1]. - Returns NULL if any argument is NULL. - The low bound is inclusive, the high bound is exclusive. - Underflow (value < low) returns 0. - Overflow (value >= high) returns count + 1. - Raises an error when count <= 0 or when low >= high. ## Numeric precision - Internally computes FLOOR(count * (value - low) / (high - low)) + 1 and clamps to [0, count + 1]. - For DOUBLE inputs, the division and FLOOR steps are subject to standard floating-point rounding. A value exactly equal to a bucket boundary can land in either of the two neighboring buckets depending on rounding. - For DECIMAL inputs, the computation is exact until the FLOOR step. ## Compatibility - Conforms to the SQL standard definition of WIDTH_BUCKET. - Matches PostgreSQL semantics for underflow and overflow handling.

Parameters

Name	Type	Description
`value`		Specifies the value to assign to a bucket. Accepts INTEGER, BIGINT, DECIMAL, or DOUBLE types.
`low`		Specifies the lower bound of the histogram range (inclusive). Must be strictly less than high.
`high`		Specifies the upper bound of the histogram range (exclusive). Must be strictly greater than low.
`count`		Specifies the number of equal-width buckets into which the range [low, high) is divided. Must be a positive integer.

Examples

-- Value in the middle of the range
SELECT WIDTH_BUCKET(15, 0, 100, 10);  -- 2

-- Value below the range returns 0 (underflow bucket)
SELECT WIDTH_BUCKET(-5, 0, 100, 10);  -- 0

-- Value at or above the upper bound returns count + 1 (overflow bucket)
SELECT WIDTH_BUCKET(105, 0, 100, 10);  -- 11

-- Value at the lower bound lands in bucket 1
SELECT WIDTH_BUCKET(0, 0, 100, 10);  -- 1

-- Histogram bucketing of column values
SELECT val, WIDTH_BUCKET(val, 0, 100, 5) AS bucket
FROM (VALUES (10), (30), (55), (80), (99)) AS t(val);

-- NULL propagation
SELECT WIDTH_BUCKET(NULL, 0, 100, 10);  -- NULL

Pitfalls

Bucket numbering starts at 1, not 0. Code that uses the result as a 0-based array index will off-by-one.
The high bound is exclusive. Values exactly equal to high fall into the overflow bucket (count + 1), not bucket count. Add a small epsilon to high if you need inclusive upper bound semantics.
low must be strictly less than high. Swapping them or passing equal bounds raises an error; WIDTH_BUCKET does not reverse the range silently.
For DOUBLE inputs, a value at a bucket boundary can land in either adjacent bucket due to rounding. Use DECIMAL when exact boundary assignment matters.
WIDTH_BUCKET assumes a linear scale. For log-scaled bucketing, apply LN() or LOG10() to the input first, then bucket the log-scaled value.