PostgreSQL：对每一行运行查询并将结果保存在其中

Question

我将每周的游戏得分存储在名为 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)

对于每周的获胜者，我显示奖牌：

我通过运行以下命令找到用户的奖牌数量：

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

并且该查询的成本相当高：

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

这就是原因（并且因为我的网站在高峰时段陷入困境，pgbouncer 日志中显示每秒 50 个查询）我想缓存该值，并将一列 medals 添加到另一个表 - 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)

我想创建一个每 15 分钟运行一次的 cronjob 来更新pref_users 表中所有用户的该列：

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

如您所见，我几乎已准备就绪。我的问题是我还没有想出用于更新 medals 列的 SQL 语句。

有什么帮助吗？

我正在使用 PostgreSQL 8.4.8 和 CentOS Linux 5.6 / 64 位。

谢谢你！ 亚历克斯

Answer 1

那么，这不会产生用户 ID 和奖牌计数的结果吗？

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

因此，您需要使用它作为更新用户的来源：

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)

我希望这能让您开始。

还有一些问题需要考虑。您可能想要定义用户在哪个点被授予奖牌 - “当前周”的奖牌可能会发生变化，因此您可能需要将奖牌数量定义为稳定数量前几周的奖牌，即时计算本周的奖牌（这应该需要查看更少的数据），或者干脆将其排除。 （如果您不执行任何操作，那么您可能会发现，如果用户暂时获得了本周的奖牌，则其 Medal_count 为 1，但如果稍后将其授予其他人，则永远不会重置为 0）。