时间戳

首页 > 解决方案 > Postgresql 选择直到达到一定的总量并锁定

sql - Postgresql 选择直到达到一定的总量并锁定

问题描述

我有一个用户批次表。我只想选择直到我的总金额达到一定金额。

id  | user_id | balance | batch_id 
----|---------|-------- |--------
 1  | 1       |   2     | 1
 2  | 2       |   15    | 2
 3  | 1       |   8     | 3
 4  | 1       |   5     | 4 
 5  | 2       |   7     | 5
 6  | 1       |   1     | 6
 7  | 2       |   5     | 7

考虑以下查询：

SELECT * FROM tb_batch_user WHERE user_id = 1 ORDER BY batch_id asc

查询结果为：

    id  | user_id | balance | batch_id 
    ----|---------|-------- |--------
     1  | 1       |   2     | 1
     3  | 1       |   8     | 3
     4  | 1       |   5     | 4 
     6  | 1       |   1     | 6

我想在表上进行选择，直到余额总数为 6。然后只应返回 ids 1、2：

    id  | user_id | balance | batch_id 
    ----|---------|-------- |--------
     1  | 1       |   2     | 1
     3  | 1       |   8     | 3

另一个余额总计为 1 的示例。然后只应返回 ids 1：

    id  | user_id | balance | batch_id 
    ----|---------|-------- |--------
     1  | 1       |   2     | 1

余额总计 11 的示例。应仅返回 id 1、3、4：

    id  | user_id | balance | batch_id 
    ----|---------|-------- |--------
     1  | 1       |   2     | 1
     3  | 1       |   8     | 3
     4  | 1       |   5     | 4

所以，在那之后我需要用 FOR UPDATE 锁定这些行：

     SELECT * FROM tb_batch_user WHERE user_id = 1 ORDER BY batch_id asc FOR UPDATE

我尝试使用窗口功能，但它不允许锁定（FOR UPDATE）。谢谢你的帮助。

标签： sqlpostgresqlsumwindow-functionsgaps-and-islands

解决方案

我能够select. . . for update使用窗口功能：

with inparms as (
  select 1 as user_id, 6 as target
), rtotal as (
  select t.id, i.target,
         sum(t.balance) over (partition by t.user_id
                                  order by t.id
                              rows between unbounded preceding
                                       and 1 preceding) as runbalance
    from tb_batch_user t
         join inparms i 
           on i.user_id = t.user_id
)
select t.*
  from rtotal r
       join tb_batch_user t
         on t.id = r.id
 where coalesce(r.runbalance, 0) < r.target
for update of t;

在这里提琴

推荐阅读