PostgreSQL WIDTH_BUCKET() Function

PostgreSQL WIDTH_BUCKET() Function

The PostgreSQL WIDTH_BUCKET() function is used to assign a value to a bucket within a specified range. This function is essential for histogram creation, data analysis, and categorizing continuous data into discrete intervals.

Syntax

WIDTH_BUCKET(value, min_value, max_value, num_buckets)

The WIDTH_BUCKET() function has the following components:

  • value: The numeric value to be assigned to a bucket.
  • min_value: The minimum value of the range.
  • max_value: The maximum value of the range.
  • num_buckets: The number of buckets.

Example PostgreSQL WIDTH_BUCKET() Queries

Let's look at some examples of PostgreSQL WIDTH_BUCKET() function queries:

1. Basic WIDTH_BUCKET() Example

SELECT WIDTH_BUCKET(5.5, 0, 10, 5) AS bucket;

This query assigns the value 5.5 to a bucket within the range of 0 to 10, divided into 5 buckets. The result is 3.

2. WIDTH_BUCKET() with Column Values

SELECT value, WIDTH_BUCKET(value, 0, 100, 10) AS bucket
FROM measurements;

This query retrieves the value and its corresponding bucket from the measurements table, dividing the range of 0 to 100 into 10 buckets.

3. WIDTH_BUCKET() with Out-of-Range Values

SELECT value, WIDTH_BUCKET(value, 0, 10, 5) AS bucket
FROM measurements
WHERE value < 0 OR value > 10;

This query retrieves the value and its corresponding bucket from the measurements table where the value is out of the specified range (0 to 10).

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the WIDTH_BUCKET() function to categorize values into buckets.

Step 1: Creating a Table

This step involves creating a new table named measurements to store numerical data.

CREATE TABLE measurements (
    id SERIAL PRIMARY KEY,
    value NUMERIC
);

In this example, we create a table named measurements with columns for id and value.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the measurements table.

INSERT INTO measurements (value)
VALUES (1.5),
       (3.2),
       (5.5),
       (8.7),
       (11.0);

Here, we insert data into the measurements table.

Step 3: Using the WIDTH_BUCKET() Function

This step involves using the WIDTH_BUCKET() function to categorize the values from the measurements table into buckets.

-- Basic WIDTH_BUCKET()
SELECT value, WIDTH_BUCKET(value, 0, 10, 5) AS bucket
FROM measurements;

-- WIDTH_BUCKET() with Out-of-Range Values
SELECT value, WIDTH_BUCKET(value, 0, 10, 5) AS bucket
FROM measurements
WHERE value < 0 OR value > 10;

These queries demonstrate how to use the WIDTH_BUCKET() function to categorize the values from the measurements table into buckets, including basic usage and handling out-of-range values.

Conclusion

The PostgreSQL WIDTH_BUCKET() function is a fundamental tool for categorizing continuous data into discrete intervals by assigning values to buckets within a specified range. Understanding how to use the WIDTH_BUCKET() function and its syntax is essential for effective data analysis and manipulation in PostgreSQL databases.