1 RUN
1 RUN
1 RUN
2 WAIT
2 WAIT
3 RUN
4 WAIT
4 WAIT
5 CANCEL
...
WITH t AS
(
SELECT 1 seq, 'RUN' event FROM dual
UNION ALL SELECT 2, 'RUN' FROM dual
UNION ALL SELECT 3, 'RUN' FROM dual
UNION ALL SELECT 4, 'WAIT' FROM dual
UNION ALL SELECT 5, 'WAIT' FROM dual
UNION ALL SELECT 6, 'RUN' FROM dual
UNION ALL SELECT 7, 'WAIT' FROM dual
UNION ALL SELECT 8, 'WAIT' FROM dual
UNION ALL SELECT 9, 'CANCEL' FROM dual
)
SELECT seq, event
, SUM(flag) OVER(ORDER BY seq) grp
FROM (SELECT seq, event
, DECODE(event, LAG(event) OVER(ORDER BY seq), 0, 1) flag
FROM t
)
;