Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Histogram/Bucketizing resultset
in article B9E741D5.C3D%markbtownsend_at_attbi.com, Mark Townsend at
markbtownsend_at_attbi.com wrote on 10/31/02 8:24 PM:
> Hence the WIDTH_BUCKET function - something like (I don't have Oracle at
> home to test this) -
>
> SELECT WIDTH_BUCKET(age, lbound, ubound, nbr_buckets) "Bucket Nbr", count(*)
> FROM t
> GROUP BY "Bucket Nbr";
>
> where lbound is min(age), ubound is max(age), and nbr_buckets is the number
> of buckets you want (10)
>
> Note that Bucket Nbr would be a number i.e 1 for the first bucket, 2 for the
> second bucket etc.
>
> To turn this number into the actual range statement, you would need
> something like an inline view
>
> SELECT lbound+(((ubound-lboundb)/nbr_buckets) * (b.bucket_nbr-1))||' - '||
> lbound+(((ubound-lboundb)/nbr_buckets) * b.bucket_nbr) "Bucket",
> val "Count"
> FROM (SELECT WIDTH_BUCKET(age, lbound, ubound, nbr_buckets) "Bucket_Nbr",
> count(*) val
> FROM t
> GROUP BY "Bucket") b
>
> Note that this will work (assuming the theory is correct) for any range of
> values, but more importantly for any number of buckets. YMMV, as there is
> bound to be some debugging required for the above pseudo code.
Apologies - I just realized I missed the most significant part of your follow up post
>> Assume that the lower bound and the upper bound is not preset
I'll need to think about this a bit more, at the office, with a database in front of me. Received on Thu Oct 31 2002 - 23:01:36 CST