Find broken sequence with match_recognize with Oracle 12C

With multiple analysts stacking on top of each other ...

with detect_first_in_seq$ as (
    select X.*,
        case when lnnvl(lag(X.cdr_id) over (partition by num order by cdr_id) = X.cdr_id-1) then X.cdr_id end as is_first_cdr_id_in_seq
    from tmp_cdr_gaps X
),
copy_first_in_seq$ as (
    select X.*,
        last_value(is_first_cdr_id_in_seq ignore nulls) over (partition by num order by cdr_id) as first_cdr_id_in_seq
    from detect_first_in_seq$ X
)
select
    X.*,
    row_number() over (partition by num, first_cdr_id_in_seq order by cdr_id) as status_sequence
from copy_first_in_seq$ X
;

      

        
        
        
      

    

With a slightly modified request of yours and one more analytical ...

select num, cdr_id,
    row_number() over (partition by num, sequence_group order by cdr_id) as status_sequence
from tmp_cdr_gaps
    match_recognize (
        partition by num
        order     by cdr_id
        measures  match_number() sequence_group
        all rows per match
        after match skip past last row
        pattern   (section_start in_seq_value*)
        define    in_seq_value as (cdr_id = prev(cdr_id)+1)
    );

      

        
        
        
      

    

And finally, only with match_recognize

...

select *
from tmp_cdr_gaps
    match_recognize (
        partition by num
        order by cdr_id
        measures count(1) as status_sequence
        all rows per match
        after match skip past last row
        pattern (sequence_group in_sequence*)
        define in_sequence as (cdr_id = prev(cdr_id)+1)
    );

      

        
        
        
      

    

This works because, as I discovered in the Oracle doc , aggregate functions in match_recognize()

have modus functions by default doing aggregates.

Thanks for getting me a little closer to mastering this great addition to SQL. :-)

Regarding yours Can someone help me understand what is wrong with the match_recognize syntax? ...

There are several errors:

• Yours in_seq_value
  
  , defined as cdr_id = prev(cdr_id)
  
  , doesn't make sense. With this definition, you are saying that you want to identify sequences of the same values cdr_id
  
  , whereas you really want to identify sequences of values cdr_id
  
  that increase by 1.
• Your match_number() status_sequence
  
  numbers are sequentially each found pattern. For cdr_id = prev(cdr_id)
  
  such a template, it will correspond to 12 one-line "subgroups", each of which will be numbered sequentially. For cdr_id = prev(cdr_id)+1
  
  that will be 1
  
  for lines cdr_id
  
  from 536
  
  to 542
  
  and 2
  
  for lines cdr_id
  
  from 544
  
  to 548
  
  .