postgresql - 从交易表中查询历史余额数据

Question

如果我回溯数据，需要帮助每天获取客户的总余额。

我在 Postgres 数据库中有以下表结构:

Table1: accounts (acc)
|id|acc_created|
|1 |2019-01-01 |
|2 |2019-01-01 |
|3 |2019-01-01 |

Table2: transactions
|transaction_id|acc_id|balance|txn_created        |
|1             |1     |100    |2019-01-01 07:00:00|
|2             |1     |50     |2019-01-01 16:32:10|
|3             |1     |25     |2019-01-01 22:10:59|
|4             |2     |200    |2019-01-02 18:34:22|
|5             |3     |150    |2019-01-02 15:09:43|
|6             |1     |125    |2019-01-04 04:52:31|
|7             |1     |0      |2019-01-05 05:10:00|
|8             |2     |300    |2019-01-05 12:34:56|
|9             |3     |120    |2019-01-06 23:59:59|

交易表显示账户进行交易后的余额。

老实说，我不确定如何编写查询，或者我是否对这种情况想得太多了。我知道它会涉及 last_value() 和 coalesce()，可能还有 lag() 和 lead()。基本上我想满足的标准是:

• 它采用该帐户当天的最后余额值。(即 2019-01-01 的 acc_id = '1' 的余额为 25 美元，acc_id ='2' 和 '3' 的余额为 0 美元)

• 对于账户没有交易的日子，余额将从该账户之前的余额中扣除。(即 2019 年 1 月 3 日 acc_id = '1' 的余额为 25 美元)

• 最后，我想要按日期汇总的所有账户的总余额。(即在 2019-01-02 年底，总余额应为 $375 (=25+200+150)

我试过下面的查询:

SELECT date_trunc('day',date), sum(balance_of_day) FROM (
SELECT txn.created as date,
       acc_id,
       row_number() over (partition BY acc_id ORDER BY txn_created ASC) as order_of_created,
       last_value(balance) over (partition by acc_id ORDER BY txn_created RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as balance_of_day
FROM transactions) X
where X.order_of_created = 1
GROUP BY 1

但是，如果任何帐户在某一天进行了交易，这只会给我总余额。

预期的最终结果(基于示例)应该是:

|date       |total_balance|
|2019-01-01 |25           |
|2019-01-02 |375          |
|2019-01-03 |375          |
|2019-01-04 |475          |
|2019-01-05 |450          |
|2019-01-06 |420          |

我不需要提供不同的帐号，只需提供当天结束时所有客户的总累计余额。请让我知道如何解决这个问题!非常感谢!

Answer 1

您可以使用一些很酷的 postgres 功能来完成此操作。首先，要获取每天的最后余额，请使用 DISTINCT ON:

SELECT DISTINCT on(acc_id, txn_created::date)
  transaction_id, acc_id, balance, txn_created::date as day
  FROM transactions
  ORDER BY acc_id, txn_created::date, txn_created desc;

为了计算任何给定日期的余额，我们将使用每行的日期范围，包括当前行并排除下一行，按 acc_id 分区:

SELECT transaction_id, acc_id, balance, daterange(day, lead(day, 1) OVER (partition by acc_id order by day), '[)')
FROM (
  SELECT DISTINCT on(acc_id, txn_created::date)
  transaction_id, acc_id, balance, txn_created::date as day
  FROM transactions
  ORDER BY acc_id, txn_created::date, txn_created desc
) sub;

最后，加入generate_series。我们可以加入 generate_series 中的日期包含在我们在上一步中创建的日期范围的位置。日期范围有意不重叠，因此我们可以安全地查询任何日期。

WITH balances as (
  SELECT transaction_id, acc_id, balance, daterange(day, lead(day, 1) OVER (partition by acc_id order by day), '[)') as drange
  FROM (
  SELECT DISTINCT on(acc_id, txn_created::date)
  transaction_id, acc_id, balance, txn_created::date as day
  FROM transactions
  ORDER BY acc_id, txn_created::date, txn_created desc
  ) sub
)
SELECT d::date, sum(balance)
FROM generate_series('2019-01-01'::date, '2019-01-06'::date, '1 day') as g(d)
JOIN balances ON d::date <@ drange
GROUP BY d::date;

     d      | sum
------------+-----
 2019-01-01 |  25
 2019-01-02 | 375
 2019-01-03 | 375
 2019-01-04 | 475
 2019-01-05 | 450
 2019-01-06 | 420
(6 rows)

Here's a fiddle.