Re: [sqlite] Creating Histogram fast and efficiently :)

[Ribeiro, Glauber]

If the data were small I'd do a case statement, otherwise, create a
table with the ranges and join that to your main table.

g 

-Original Message-
From: Jonathon [mailto:thejunk...@gmail.com] 
Sent: Wednesday, December 31, 2008 5:16 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Creating Histogram fast and efficiently :)

Hello all,

I have a column of numbers in a table, and I was wondering if it is
possible
to create a histogram out of it fast and efficiently?

For example, assuming the data in the column is:  1, 5, 10, 12, 12, 15,
20,
20, 20.. I would like to return:

'less than 10' --> 2
'less than 20 and greater than or equal to 10' --> 4
'great than or equal to 20' --> 3

I hope that makes sense.  I checked all over Google, and it seems that
different databases seem to tackle this problem differently.  So.. I was
just curious how sqlite can help me make this calculation fast :)

Thanks,
J

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creating Histogram fast and efficiently :)

[Jonathon]

Thanks Russell :)

It works great.

J

On Wed, Dec 31, 2008 at 5:52 AM, Russell Leighton  wrote:

>
> create table numbers (val integer);
>
> insert into numbers values (1);
> insert into numbers values (5);
> 
>
> sqlite> select * from numbers order by val;
> 1
> 5
> 10
> 12
> 12
> 15
> 20
> 20
> 20
>
>
>
> select case when val < 10 then 1 when val >=10 and val < 20 then 2
> else 3 end  as bin,
>count(1) as c
> from numbers  group by bin;
>
>
> sqlite> select case when val < 10 then 1 when val >=10 and val < 20
> then 2 else 3 end  as bin,
>...>count(1) as c
>...> from numbers  group by bin;
> 1|2
> 2|4
> 3|3
>
>
> On Dec 31, 2008, at 6:16 AM, Jonathon wrote:
>
> > Hello all,
> >
> > I have a column of numbers in a table, and I was wondering if it is
> > possible
> > to create a histogram out of it fast and efficiently?
> >
> > For example, assuming the data in the column is:  1, 5, 10, 12, 12,
> > 15, 20,
> > 20, 20.. I would like to return:
> >
> > 'less than 10' --> 2
> > 'less than 20 and greater than or equal to 10' --> 4
> > 'great than or equal to 20' --> 3
> >
> > I hope that makes sense.  I checked all over Google, and it seems that
> > different databases seem to tackle this problem differently.  So.. I
> > was
> > just curious how sqlite can help me make this calculation fast :)
> >
> > Thanks,
> > J
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creating Histogram fast and efficiently :)

[Russell Leighton]

create table numbers (val integer);

insert into numbers values (1);
insert into numbers values (5);


sqlite> select * from numbers order by val;
1
5
10
12
12
15
20
20
20



select case when val < 10 then 1 when val >=10 and val < 20 then 2  
else 3 end  as bin,
count(1) as c
from numbers  group by bin;


sqlite> select case when val < 10 then 1 when val >=10 and val < 20  
then 2 else 3 end  as bin,
...>count(1) as c
...> from numbers  group by bin;
1|2
2|4
3|3


On Dec 31, 2008, at 6:16 AM, Jonathon wrote:

> Hello all,
>
> I have a column of numbers in a table, and I was wondering if it is  
> possible
> to create a histogram out of it fast and efficiently?
>
> For example, assuming the data in the column is:  1, 5, 10, 12, 12,  
> 15, 20,
> 20, 20.. I would like to return:
>
> 'less than 10' --> 2
> 'less than 20 and greater than or equal to 10' --> 4
> 'great than or equal to 20' --> 3
>
> I hope that makes sense.  I checked all over Google, and it seems that
> different databases seem to tackle this problem differently.  So.. I  
> was
> just curious how sqlite can help me make this calculation fast :)
>
> Thanks,
> J
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Creating Histogram fast and efficiently :)

[Jonathon]

Hello all,

I have a column of numbers in a table, and I was wondering if it is possible
to create a histogram out of it fast and efficiently?

For example, assuming the data in the column is:  1, 5, 10, 12, 12, 15, 20,
20, 20.. I would like to return:

'less than 10' --> 2
'less than 20 and greater than or equal to 10' --> 4
'great than or equal to 20' --> 3

I hope that makes sense.  I checked all over Google, and it seems that
different databases seem to tackle this problem differently.  So.. I was
just curious how sqlite can help me make this calculation fast :)

Thanks,
J
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users