Cumulative sum in PostgreSQL (short)
When we use sql, we sometimes or often get into a situation where we have to calculate cumulative sums. I’ve learned how to do that.
Suppose we have the following table whose name is tbl1, having 3 columns, idx, date, flag_event:
And if we want to calculate the cumulative sum of the values for flag_event, we can take advantage of the sum function in the following way:
postgres=# select *, sum(flag_event) over (partition by batch order by date asc) as cumsum0 from ( select *, 0 as batch from tbl1) as sub1;
Then we can see that we have produced the desired result: