如何基于其他表计算结果批量更新PostgreSQL中多行账户余额？

修正后的完整PL/pgSQL函数 #

Create or replace function public.announce_result(luckyWord text, price numeric)
returns void as
$$
begin
   -- 插入完整结果到results表（补充赔付倍数字段）
   insert into results(date, luckyWord, price) values (now(), $1, $2);
   
   -- 批量计算用户盈亏并更新账户余额
   UPDATE accounts a
   SET balance = a.balance + calc.change_amount
   FROM (
       SELECT 
           userid,
           -- 计算余额变化量：中奖金额*赔付倍数 - 总下注金额
           sum(case when luckWord = $1 then amount * $2 else 0 end) - sum(amount) as change_amount
       FROM bets
       GROUP BY userid
   ) calc
   WHERE a.userid = calc.userid;
end
$$ language plpgsql security definer;

逻辑说明 #

1. 修正结果插入：原函数未将赔付倍数price插入Results表，补充后与表结构匹配。
2. 盈亏计算逻辑：
   • 对每个用户，先计算中奖下注的总赔付额（中奖金额 * 赔付倍数）
   • 减去该用户的总下注金额，得到余额的净变化量
   • 例如用户1：(5*50) - (10+5) = 235，原有余额15 + 235 = 250，符合预期；用户2：0 - 2 = -2，原有余额2 - 2 = 0，符合预期
3. 批量更新：通过UPDATE FROM关联账户表和预计算的盈亏结果，一次性完成所有用户的余额更新，避免多次查询的性能损耗。

内容的提问来源于stack exchange，提问作者pckim