Oracle WIDTH_BUCKET Function: A Simple Guide

The WIDTH_BUCKET function in Oracle SQL is a powerful analytical function that lets you group data into "buckets" of equal size.

It's most commonly used to build equiwidth histograms, where you define a range (from a minimum to a maximum value) and divide that range into a specific number of equal intervals (buckets). The function then tells you which bucket a particular value falls into.

What is the WIDTH_BUCKET Function in Oracle?

The WIDTH_BUCKET(expr, min_value, max_value, num_buckets) function takes an expression (like a column value), checks where it fits within your defined range (min_value to max_value), and returns the corresponding bucket number.

A key feature is its handling of values outside the specified range:

• Underflow Bucket (Bucket 0): Any value less than min_value is assigned to bucket 0.
• Overflow Bucket (Bucket num_buckets + 1): Any value greater than or equal to max_value is assigned to an overflow bucket.

WIDTH_BUCKET Function Syntax

The syntax for WIDTH_BUCKET is:

WIDTH_BUCKET(expr, min_value, max_value, num_buckets)

Let's break that down:

• expr: The value or column you want to categorize (e.g., price, age, score).
• min_value: The lowest value of the range (inclusive).
• max_value: The highest value of the range (exclusive).
• num_buckets: The total number of equal-sized buckets to create within the range.

Oracle WIDTH_BUCKET Function Examples

Here are two practical examples of how to use WIDTH_BUCKET.

Example 1: Grouping Student Scores

Let's say we have a list of student scores and we want to group them into 4 buckets (A, B, C, D) based on a "passing" range of 60 to 100.

• Range: 60 to 100
• Buckets: 4
• Bucket Size: (100 - 60) / 4 = 10 points per bucket.
  • Bucket 1: [60, 70)
  • Bucket 2: [70, 80)
  • Bucket 3: [80, 90)
  • Bucket 4: [90, 100)

Query:

WITH scores AS (
  SELECT 'Alice' AS student, 85 AS score FROM DUAL UNION ALL
  SELECT 'Bob' AS student, 55 AS score FROM DUAL UNION ALL
  SELECT 'Charlie' AS student, 72 AS score FROM DUAL UNION ALL
  SELECT 'David' AS student, 90 AS score FROM DUAL UNION ALL
  SELECT 'Eve' AS student, 105 AS score FROM DUAL
)
SELECT 
  student, 
  score,
  WIDTH_BUCKET(score, 60, 100, 4) AS "Score_Bucket"
FROM scores;

Result:

STUDENT SCORE Score_Bucket
------- ----- ------------
Alice      85            3
Bob        55            0  <-- (Bucket 0 for underflow)
Charlie    72            2
David      90            4
Eve       105            5  <-- (Bucket 5 for overflow, 4+1)

Example 2: Grouping Product Prices

This example groups product prices into 5 buckets between $0 and $500. Any price $500 or over will go into the overflow bucket (Bucket 6).

Query:

SELECT 
  'Laptop' AS product, 
  650 AS price,
  WIDTH_BUCKET(650, 0, 500, 5) AS "Price_Group"
FROM DUAL;

Result:

PRODUCT PRICE Price_Group
------- ----- -----------
Laptop    650           6