Thread: columns for count histograms of values

columns for count histograms of values

From
Alexy Khrabrov
Date:
Greetings -- I have a table of the kind

Ratings:
id integer
rating smallint

-- where value can take any value in the range 1 to 5.  Now I want to  
have a statistical table Stats of the form

id integer
min smallint
max smallint
avg real
r1 integer
r2 integer
r3 integer
r4 integer
r5 integer

-- how can I create it in one pass over Ratings?  I can use min(),  
max(), avg() for 

insert into stats values (id,select min(rating), max(rating),  
avg(rating), ...) from ratings

-- but what to do for r1,..,r5, short of subselects (select  
count(rating) from ratings where stats.id=ratings.id) for each, which  
is an overkill?

Also, if a table Stats already exists with some more columns, and we  
need to do an update, not insert, for the above, how would that work --

update stats set min=min(ratings), ... from ratings where  
stats.id=ratings.id -- how do we do the histogram in this case, where  
the id is fixed explicitly?

Cheers,
Alexy


Re: columns for count histograms of values

From
"Mag Gam"
Date:
Just curious, how are you planning to display the histogram? <br />Are you allowed to use application code (C/C++/Perl,
etc..)to generate the histogram? Personally, SQL is great for showing the data but not good for making graphs with data
youcan show.<br /><br /><br /><br /><div class="gmail_quote">On Wed, Apr 30, 2008 at 5:01 PM, Alexy Khrabrov <<a
href="mailto:deliverable@gmail.com">deliverable@gmail.com</a>>wrote:<br /><blockquote class="gmail_quote"
style="border-left:1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> Greetings -- I have a
tableof the kind<br /><br /> Ratings:<br /> id integer<br /> rating smallint<br /><br /> -- where value can take any
valuein the range 1 to 5.  Now I want to have a statistical table Stats of the form<br /><br /> id integer<br /> min
smallint<br/> max smallint<br /> avg real<br /> r1 integer<br /> r2 integer<br /> r3 integer<br /> r4 integer<br /> r5
integer<br/><br /> -- how can I create it in one pass over Ratings?  I can use min(), max(), avg() for<br /> insert
intostats values (id,select min(rating), max(rating), avg(rating), ...) from ratings<br /><br /> -- but what to do for
r1,..,r5,short of subselects (select count(rating) from ratings where stats.id=<a href="http://ratings.id"
target="_blank">ratings.id</a>)for each, which is an overkill?<br /><br /> Also, if a table Stats already exists with
somemore columns, and we need to do an update, not insert, for the above, how would that work --<br /><br /> update
statsset min=min(ratings), ... from ratings where stats.id=<a href="http://ratings.id" target="_blank">ratings.id</a>
--how do we do the histogram in this case, where the id is fixed explicitly?<br /><br /> Cheers,<br /> Alexy<br /><font
color="#888888"><br/> -- <br /> Sent via pgsql-sql mailing list (<a href="mailto:pgsql-sql@postgresql.org"
target="_blank">pgsql-sql@postgresql.org</a>)<br/> To make changes to your subscription:<br /><a
href="http://www.postgresql.org/mailpref/pgsql-sql"target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br
/></font></blockquote></div><br/> 

Re: columns for count histograms of values

From
chester c young
Date:
--- Alexy Khrabrov <deliverable@gmail.com> wrote:

> Greetings -- I have a table of the kind
> 
> Ratings:
> id integer
> rating smallint
> 
> -- where value can take any value in the range 1 to 5.  Now I want to
>  
> have a statistical table Stats of the form
> 
> id integer
> min smallint
> max smallint
> avg real
> r1 integer
> r2 integer
> r3 integer
> r4 integer
> r5 integer
> 
> -- how can I create it in one pass over Ratings? 

select id, min(rating), max(rating), avg(rating), sum( case rating = 1 then 1 else 0 end ), ...


     ____________________________________________________________________________________
Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ


Re: columns for count histograms of values

From
Alexy Khrabrov
Date:
Chester -- awesome!  Exactly what the doctor ordered.  Just one  
syntactic fix needed on 8.3.1:

case when rating=1 then 1 else 0 end

-- etc.

Cheers,
Alexy

On Apr 30, 2008, at 4:12 PM, chester c young wrote:

>
> --- Alexy Khrabrov <deliverable@gmail.com> wrote:
>
>> Greetings -- I have a table of the kind
>>
>> Ratings:
>> id integer
>> rating smallint
>>
>> -- where value can take any value in the range 1 to 5.  Now I want to
>>
>> have a statistical table Stats of the form
>>
>> id integer
>> min smallint
>> max smallint
>> avg real
>> r1 integer
>> r2 integer
>> r3 integer
>> r4 integer
>> r5 integer
>>
>> -- how can I create it in one pass over Ratings? 
>
> select id, min(rating), max(rating), avg(rating),
>  sum( case rating = 1 then 1 else 0 end ),
>  ...
>
>
>
>       
> ____________________________________________________________________________________
> Be a better friend, newshound, and
> know-it-all with Yahoo! Mobile.  Try it now.  http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ