How to add column with accumulations of certain values from another column in SQL?-CodePudding

I have a table:

     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

As you see there is 2 types of actions: "income" and "spending". I want to add column with accumulation of "value" at each moment for each id. And after each action "income" it must increase by value of that "income" and when there is "spending" it must decrease by value of that decrease. So result must look like this:

     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

How to do that? I don't mind to do it with Python too

CodePudding user response：

Assuming that the value can be converted to a negative one where it coincides with spending, a running total can be calculated using a window function as follows:

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

You might also find this resource from LearnSQL of use.

Edit: I have updated the above query to include a CASE nested within the window function; i.e. transform the values into negative ones where the category is 'Spending' and then calculate a running total.

CodePudding user response：

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