In this post, we will explain the usage of WIDTH_BUCKET analytic function in Oracle with a simple example. The function WIDTH_BUCKET may sound odd, but the example that we will provide to explain it would make sure that you crack its usage without complexities.
To understand what WIDTH_BUCKET is all about, consider a scenario where you have a set of different brands of laptops with their Price marked. Out of all the laptop models you have, you know the minimum price and maximum price. Now, you would like to define some baskets and place all your laptops in appropriate ones, based on the price. WIDTH BUCKET function does this automatically for you, all you need to do is to help that function with your requirements.
Create a simple table and insert the data into the table as shown below, in a few minutes we will explain WIDTH BUCKET.
| CREATE TABLE WIDTH_BUCKET_TEST ( PRODUCT_ID NUMBER, PROD_DESCR VARCHAR2(50), PRICE NUMBER ) INSERT INTO WIDTH_BUCKET_TEST VALUES(1,'Pavilion dv6-3079TX Notebook',1200) INSERT INTO WIDTH_BUCKET_TEST VALUES(2,'Aspire AS5820TG-524G50Mn Timeline Notebook',1150) INSERT INTO WIDTH_BUCKET_TEST VALUES(3,'Satellite A660/07R 3D Notebook',1700) INSERT INTO WIDTH_BUCKET_TEST VALUES(4,'HP Pavilion dv6-3138TX Notebook' ,900) INSERT INTO WIDTH_BUCKET_TEST VALUES(5,'Aspire AS5742 Notebook' ,900) INSERT INTO WIDTH_BUCKET_TEST VALUES(6,'HP G62-454TU Notebook' ,500) INSERT INTO WIDTH_BUCKET_TEST VALUES(7,'Presario CQ62-402AX Notebook' ,480) INSERT INTO WIDTH_BUCKET_TEST VALUES(8,'Satellite L640/05K Notebook',2000) INSERT INTO WIDTH_BUCKET_TEST VALUES(9,'Asus N61 16 Notebook',1800) INSERT INTO WIDTH_BUCKET_TEST VALUES(10,'Sony Vaio Notebook' ,1000) |
If you look at the data above, the lowest Notebook price is 500 and highest is 2000. If I specify the number of baskets (i.e. buckets) as 3, then price range [500,999.99] would be in first bucket, [1000,1499.99] would be in the second bucket and [1500,1999.99] will be in third bucket. Oracle takes the number of buckets parameter and automatically decides the size of the bucket. Anything less than 500, will be in 0th bucket (i.e. not mapped) and greater than 1999.99 will come as 4th bucket.
SELECT PRODUCT_ID, PROD_DESCR, PRICE, WIDTH_BUCKET(PRICE, 500, 2000, 3) FROM WIDTH_BUCKET_TEST | ||||||||||||||||||||||||||||||||||||||||||||||||||
|
The output is as per our explanation and we have got a total of five buckets i.e. 3 for our requirement and two by default to define the boundaries; both beyond and below. If the low value and high value are interchanged as shown below, then the output boundaries also get changed..As an example
SELECT PRODUCT_ID, PROD_DESCR, PRICE, WIDTH_BUCKET(PRICE, 2000, 500, 3) FROM WIDTH_BUCKET_TEST | ||||||||||||||||||||||||||||||||||||||||
|
A value of 1000.01 would still have taken the second bucket in the second SQL example. To check this you can insert another record and run the same SQL..the new row's value will be as shown below
| INSERT INTO WIDTH_BUCKET_TEST VALUES(10,'Sony Vaio Notebook 2' ,1000.01) |
| 10 Sony Vaio Notebook 2 1000.01 2 |
If you attempt to give a negative value in WIDTH BUCKET for the number of buckets (parameter 4), then you get the following error
ORA-30494: The argument [4] of WIDTH_BUCKET function is NULL or invalid.
This error means, you have either specified a NULL or invalid value for number of buckets parameter. Also, if you give same values for both low value (parameter 2) and high value (parameter 3), you don't end up getting any classified buckets for our case. The 480 price row gets a value of 0, and all other (including 500) gets a value of 2, for the SQL example below
| SELECT PRODUCT_ID, PROD_DESCR, PRICE, WIDTH_BUCKET(PRICE, 500, 500, 1) FROM WIDTH_BUCKET_TEST |
You also get an invalid identifier or invalid number error when the fourth parameter to this function is passed as a character etc.
No comments:
Post a Comment