A flexible method of grouping data into even or unevenly sized buckets.
Very similar to DECODE
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;
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'.
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.
SELECT last_name, salary,
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
© Copyright SS64.com 1999-2013
Some rights reserved