Как добавить столбец с накоплением определенных значений из другого столбца в SQL?

#python #sql #python-3.x #dataframe #presto

Вопрос:

У меня есть столик:

      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

Как вы видите, существует 2 вида действий: «доходы» и «расходы». Я хочу добавить столбец с накоплением «значения» в каждый момент для каждого идентификатора. И после каждого действия «доход» должен увеличиваться на величину этого «дохода», а когда есть «расходы», он должен уменьшаться на величину этого уменьшения. Поэтому результат должен выглядеть так:

      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

Ответ №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 об использовании.

Изменить: Я обновил приведенный выше запрос, включив в него СЛУЧАЙ, вложенный в функцию окна; т. Е. Преобразуйте значения в отрицательные, где категория «Расходы», а затем рассчитайте текущую сумму.

Ответ №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