Search notes:

Oracle MATCH_RECOGNIZE: ONE ROW PER MATCH vs ALL ROWS PER MATCH

In the match_recognize clause, it is possible to specify if a matched pattern produces one record (one row per match) or returns a record for all rows that were matched (all rows per match).

The differences between using these claues is demonstrated with the following example.

First, we need a table with some test data. Especially note that there are four groups where where of «consecutive» values for nr (the second and fourth group consists of one row only):

create table tq84_consecutive_rows (
   id   number,
   txt  varchar2(20)
);


insert into tq84_consecutive_rows values ( 1, 'one'   );
insert into tq84_consecutive_rows values ( 2, 'two'   );
insert into tq84_consecutive_rows values ( 3, 'three' );

insert into tq84_consecutive_rows values ( 5, 'five'  );

insert into tq84_consecutive_rows values ( 8, 'eight' );
insert into tq84_consecutive_rows values ( 9, 'nine'  );
insert into tq84_consecutive_rows values (10, 'ten'   );
insert into tq84_consecutive_rows values (11, 'eleven');

insert into tq84_consecutive_rows values (20, 'twenty');

Github repository Oracle-Patterns, path: /SQL/select/match_recognize/find-consecutive-rows/data.sql

One row per match

The following query uses one row per match (which is the default and therefore not explicitetly stated) to find the first and last (min and max) id of each group of consecutive rows:

select * from  tq84_consecutive_rows
match_recognize (
   order by id
   measures
     min(id)       start_id,
     max(id)       end_id
   pattern (R C*)
   define C as id = prev(id) + 1
);
-- 
--   START_ID     END_ID
-- ---------- ----------
--          1          3
--          5          5
--          8         11
--         20         20

Github repository Oracle-Patterns, path: /SQL/select/match_recognize/find-consecutive-rows/one-row-per-match.sql

Find ALL consecutive rows

This query specifies all rows per match and thus returns more details.

In order to return a matched groups starting and ending id, it also specifies first(id) and final last(id) in the measures clause:

select * from  tq84_consecutive_rows
match_recognize (
   order by id
   measures
      match_number()    match_no,
      first(id)         start_id,
      final last(id)      end_id
   all rows per match
   pattern (R C*)
   define C as id = prev(id) + 1
);
--
--         ID   MATCH_NO   START_ID     END_ID TXT                 
-- ---------- ---------- ---------- ---------- --------------------
--          1          1          1          3 one                 
--          2          1          1          3 two                 
--          3          1          1          3 three               
--          5          2          5          5 five                
--          8          3          8         11 eight               
--          9          3          8         11 nine                
--         10          3          8         11 ten                 
--         11          3          8         11 eleven              
--         20          4         20         20 twenty

Github repository Oracle-Patterns, path: /SQL/select/match_recognize/find-consecutive-rows/all.sql

Index