[SOLVED] Rolling sum that resets at a flag

Rolling sum that resets at a flag

I'm trying to sum values in a table and then reset the sum at a boundary.

So, given a table like this

id	value	flag
1	23	1
2	10	1
3	15	0
4	18	0
5	11	0
6	1	1
7	14	1
8	16	1

I'd want the sum to reset whenever the flag is 0, so the output from the above would be

sum	calculation
48	(23+10+15)
18	(18)
11	(11)
31	(1+14+16)

The calculation column wouldn't be in the output, it just shows which values should be summed.

Is there a way to do this in SQL (I'm using MySQL). I think what I need to do is turn the table into something like

id	value	group
1	23	1
2	10	1
3	15	1
4	18	2
5	11	3
6	1	4
7	14	4
8	16	4

Then I can sum the groups, but I can't see how to do this either.

Solution

It is a variation of "gaps-and-islands" and can be very easily solved in SQL by introducing a helper grouping column:

SELECT *, 
  SUM(value) OVER(PARTITION BY grp),
  DENSE_RANK() OVER(ORDER BY grp)
FROM (SELECT *, flag + SUM(1-flag) OVER(ORDER BY id) AS grp FROM tab) AS s
ORDER BY id;

Output:

db<>fiddle demo

Assumptions:

flag values allowed: 0 and 1
0 indicates a new group