sqloracle-databasematch-recognize

Query Consequtive Events Occuring on Item on Oracle Databse


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

Solution

  • 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