Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF3';
CREATE TABLE mytable (id, col) AS
SELECT  1, 0 FROM DUAL UNION ALL
SELECT  2, 0 FROM DUAL UNION ALL
SELECT  3, 0 FROM DUAL UNION ALL
SELECT  4, 1 FROM DUAL UNION ALL
SELECT  5, 1 FROM DUAL UNION ALL
SELECT  6, 1 FROM DUAL UNION ALL
SELECT  7, 0 FROM DUAL UNION ALL
SELECT  8, 0 FROM DUAL UNION ALL
SELECT  9, 1 FROM DUAL UNION ALL
SELECT 10, 0 FROM DUAL;
10 rows affected

SELECT id, col, grp
FROM   mytable
MATCH_RECOGNIZE(
  ORDER BY id
  MEASURES MATCH_NUMBER() AS grp
  ALL ROWS PER MATCH
  PATTERN  (same_col+)
  DEFINE   same_col AS FIRST(col) = col
);


ID
COL
GRP


1
0
1

2
0
1

3
0
1

4
1
2

5
1
2

6
1
2

7
0
3

8
0
3

9
1
4

10
0
5


SELECT id, col,
       SUM(has_changed) OVER (ORDER BY id) AS grp
FROM   (
  SELECT t.*,
         CASE LAG(col) OVER (ORDER BY id) WHEN col THEN 0 ELSE 1 END AS has_changed
  FROM   mytable t
)


ID
COL
GRP


1
0
1

2
0
1

3
0
1

4
1
2

5
1
2

6
1
2

7
0
3

8
0
3

9
1
4

10
0
5