database - Find broken sequence with match_recognize using Oracle 12C -

i want detect gaps in cdr_id sequence each numbers, using match_recognize analytic function.

code:

sql> select  distinct number,              cdr_id,              status_sequence          tmp_cdr_gaps  match_recognize (                         partition number                          order     cdr_id                         measures  match_number() status_sequence                         rows per match                         after match skip past last row                         pattern   (section_start in_seq_value*)                         define    in_seq_value (cdr_id = prev(cdr_id))                         ) 

this result :

number          cdr_id      status_sequence                          003301011849    536         1 003301011849    537         2 003301011849    538         3 003301011849    539         4 003301011849    540         5 003301011849    541         6 003301011849    542         7 003301011849    544         8           <-- !!!  003301011849    545         9 003301011849    546         10 003301011849    547         11 003301011849    548         12 

you notice cdr_id 543 missing, want detect, expecting status_sequence restart 1 next cdr_id (cdr_id 544 in case).

can me understand wrong match_recognize syntax ?

thank upfront, david

with stacking few analytics on each other...

with detect_first_in_seq$ (     select x.*,         case when lnnvl(lag(x.cdr_id) on (partition num order cdr_id) = x.cdr_id-1) x.cdr_id end is_first_cdr_id_in_seq     tmp_cdr_gaps x ), copy_first_in_seq$ (     select x.*,         last_value(is_first_cdr_id_in_seq ignore nulls) on (partition num order cdr_id) first_cdr_id_in_seq     detect_first_in_seq$ x ) select     x.*,     row_number() on (partition num, first_cdr_id_in_seq order cdr_id) status_sequence copy_first_in_seq$ x ; 

with modified query of yours , 1 additional analytic...

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

and, finally, match_recognize only...

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

this seems work because, found out in oracle doc, aggregate functions in match_recognize() have default modus operandi of doing running aggregates.

thanks, too, getting me bit closer mastering wonderful addition sql. :-)

as can me understand wrong match_recognize syntax ? ...

there multiple things wrong:

1. your in_seq_value defined cdr_id = prev(cdr_id) not make sense. definition you're saying want identify sequences of same cdr_id values, whereas in fact want identify sequences of cdr_id values increasing 1.
2. your match_number() status_sequence numbers sequentially each found pattern. cdr_id = prev(cdr_id) such pattern match 12 one-row "subgroups", each numbered sequentially. cdr_id = prev(cdr_id)+1 1 rows cdr_id 536 542 , 2 rows cdr_id 544 548.