PostgreSQL: running a query for each row and saving the result in it

I store weekly game score in a table called pref_money:

# select * from pref_money limit 5;
       id       | money |   yw
----------------+-------+---------
 OK32378280203  |   -27 | 2011-44
 OK274037315447 |   -56 | 2011-44
 OK19644992852  |     8 | 2011-44
 OK21807961329  |   114 | 2011-44
 FB1845091917   |   774 | 2011-44
(5 rows)

And for the winners of each week I display medal(s):

I find the number of medals for a user by running:

# select count(id) from (
     select id,
            row_number() over(partition by yw order by money desc) as ranking
     from pref_money
) x
where x.ranking = 1 and id='OK260246921082';
 count
-------
     3
(1 row)

And that query is quite costly:

# explain analyze select count(id) from (
    select id,
           row_number() over(partition by yw order by money desc) as ranking
    from pref_money
) x
where x.ranking = 1 and id='OK260246921082';
                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=18946.46..18946.47 rows=1 width=82) (actual time=2423.145..2423.145 rows=1 loops=1)
   ->  Subquery Scan x  (cost=14829.44..18946.45 rows=3 width=82) (actual time=2400.004..2423.138 rows=3 loops=1)
         Filter: ((x.ranking = 1) AND ((x.id)::text = 'OK260246921082'::text))
         ->  WindowAgg  (cost=14829.44..17182.02 rows=117629 width=26) (actual time=2289.079..2403.685 rows=116825 loops=1)
               ->  Sort  (cost=14829.44..15123.51 rows=117629 width=26) (actual time=2289.069..2319.575 rows=116825 loops=1)
                     Sort Key: pref_money.yw, pref_money.money
                     Sort Method:  external sort  Disk: 4320kB
                     ->  Seq Scan on pref_money  (cost=0.00..2105.29 rows=117629 width=26) (actual time=0.006..22.566 rows=116825 loops=1)
 Total runtime: 2425.001 ms
(9 rows)

That is why (and because my web site is struggling during peak times, with 50 queries/s displayed in pgbouncer log) I'd like to cache that value and have added a column medals to another table - the pref_users:

pref=> \d pref_users;
                Table "public.pref_users"
   Column   |            Type             |   Modifiers
------------+-----------------------------+---------------
 id         | character varying(32)       | not null
 first_name | character varying(32)       |
 last_name  | character varying(32)       |
 female     | boolean                     |
 avatar     | character varying(128)      |
 city       | character varying(32)       |
 lat        | real                        |
 lng        | real                        |
 login      | timestamp without time zone | default now()
 last_ip    | inet                        |
 medals     | smallint                    | default 0
 logout     | timestamp without time zone |
Indexes:
    "pref_users_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "pref_users_lat_check" CHECK ((-90)::double precision <= lat AND lat <= 90::double precision)
    "pref_users_lng_check" CHECK ((-90)::double precision <= lng AND lng <= 90::double precision)
    "pref_users_medals_check" CHECK (medals >= 0)

I would like to create a cronjob to be run every 15 minutes to update that column for all users in the pref_users table:

*/15       *       *       *       *       psql -a -f $HOME/bin/medals.sql

As you see, I've got almost everything in-place. My problem is that I haven't come up with the SQL statement yet for updating the medals column.

Any help please?

I'm us开发者_StackOverflowing PostgreSQL 8.4.8 with CentOS Linux 5.6 / 64 bit.

Thank you! Alex

Well, won't this produce a result of user IDs and medal counts?

create view user_medal_count as
select id, count(*) as medals from (
     select id,
            row_number() over(partition by yw order by money desc) as ranking
     from pref_money
) x
where x.ranking = 1
group by id

So you need to use that as a source to update your users:

update pref_users
set medals = user_medal_count.medals
from user_medal_count
where pref_users.id = user_medal_count.id
      and (pref_users.medal_count is null
           or pref_users.medal_count <> user_medal_count.medal_count)

I hope that gets you started.

There are issues left to consider. You probably want to define at which point a user is awarded a medal- the medal for the "current week" is presumably subject to change, so you may want to define the medal count as the stable count of previous weeks' medals, calculate the current week's medal on the fly (which should require looking at much less data), or simply exclude it. (If you don't do anything, then you may find users get a medal_count of 1 if they temporarily get the current week's medal, but that never gets reset to 0 if it is later given to someone else).