如何用postgresql 语句实现数据的累加

Question

假设现在有两张表，第一张表叫做prepay_card　表示的是预付卡，第二张表是prepay_card_transition表示的预付卡的交易

我们的第一张表prepay_card的表结构是这样的：

Column	Type	Collation	Nullable	Default
prepay_card_id	integer		not null	generated by default as identity
total	numeric(12,2)		not null
shopper_id	integer		not null
created_at	timestamp with time zone		not null	CURRENT_TIMESTAMP

我们的第二张表的表结构是这样的：

Column	Type	Collation	Nullable	Default
id	integer		not null	generated by default as identity
prepay_card_id	integer		not null
shopper_id	integer		not null
total	numeric(12,2)		not null
created_at	timestamp with time zone		not null	CURRENT_TIMESTAMP

问如何希望将这两张表结合，导出一个新的表来，能够呈现以下数据。

时间　｜　卡号　｜　卡面金额　｜　销售金额　｜　剩余金额　｜ 处理类型

例如，如果shopper_id为1的用户买了一张预付卡，则会在prepay_card表中有一条数据

prepay_card_id	shopper_id	total	created_at
1	1	1000.00	2018-12-19 10:34:17.329586+08

然后他又有两笔消费，会在prepay_card_transition表中有两条数据

id	prepay_card_id	shopper_id	total	created_at
1	1	1	100.00	2018-12-19 11:34:17.329586+08
2	1	1	100.00	2018-12-19 12:34:17.329586+08

则希望得到的数据应该是这样的：

时间	卡号	卡面金额	销售金额	剩余金额	处理类型
2018-12-19 10:34:17.329586+08	1	1000	0	1000	购卡
2018-12-19 11:34:17.329586+08	1	1000	100	900	消费
2018-12-19 12:34:17.329586+08	1	1000	100	800	消费

如何使用 sql实现这样的计算呢？或者这么说吧，难点其实就是对剩余金额的计算，如果计算剩余金额。

Answer 1

WITH data AS (
SELECT created_at, id AS card_id, total, total AS remaining_total FROM prepay_card
UNION ALL
SELECT pct.created_at, pct.prepay_card_id AS card_id, pc.total, -pct.total AS remaining_total
FROM prepay_card_transaction pct 
INNER JOIN prepay_card pc ON pct.prepay_card_id = pc.id
)
SELECT created_at, card_id, total,sum(remaining_total) OVER (PARTITION BY card_id ORDER BY created_at) AS remaining_total_all FROM data
ORDER BY card_id, created_at ;