Re: Histogram/Bucketizing resultset

From: John Gilson <jag_at_acm.org>
Date: Thu, 31 Oct 2002 19:37:26 GMT
Message-ID: <WDfw9.63952$Up6.14063027@twister.nyc.rr.com>

"NoSpamPlease" <googleposer_at_yahoo.com> wrote in message news:499d4ffb.0210310936.731aa87d_at_posting.google.com...
> Thx Mark
> My bad - I framed the question incorrectly
>
> Assume that the lower bound and the upper bound is not preset
> I need to do this in 1 single query
> Hence
> LB = minimum of the attribute in the table
> UB = max of the attribute in the table
> Bucketsize = UB-LB/10 <- 10 is -need 10 buckets
> Age
> > > 27
> > > 24
> > > 34
> > > 56
> > > 40
> > > 76
> > > 30
> > > 32
> > > 67
> > > 71
>
> LB = 24
> UB = 76
> BucketSize =(76 -24)/10
>
> Bucket Count
> 24-29.2 2
> (total 10 rows)
> ..
> 70.8-76 1

SELECT lowerBound + (b - 1) * bucketSize AS lower,

               lowerBound + b * bucketSize AS upper,
               COUNT(age) AS num
FROM (SELECT MIN(age), (MAX(age) - MIN(age)) / 10.0
              FROM t) AS Stats(lowerBound, bucketSize)
            CROSS JOIN
            (SELECT 1
             UNION ALL
             SELECT 2
             UNION ALL
             SELECT 3
             UNION ALL
             SELECT 4
             UNION ALL
             SELECT 5
             UNION ALL
             SELECT 6
             UNION ALL
             SELECT 7
             UNION ALL
             SELECT 8
             UNION ALL
             SELECT 9
             UNION ALL
             SELECT 10) AS Buckets(b)
            LEFT OUTER JOIN
            T
            ON age BETWEEN lowerBound + (b - 1) * bucketSize AND
                                             lowerBound + b * bucketSize
GROUP BY lowerBound + (b - 1) * bucketSize,
                    lowerBound + b * bucketSize

ORDER BY lowerBound + (b - 1) * bucketSize ASC

On your sample data this query run on SQL Server 2000 returns

lower upper num

24.000000 29.200000 2
29.200000 34.400000 3
34.400000 39.600000 0
39.600000 44.800000 1
44.800000 50.000000 0
50.000000 55.200000 0
55.200000 60.400000 1
60.400000 65.600000 0
65.600000 70.800000 1
70.800000 76.000000 2

Regards,
jag Received on Thu Oct 31 2002 - 13:37:26 CST