SQL / Django views conditional cumulative sum of values

user_6469978 0
Viewed 68

Pretty new to SQL and Django, I am trying to create a query to cumulate the value of column A (integer) if the value in column B (string) is different. This cumulation should be done based on date (column C).

Here is the data:

Column A Column B Column C 
2.0      Rock     2020-08-08
3.0      Paper    2020-09-08
25.0     Rock     2021-09-09
12.0     Rock     2021-10-10
5.0      Paper    2021-11-11

Based on this data, I would like to have a third column D, which will represent the cumulative value such as follow:

Column A    Column B Column C    Column D
2.0         Rock     2020-08-08  2.0
3.0         Paper    2020-09-08  5.0
25.0        Rock     2021-09-09  28.0
12.0        Rock     2021-10-10  15.0
5.0         Paper    2021-11-11  17.0
window-functions sqlite django-views django sql
1 Answers

If you want a query you can do it with a self join and FIRST_VALUE() window function:

SELECT DISTINCT t1.*, 
       t1.ColumnA + COALESCE(FIRST_VALUE(t2.ColumnA) OVER (PARTITION BY t1.ColumnC ORDER BY t2.ColumnC DESC), 0) ColumnD
FROM tablename t1 LEFT JOIN tablename t2
ON t2.ColumnC < t1.ColumnC AND t2.ColumnB <> t1.ColumnB
ORDER BY t1.ColumnC;

See the demo.

user_104988280
Related