累积求和或相减的SQL查询

Question

如何从列的唯一值创建列并进行累积加法或减法。

我有类似的交易记录

>mount<1>0.00

transaction_id	created_at	transaction _type
124	20-08-06 17:00:09	25.00
123	22020-08-06 17:00:03

WITH RECURSIVE RowNums AS
(
SELECT
transaction_id,
created_at,
CAST(amount AS SIGNED) AS amount,
effect,
ROW_NUMBER() OVER
(
ORDER BY
created_at, 
transaction_id
) AS RowNum
FROM transactions t
JOIN transaction_types tt
ON t.transaction_type = tt.id 
), 
Balances AS
(
SELECT
transaction_id,
created_at,
amount,
effect,
100000 AS initial_balance,
100000 +
(
CASE effect
WHEN 'add' THEN amount
WHEN 'subtract' THEN (-(amount))
END
) AS final_balance,
RowNum
FROM RowNums
WHERE RowNum = 1

UNION ALL

SELECT
rn.transaction_id,
rn.created_at,
rn.amount,
rn.effect,
b.final_balance,
b.final_balance +
(
CASE rn.effect
WHEN 'add' THEN rn.amount
WHEN 'subtract' THEN (-(rn.amount))
END
),
rn.RowNum
FROM Balances b
JOIN RowNums rn
ON (b.RowNum + 1) = rn.RowNum
)
SELECT
transaction_id,
initial_balance,
CASE effect
WHEN 'add' 
THEN CAST(amount AS CHAR(20)) 
ELSE ''
END AS deposit,
CASE effect
WHEN 'subtract' 
THEN CAST(amount AS CHAR(20)) 
ELSE ''
END AS withdrawal,
final_balance
FROM Balances
ORDER BY
created_at,
transaction_id

来源：https://www.reddit.com/r/sqltutorial/comments/sx3ycb/comment/hxr6f9l/?utm_source=share&utm_medial=web2x&上下文=3