MATCH_RECOGNIZE: regular expressions for rows

You probably know regular expressions for text: 6{3,} finds three or more 6s in a row inside a string. MATCH_RECOGNIZE does the same thing, except the "characters" are rows, and you decide what makes a row count as a "6". Finally, a regular expression you can still read the next morning.

It arrived in Oracle 12c and works the same way in 19c and 26ai. 19c+

Why it exists

Plenty of questions are about sequences: three wickets in a row, a price that falls and then rises, a customer who logs in, browses and leaves without buying. You can answer them with LAG, LEAD and running counts (this part shows those ways too), but the SQL gets long, and it gets much longer as the pattern grows. A pattern stays short.

A first taste

Here is one over: six balls, and what the batter scored off each.

The test case

In this over, where does the run of three or more sixes start, where does it end, and how many sixes is it?

Expected result

Way 1: MATCH_RECOGNIZE

with one_over as (
  select 1 as ball, 1 as runs from dual union all
  select 2, 6 from dual union all
  select 3, 6 from dual union all
  select 4, 6 from dual union all
  select 5, 6 from dual union all
  select 6, 0 from dual
)
select *
from   one_over
match_recognize (
  order by ball                        -- 1. put the rows in order
  measures first(ball) as from_ball,   -- 3. what to report about each match
           last(ball)  as to_ball,
           count(*)    as sixes
  pattern (six{3,})                    -- 2. the regular expression...
  define six as runs = 6               --    ...and what each letter means
);

Read it in the numbered order:

1. ORDER BY lines the rows up. A pattern only means something once rows have an order.
2. PATTERN is the regular expression and DEFINE says what each name in it means. six is a pattern variable: a row is a six when runs = 6. six{3,} means "three or more six rows in a row".
3. MEASURES says what to report for each match: here the first and last ball of the match and how many rows it covered.

By default you get one row per match, and only the rows that matched take part, so balls 1 and 6 aren't in the output at all.

Way 2: turn the rows into a string and use a real regular expression

The analogy is exact, so you can test it. Write one letter per ball, then let REGEXP_INSTR find the run.

with one_over as (
  select 1 as ball, 1 as runs from dual union all
  select 2, 6 from dual union all
  select 3, 6 from dual union all
  select 4, 6 from dual union all
  select 5, 6 from dual union all
  select 6, 0 from dual
),
as_text as (
  select listagg(case when runs = 6 then 'S' else '.' end)
           within group (order by ball) as balls     -- '.SSSS.'
  from   one_over
)
select regexp_instr(balls, 'S{3,}')                                 as from_ball,
       regexp_instr(balls, 'S{3,}', 1, 1, 1) - 1                    as to_ball,
       length(regexp_substr(balls, 'S{3,}'))                        as sixes
from   as_text;

Pick this when: never, really. It works for one tiny list, but it can't tell you anything about the rows except their position, and it falls apart with thousands of rows or several kinds of row. It's here to show that MATCH_RECOGNIZE really is regex for rows.

The whole clause on one page

select ...
from   some_rows
match_recognize (
  partition by ...          -- optional: run the pattern separately for each group (each match, each bowler)
  order by ...              -- required: the order the pattern reads the rows in
  measures ...              -- optional: the columns you get for each match
  one row per match         -- default; or ALL ROWS PER MATCH to see every matched row
  after match skip past last row   -- default; where to start looking for the next match
  pattern ( ... )           -- required: the regular expression of pattern variables
  subset ...                -- optional: a name for a union of variables
  define ...                -- the condition for each variable (a variable that's not defined matches any row)
);

And the order Oracle works through it in:

The rest of this part takes these one at a time: