Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Histogram/Bucketizing resultset

Re: Histogram/Bucketizing resultset

From: Mark Townsend <markbtownsend_at_attbi.com>
Date: Fri, 01 Nov 2002 05:01:36 GMT
Message-ID: <B9E74AA1.C91%markbtownsend@attbi.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US