在PostgreSQL中计算帐户余额历史记录

Question

我正在尝试使用SQL获取帐户的余额历史记录。我在PostgreSQL中的表如下所示：

id  sender_id    recipient_id           amount_money
--- -----------  ---------------------- -----------------
1   1            2                      60.00
2   1            2                      15.00
3   2            1                      35.00

因此id号为2的用户当前在他的账户中有40美元。我想使用sql:得到这个结果

[60, 75, 40]

在postgres中使用sql可以做这样的事情吗？

Answer 1

要获得滚动余额，您可以根据id是收件人还是发件人来SUM金额(最多包括当前行(：

SELECT id, sender_id, recipient_id, amount_money,
SUM(CASE WHEN recipient_id = 2 THEN amount_money
WHEN sender_id = 2 THEN -amount_money
END) OVER (ORDER BY id) AS balance
FROM transactions

输出：

id  sender_id   recipient_id    amount_money    balance
1   1           2               60.00           60.00
2   1           2               15.00           75.00
3   2           1               35.00           40.00

如果您想要一个数组，可以将array_agg与上面的查询一起用作派生表：

SELECT array_agg(balance)
FROM (
SELECT SUM(CASE WHEN recipient_id = 2 THEN amount_money
WHEN sender_id = 2 THEN -amount_money
END) OVER (ORDER BY id) AS balance
FROM transactions
) t

输出：

[60,75,40]

dbfiddle 演示

如果你想更复杂并支持多个账户的余额，你需要将初始数据拆分为账户id，当id是收件人时进行加法运算，当id是发件人时进行减法运算。您可以使用CTE生成适当的数据：

WITH trans AS (
SELECT id, sender_id AS account_id, -amount_money AS amount
FROM transactions
UNION ALL
SELECT id, recipient_id AS account_id, amount_money AS amount
FROM transactions
),
balances AS (
SELECT id, account_id, ABS(amount),
SUM(amount) OVER (PARTITION BY account_id ORDER BY id) AS balance
FROM trans
)
SELECT account_id, ARRAY_AGG(balance) AS bal_array
FROM balances
GROUP BY account_id

输出：

account_id  bal_array
1           [-60,-75,-40]
2           [60,75,40]

dbfiddle 演示