By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
with t as (
select v.*
from (values (1, 111, 'Carry', 'Carry'), (2, 111, 'Carry', 'Carry'), (3, 111, NULL, 'Carry'), (4, 111, 'Carry', 'Carry')) v(a, id, fg, x)
)
select t.*,
(case when fg = 'Carry' and x = 'Carry'
then row_number() over (partition by id, grp, fg, x order by a)
else 0
end) as z
from (select t.*,
sum(case when fg = 'Carry' and x = 'Carry' then 0 else 1 end) over (partition by id order by a) as grp
from t
) t
order by a
| a | id | fg | x | grp | z |
|---|---|---|---|---|---|
| 1 | 111 | Carry | Carry | 0 | 1 |
| 2 | 111 | Carry | Carry | 0 | 2 |
| 3 | 111 | null | Carry | 1 | 0 |
| 4 | 111 | Carry | Carry | 1 | 1 |