SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
05-02-2008, 05:07 AM
| ||||
| ||||
 columns for count histograms of values 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql      |
|
05-02-2008, 05:07 AM
| |||
| |||
| Re: columns for count histograms of values Just curious, how are you planning to display the histogram? 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 you can show. On Wed, Apr 30, 2008 at 5:01 PM, 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? 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 > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > |
|
05-02-2008, 05:07 AM
| |||
| |||
| Re: columns for count histograms of values --- 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=Ahu06i...Dypao8Wcj9tAcJ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql |
|
05-02-2008, 05:07 AM
| ||||
| ||||
| Re: columns for count histograms of values 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=Ahu06i...Dypao8Wcj9tAcJ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql |
Linear Mode
