By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select *
into t
from (values ( 1, 0),
( 2, 0),
( 3, 2),
( 4, 0),
( 5, 0),
( 6, 3),
( 7, 0),
( 8, 2),
( 9, 0),
(10, 0),
(11, 0),
(12, 0)
) v(id, value)
12 rows affected
select t.*,
(case when max(value) over (partition by grp) < row_number() over (partition by grp order by id) - 1
then 0
else (max(value) over (partition by grp) -
(row_number() over (partition by grp order by id) - 1)
)
end) as my_value
from (select t.*,
sum(case when value <> 0 then 1 else 0 end) over (order by id) as grp
from t
) t
| id | value | grp | my_value |
|---|---|---|---|
| 1 | 0 | 0 | 0 |
| 2 | 0 | 0 | 0 |
| 3 | 2 | 1 | 2 |
| 4 | 0 | 1 | 1 |
| 5 | 0 | 1 | 0 |
| 6 | 3 | 2 | 3 |
| 7 | 0 | 2 | 2 |
| 8 | 2 | 3 | 2 |
| 9 | 0 | 3 | 1 |
| 10 | 0 | 3 | 0 |
| 11 | 0 | 3 | 0 |
| 12 | 0 | 3 | 0 |
with tn as (
select t.id, t.value, row_number() over (order by id) as seqnum
from t
),
cte as (
select tn.id, tn.value, tn.seqnum, tn.value as s
from tn
where id = 1
union all
select tn.id, tn.value, tn.seqnum,
(case when cte.s = 0
then tn.value
when tn.value = 0 and cte.s > 0
then cte.s - 1
-- when tn.value > 0 and cte.value > 0
else tn.value + cte.s - 1
end)
from cte join
tn
on tn.seqnum = cte.seqnum + 1
)
select *
from cte
| id | value | seqnum | s |
|---|---|---|---|
| 1 | 0 | 1 | 0 |
| 2 | 0 | 2 | 0 |
| 3 | 2 | 3 | 2 |
| 4 | 0 | 4 | 1 |
| 5 | 0 | 5 | 0 |
| 6 | 3 | 6 | 3 |
| 7 | 0 | 7 | 2 |
| 8 | 2 | 8 | 3 |
| 9 | 0 | 9 | 2 |
| 10 | 0 | 10 | 1 |
| 11 | 0 | 11 | 0 |
| 12 | 0 | 12 | 0 |