CASE and Width_Bucket Functions

Case Function
A flexible method of grouping data into even or unevenly sized buckets.
Very similar to DECODE

Syntax:

CASE WHEN <cond1> THEN <Value1>
     WHEN <cond2> THEN <Value2>
     [ELSE Value n ] END   

A single CASE statement can be selected (along with other columns), resulting in a vertical list of data buckets. Alternatively several case statements can be summed to display totals in a horizontal row:

 SELECT CASE WHEN sal>1000 THEN 'Over paid'
             ELSE 'Under paid' END
 FROM   emp;


 SELECT SUM(CASE WHEN SUM(amount) BETWEEN 0 AND 49 THEN 1 
            ELSE 0 END) AS "0-49",
        SUM(CASE WHEN SUM(amount) BETWEEN 50 AND 100
           THEN 1 ELSE 0 END) AS "50-100"
 FROM sales;
 

WIDTH_BUCKET Function

Divide a data set into buckets with an equal interval size.
e.g. Age = 0-20, 20-40, 40-60, 60-80...
This is known as an 'equiwidth histogram'.

Syntax:

WIDTH_BUCKET(column/expr, low boundary, high_boundary,
              bucket_count)

If you ask for (n) buckets you actually get (n+2) buckets
The extra 2 being for values above and below the high/low boundaries.

e.g.
SELECT last_name, salary,
       WIDTH_BUCKET(salary,3000,9000,3)

Will create 5 buckets:
Up_to_3000, 3000-5000, 5000-7000, 7000-9000, 9000+

When using WIDTH_BUCKET pay attention to the boundary values, each bucket will contain values equal to or greater than the lowest boundary of that bucket, so age ranges 0-20, 20-40... would actually be 0-19.99 and 20-39.999...

"Our team is well balanced. We have problems everywhere." - Tommy Prothro

Back to Analytic features

List of all functions