SQL> with t(value,id) as (
2 select 1 value ,1 id from dual union all
3 select 1 value ,2 id from dual union all
4 select 1 value ,3 id from dual union all
5 select 1 value ,4 id from dual union all
6 select -1 value ,5 id from dual union all
7 select -1 value ,6 id from dual union all
8 select -1 value ,7 id from dual union all
9 select -1 value ,8 id from dual union all
10 select 1 value ,9 id from dual union all
11 select -1 value ,10 id from dual union all
12 select -1 value ,11 id from dual union all
13 select 1 value ,12 id from dual union all
14 select -1 value ,13 id from dual
15 )
16 select id,
17 value,
18 row_number() over(partition by group_id order by id) result
19 from (select id, value, sum(start_value) over(order by id) group_id
20 from (select id,value, lag_value,
21 case when id = 1 or id > 1 and value != lag_value then 1 else 0 end start_value
22 from (select id,
23 value,
24 lag(value) over(order by id) lag_value
25 from t)
26 )
27 )
ID VALUE RESULT
---------- ---------- ----------
1 1 1
2 1 2
3 1 3
4 1 4
5 -1 1
6 -1 2
7 -1 3
8 -1 4
9 1 1
10 -1 1
11 -1 2
12 1 1
13 -1 1
13 rows selected
SQL>