How to get the desired results with MATCH_RECOGNIZE?

Question

Input:

Query written using MATCH_RECOGNIZE clause

select *

from MTR_ACTIONS MATCH_RECOGNIZE (

     PARTITION BY METER_CODE

     ORDER BY ACTION_DT

     MEASURES 

     FIRST(ENTER.ACTION_DT) AS ENTER_DT,

     LAST(EX.ACTION_DT) AS EXIT_DT,

     MATCH_NUMBER() AS MN,

     CLASSIFIER() AS CLS

--     ALL ROWS PER MATCH 

--     ALL ROWS PER MATCH WITH UNMATCHED ROWS

    ONE ROW PER MATCH 

     AFTER MATCH SKIP PAST LAST ROW

     PATTERN (ENTER EX+)

     DEFINE

        ENTER AS (ENTER.ACTION='IN'),

        EX AS (EX.ACTION='OUT') 

     ) MR

WHERE  METER_CODE ='MTR1'

Output of the above query:

Desired output:

I don't know why EXIT_DT is not populated no matter whatever I change in MEASURES/PATTERN/DEFINE section in the above query.

How can I identify the issue?

score 0 · Answer 1 · answered Dec 12 '18 at 07:24

Using lead analytic function is able to solve your problem as

  select site, meter_code, enter_dt, exit_dt
    from
     (
      with mtr_actions( site, meter_code, action_date, action ) as
          (
           select 1,'MTR1',date'2012-03-21','IN'   from dual union all
           select 1,'MTR1',date'2012-04-24','READ' from dual union all
           select 1,'MTR1',date'2012-05-23','READ' from dual union all
           select 1,'MTR1',date'2016-05-27','OUT'  from dual union all
           select 2,'MTR1',date'2016-05-28','IN'   from dual union all
           select 1,'MTR1',date'2016-06-27','SKIP' from dual union all
           select 2,'MTR1',date'2016-06-27','READ' from dual union all
           select 2,'MTR1',date'2016-06-28','OUT'  from dual union all
           select 5,'MTR1',date'2016-06-29','IN'   from dual
          )
       select t.action, site, meter_code, action_date as enter_dt, 
              lead(t.action_date) over (order by t.action_date) as exit_dt
         from mtr_actions t
        where t.action in ('IN','OUT') 
      )  
    where action = 'IN';

    SITE    METER_CODE  ENTER_DT    EXIT_DT
    ------  ----------  ----------  ---------- 
      1        MTR1     21.03.2012  27.05.2016
      2        MTR1     28.05.2016  28.06.2016
      5        MTR1     29.06.2016

Rextester Demo

score 0 · Answer 2 · answered Apr 28 '21 at 10:16

try this:

select * 
from MTR_ACTIONS 
MATCH_RECOGNIZE 
(     PARTITION BY METER_CODE
     ORDER BY action_date
     MEASURES 
     first(enter.site) as site,
     FIRST(ENTER.action_date) AS ENTER_DT,
     LAST(EX.action_date) AS EXIT_DT
     --MATCH_NUMBER() AS MN,
     --CLASSIFIER() AS CLS
     --ALL ROWS PER MATCH 
     --ALL ROWS PER MATCH --WITH UNMATCHED ROWS
     ONE ROW PER MATCH 
     --AFTER MATCH SKIP to next row
     PATTERN (ENTER X* EX?)
     DEFINE
        ENTER AS (ENTER.ACTION='IN'),
        x as  (action in ('READ','SKIP')),
        EX    AS (EX.ACTION='OUT') 
) MR
;

How to get the desired results with MATCH_RECOGNIZE?

2 Answers2