By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with t(TENURE_NUMBER_ID, EVENT_NUMBER, EVENT_TYPE) as (
select 1099391, 5994168, 'RMPR' from dual union all
select 1099391, 5994169, 'PROT' from dual union all
select 1099489, 5963896, 'PROT' from dual union all
select 1099489, 5994168, 'RMPR' from dual union all
select 1099489, 5994169, 'PROT' from dual union all
select 1099491, 5963896, 'PROT' from dual union all
select 1099491, 5994168, 'RMPR' from dual union all
select 1099491, 5994169, 'PROT' from dual union all
select 1099491, 5990993, 'PROT' from dual union all
select 1099491, 5983849, 'RMPR' from dual union all
select 1099967, 5989988, 'PROT' from dual union all
select 1099967, 5989990, 'PROT' from dual union all
select 1099967, 5989992, 'RMPR' from dual union all
select 1099967, 5989993, 'PROT' from dual union all
select 1099967, 5989999, 'PROT' from dual
)
select *
from t
match_recognize (
partition by TENURE_NUMBER_ID
order by EVENT_NUMBER
measures
count(same.*) as cnt
,CLASSIFIER() AS pttrn
all rows per match
pattern( (same|diff)*)
define
same as prev(EVENT_TYPE) = EVENT_TYPE
,diff as lnnvl(prev(EVENT_TYPE) = EVENT_TYPE)
)
| TENURE_NUMBER_ID | EVENT_NUMBER | CNT | PTTRN | EVENT_TYPE |
|---|---|---|---|---|
| 1099391 | 5994168 | 0 | DIFF | RMPR |
| 1099391 | 5994169 | 0 | DIFF | PROT |
| 1099489 | 5963896 | 0 | DIFF | PROT |
| 1099489 | 5994168 | 0 | DIFF | RMPR |
| 1099489 | 5994169 | 0 | DIFF | PROT |
| 1099491 | 5963896 | 0 | DIFF | PROT |
| 1099491 | 5983849 | 0 | DIFF | RMPR |
| 1099491 | 5990993 | 0 | DIFF | PROT |
| 1099491 | 5994168 | 0 | DIFF | RMPR |
| 1099491 | 5994169 | 0 | DIFF | PROT |
| 1099967 | 5989988 | 0 | DIFF | PROT |
| 1099967 | 5989990 | 1 | SAME | PROT |
| 1099967 | 5989992 | 1 | DIFF | RMPR |
| 1099967 | 5989993 | 1 | DIFF | PROT |
| 1099967 | 5989999 | 2 | SAME | PROT |