如何添加列与某些值的积累从另一列在SQL?

Question

我有一个表:

date           id  action     value
2021-09-02      aa  income      500
2021-09-02      aa  spending    500
2021-09-02      aa  spending    45
2021-09-03      aa  income      30
2021-09-03      aa  income      30
2021-09-03      aa  spending    25
2021-09-04      b1  income      100
2021-09-05      b1  income      500
2021-09-05      b1  spending    500
2021-09-05      b1  spending    45
2021-09-06      b1  income      30
2021-09-06      b1  income      30
2021-09-07      b1  spending    25

正如你所看到的，有两种类型的动作:"收入"one_answers";spending"。我想添加一列积累"价值"。在每个id的每个时刻。而每一次行动之后的"收入";它必须增加价值的"income"当有"支出"的时候;它必须根据减少的值而减少。所以结果必须像这样:

date           id  action     value    saved 
2021-09-02      aa  income      500      0
2021-09-02      aa  spending    400      500
2021-09-02      aa  spending    40       100
2021-09-03      aa  income      30       60     
2021-09-03      aa  income      30       90
2021-09-03      aa  spending    25       120
2021-09-04      b1  income      100      0
2021-09-05      b1  income      500      100
2021-09-05      b1  spending    500      600
2021-09-05      b1  spending    45       100
2021-09-06      b1  income      30       55
2021-09-06      b1  income      30       85
2021-09-07      b1  spending    25       115

怎么做?我也不介意用Python做这个

Answer 1

假设该值可以转换为与支出一致的负数，则可以使用如下窗口函数计算运行总额:

SELECT date,action,value,
SUM(CASE WHEN action = 'spending' THEN -1*value ELSE value END) OVER (ORDER BY date)
AS saved
FROM table;

你也可以从LearnSQL中找到这个有用的资源。

编辑:我已经更新了上面的查询，以包括嵌套在窗口函数中的CASE;例如，在类别为"支出"时，将值转换为负值，然后计算运行总数。

Answer 2

select *
, case when row_number() over (order by date) = 1 then 0 
else sum(case when action = 'spending' then -value else value end) over (order by date) end as save 
from table