sql - 每行的 SUM 和过滤结果 SUM IN $x

Question

我的表在 Postgres 中的数据:

id  user_id sell_amount sell_currency_id    buy_amount  buy_currency_id type    status  date_add
5   2   2.00000000  1   4.00000000  0   0   0   2013-12-15 19:06:40
6   3   2.60000000  1   5.10000000  0   0   0   2013-12-15 19:06:54 
4   1   1.00000000  1   0.80000000  0   0   0   2013-12-15 19:07:05
7   4   4.00000000  1   8.20000000  0   0   0   2013-12-15 19:07:21
8   5   3.00000000  1   6.00000000  0   1   0   2013-12-15 19:07:40

我必须从该表中选择 id、user_id、sell_amount、sell_currency_id 为 status =0 AND type=0 AND SUM 到 $x 中的当前行，按 ORDER BY buy_amount/sell_amount ASC，date_add ASC

$x = 6的结果

id  user_id sell_amount sell_currency_id    SUM(sell_amount)
4   1   1.00000000  1   1.00000000
6   3   2.60000000  1   3.60000000
5   2   2.00000000  1   5.60000000
7   4   4.00000000  1   9.60000000

Answer 1

你需要一个累积总和，这是 Postgres 提供的。然后逻辑有点棘手。您需要大于或等于 $x 的第一个值。

select id, user_id, sell_amount, sell_currency
from (select id, user_id, sell_amount, sell_currency,
             sum(sell_amount) over (order by buy_amount/sell_amount ASC, date_add ASC) as cumsell
      from table t
      where status = 0 and type = 0
     ) t
where $x <= cumsell and $x > cumsell - sell_amount;