Re: Histogram/Bucketizing resultset

From: Steve Kass <skass_at_drew.edu>
Date: Fri, 01 Nov 2002 23:27:52 -0500
Message-ID: <3DC35448.14FABCDC@drew.edu>

I hate it when this happens:

set nocount on
go

create table T (
val float
)
insert into T
select freight from northwind..orders
go

create procedure Bucketize (

  @low decimal(10,4),
  @hi  decimal(10,4),
  @N   int

) as
  declare @t table (
    i int
  )
  declare @j int set @j = @N
  while @j > 0 begin
    set @j = @j - 1
    insert into @t select @j
  end
  select
    cast(@low+i*(@hi-_at_low)/@N as decimal(10,4)) as [From],     cast(@low+(i+1)*(@hi-_at_low)/@N as decimal(10,4)) as [To],     count(bNum) as Number
  from @t B left outer join (
    select case when bNum = @N then @N-1 else bNum end as bNum

above case expression puts values equalling @hi in last bucket from ( select floor(@N*(val-_at_low)/(@hi-_at_low)) as bNum from T where val between @low and @hi ) G ) H on B.i = H.bNum group by B.i order by B.i if exists ( select * from T where val not between @low and @hi ) print 'Some values were outside the requested range.' go

exec Bucketize 0,1010,2
exec Bucketize 0,0.4,2
exec Bucketize 1,100,7

drop proc Bucketize
drop table T

Steve

Steve Kass wrote:

> Here's an idea for you, written for SQL Server.

> If you are using SQL Server pre-2000, change the table variable
> to a temporary table - if you're using Oracle, you'll have to figure
> out the changes on your own, though it shouldn't be all that hard.

> Steve Kass
> Drew University

> NoSpamPlease wrote:

>


> > 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

> >

> > Mark Townsend <markbtownsend_at_attbi.com> wrote in message news:<B9E5FECF.B3D%markbtownsend_at_attbi.com>...

> > > in article 499d4ffb.0210302045.452ef143_at_posting.google.com, NoSpamPlease at

> > > googleposer_at_yahoo.com wrote on 10/30/02 8:45 PM:

> > >

> > > > Given an input table T(age)

> > > >

> > > > Age

> > > > 27

> > > > 24

> > > > 34

> > > > 56

> > > > 40

> > > > 76

> > > > 30

> > > > 32

> > > > 67

> > > > 71

> > > >

> > > > I would like to generate the following output

> > > >

> > > > Range        Value

> > > > 0-10        0

> > > > 11-20        0

> > > > 21-30        2

> > > > 31-40        4

> > > > 41-50        0

> > > > 51-60        1

> > > > 61-70        1

> > > > 71-80        2

> > > > 81-90        0

> > > > 91-100        0

> > > >

> > > >

> > > > Can anyone help me with a query

> > > > Thx in adv

> > >

> > > For an Oracle solution, assuming the bucket ranges you have given, something

> > > like

> > >

> > > SELECT

> > >  (CASE WHEN age BETWEEN   0 AND 10 THEN  ' 0 - 10'

> > >        WHEN age BETWEEN  11 AND 20 THEN  '11 - 20'

> > >        WHEN age BETWEEN  21 AND 30 THEN  '21 - 30'

> > >    ...

> > >  END)

> > >   AS Range,

> > >   COUNT(*) AS Value

> > > FROM T

> > > GROUP BY

> > >  (CASE WHEN age BETWEEN   0 AND 10 THEN  ' 0 - 10'

> > >        WHEN age BETWEEN  11 AND 20 THEN  '11 - 20'

> > >        WHEN age BETWEEN  21 AND 30 THEN  '21 - 30'

> > >    ...

> > >  END);

> > >

> > > This obviously becomes a PITA with more buckets.

> > >

> > > Depending on your actual requirements, you may also want to investigate

> > > NTILE or WIDTH_BUCKET functions, which will automatically create either

> > > equiwidth buckets or histograms for you, respectively -

> > > http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/server.920
> > > /a96520/analysis.htm
Received on Fri Nov 01 2002 - 22:27:52 CST