Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
drop table if exists my_table;
create table my_table(id serial primary key, val int);
insert into my_table(val) values
(0),(1),(1),(1),(1),(0),(-1),(-1),(-1),(0);
10 rows affected

-- enumerate series of non-zero values
select 
    id, 
    val, 
    case 
        val when 0 then 0 
        else sum((val = 0)::int) over w 
        end as series
from my_table
window w as (order by id)
order by id


id
val
series


1
0
0

2
1
1

3
1
1

4
1
1

5
1
1

6
0
0

7
-1
2

8
-1
2

9
-1
2

10
0
0


-- use the above query as a derived table
-- to calculate cumulative sums in each series
select id, val, sum(val) over w
from (
    select 
        id, 
        val, 
        case val 
            when 0 then 0 
            else sum((val = 0)::int) over w 
            end as series
    from my_table
    window w as (order by id)
    ) t
window w as (partition by series order by id)
order by id


id
val
sum


1
0
0

2
1
1

3
1
2

4
1
3

5
1
4

6
0
0

7
-1
-1

8
-1
-2

9
-1
-3

10
0
0