Query Consequtive Events Occuring on Item on Oracle Databse

Question

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:

ITEM_ID is the unique identifier of the item
EVENT_ID is the unique identifier of the PROT or RMPR event
EVENT_TYPE is the type of event (PROT or RMPR)

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

In other words if the number of PROT vs RMPR per item are bigger than 1 and are different would do the same? — Jorge Campos, Aug 26 '23 at 00:24

score 3 · Accepted Answer · answered Aug 26 '23 at 01:16

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

score 3 · Answer 2 · answered Aug 26 '23 at 09:56

You can also use

last_value(EVENT_TYPE) over(PARTITION BY TENURE_NUMBER_ID ORDER BY EVENT_NUMBER
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS last_event_type

The rows

where last_event_type = event_type

are the duplicates, so filtering also on last_event_type = 'PROT' will give the answer .

Query Consequtive Events Occuring on Item on Oracle Databse

2 Answers2