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

Question

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

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。然后只返回id 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的示例。应该只返回ids，3，4:

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

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

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

我尝试了窗口功能，但它不允许锁定(更新)。感谢任何帮助。

Answer 1

下面是一种使用窗口函数的方法：

select id, balance, user_id, batch_id
from (
    select t.*, 
        sum(balance) over(partition by user_id order by id) sum_balance
    from mytable t
    where user_id = 1
) t
where sum_balance - balance < 6

您需要一个累积余额，直到第一个余额等于或超过阈值。为此，您可以只使用一个窗口< code>sum()。

您可以将不等式条件更改为所需的阈值。您还可以更改（或删除）子查询中对user_id的筛选。

我们可以使用子查询轻松实现相同的逻辑，这将支持进行更新：

select *
from mytable t
where user_id = 1 and (
    select coalesce(sum(balance), 0)
    from mytable t1
    where t1.user_id = t.user_id and t1.id < t.id
) < 6
for update

DB Fiddle上的演示：

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

Answer 2

你在找这个吗？

with w0 as (
  select id, user_id, balance, batch_id,
     coalesce(lag(running_balance) over (partition by user_id order by batch_id asc), 0) running_balance 
  from (
      SELECT t.* ,
        sum(balance) over (partition by user_id order by batch_id asc) running_balance
      FROM tb_batch_user t 
      --where t.user_id = 1
  ) x 
)
select * from w0
where running_balance < 6

PS：您可以添加user_id作为where子句。请参见注释

为了锁定，

select * from tb_batch_user tb
where tb.id in (select w0.id from w0 where running_balance < 6)
for update 

Answer 3

我能够选择。。。对于使用窗口函数进行更新：

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;

在这里拉小提琴