WIDTH_BUCKET( ) Function in Oracle

WIDTH_BUCKET( ) is  a mathematical function used in Oracle and PostgreSQL. As the name suggests width_bucket means dividing the histogram into equal width buckets, that is each bucket has an equal size/interval. This function takes four inputs that are the expression,the lower bound, the upper bound of expression, and the number of groups in which we want to divide the histogram. So after processing the expression the bucket number is returned which contains the value required. Thus using the width_bucket function we get a histogram of equal width but can vary in height.

The width_bucket function is a very useful grouping function.

Syntax:

WIDTH_BUCKET(expression, hist_min_value,
hist_max_value, num_buckets)

Parameters:

• expression: An numeric expression to be grouped in various intervals. This expression must evaluate a numeric or date-time value or to a value that can be implicitly converted to a numeric or date-time value. The value must be within the range of -(2^53 – 1) to 2^53 – 1 (inclusive).
• hist_min_value: A numeric or date-time value expression that provides the lower bound/min value of the bucket.
• hist_max_value: A numeric or date-time value expression that provides the upper bound/max value of the bucket.
• num_buckets: An INTEGER expression greater than 0 specifies the number of buckets in which we want to divide our expression.

NOTE: MySQL and SQL Server do not support WIDTH_BUCKET but Oracle and PostgreSQL support the ANSI SQL syntax for WIDTH_BUCKET.

Now take an example, to create a four-bucked histogram on the employee salary column having salaries in the range of 10,000 and 1,00,000 rupees. The function returns the bucket number as – SALES GROUP for each value in the set.

Step 1: We create a Table. We have created the table which consists of 3 columns – employee id, employee name, and employee salary . For this, we use the below command to create a Table named GeeksforGeeks_demo. 

Query:

create table geeksforgeeks_demo(employee_id number,
employee_name varchar(20),employee_salary number);

Output:

Step 2:  This is the query for Inserting rows into the Table.

Query:

insert into GeeksforGeeks_demo values(1, 'Chandler', 75000);
insert into GeeksforGeeks_demo values(2, 'Erica', 12500);
insert into GeeksforGeeks_demo values(3, 'Ron', 71000);
insert into GeeksforGeeks_demo values(4, 'Lucy', 52000);
insert into GeeksforGeeks_demo values(5, 'Adam', 22000);
insert into GeeksforGeeks_demo values(6, 'Steve', 48500);
insert into GeeksforGeeks_demo values(7, 'Monica', 84800);
insert into GeeksforGeeks_demo values(8, 'Rachel', 65000);
insert into GeeksforGeeks_demo values(9, 'Joey', 91500);
insert into GeeksforGeeks_demo values(10, 'Phoebe', 36000);
insert into GeeksforGeeks_demo values(11, 'Mike', 18000);
insert into GeeksforGeeks_demo values(12, 'Liam', 46000);

Output:

Step 3: Viewing the inserted data

Query:

select * from GeeksforGeeks_demo;

Output:

Step 4: Query the table, calling WIDTH_BUCKET( ):

Query:

select 
      employee_id,
      employee_name, 
      employee_salary,  
      width_bucket(employee_salary,
       10000, 100000, 4) salary_group
from GeeksforGeeks_demo
order by salary_group;

Output: