I have an Oracle table (Oracle database v12.2.0.2.1) that records when an item has a protection (PROT) placed on it and it's subsequent protection removal (RMPR).
The rule is, an item should not be protected twice consecutively. In other words, before a second protection can be placed on an item, the previous one needs to be removed first.
The users of the database don't always follow this rule and inadvertently place a protection on an item before they removed the previous one. I want to audit the table and query for consecutive 'PROT' on the same item. Here is a sample of my data where:
TENURE_NUMBER_ID | EVENT_NUMBER | EVENT_TYPE |
---|---|---|
1099391 | 5994168 | RMPR |
1099391 | 5994169 | PROT |
1099489 | 5963896 | PROT |
1099489 | 5994168 | RMPR |
1099489 | 5994169 | PROT |
1099491 | 5963896 | PROT |
1099491 | 5994168 | RMPR |
1099491 | 5994169 | PROT |
1099491 | 5990993 | PROT |
1099491 | 5983849 | RMPR |
1099967 | 5989988 | PROT |
1099967 | 5989990 | PROT |
1099967 | 5989992 | RMPR |
1099967 | 5989993 | PROT |
1099967 | 5989999 | PROT |
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)
)
DBFiddle: https://dbfiddle.uk/f4MqsPWX