AFTER MATCH SKIP: where the next match starts

After a pattern matches, Oracle has to decide where to start looking for the next match. That decides whether matches can overlap, and so it changes your counts.

The test case

In match 3, Karthik Raman hit four sixes in a row (15.2 to 15.5) and had a couple of other back-to-back sixes. List every pair of consecutive sixes he hit (balls he faced).

How many pairs is four sixes in a row? It depends on what you mean, and that's the point of this page. (Ask the bowler and the answer is "far too many".)

Expected results

Without overlaps (the default), four sixes make two pairs: 15.2-15.3 and 15.4-15.5.

With overlaps, four sixes make three pairs: 15.2-15.3, 15.3-15.4 and 15.4-15.5.

SKIP PAST LAST ROW (the default)

select m.from_ball, m.to_ball
from   (select *
        from   cr_balls_v
        where  match_id = 3
        and    (extra_type is null or extra_type <> 'WD')
        and    batter_id = (select player_id from cr_players where player_name = 'Karthik Raman'))
match_recognize (
  order by seq_no
  measures first(over_ball) as from_ball,
           last(over_ball)  as to_ball,
           first(seq_no)    as start_seq
  after match skip past last row
  pattern (six six)
  define six as runs_batter = 6
) m
order  by m.start_seq;

The first match is 15.2-15.3. The search starts again at 15.4, which pairs with 15.5. The ball 15.3 is never the start of a pair, because it was used up.

start_seq is a measure we only use to sort: over_ball is text, so ordering by it would put 15.2 before 6.1.

SKIP TO NEXT ROW

select m.from_ball, m.to_ball
from   (select *
        from   cr_balls_v
        where  match_id = 3
        and    (extra_type is null or extra_type <> 'WD')
        and    batter_id = (select player_id from cr_players where player_name = 'Karthik Raman'))
match_recognize (
  order by seq_no
  measures first(over_ball) as from_ball,
           last(over_ball)  as to_ball,
           first(seq_no)    as start_seq
  after match skip to next row
  pattern (six six)
  define six as runs_batter = 6
) m
order  by m.start_seq;

After the match at 15.2-15.3, the search starts again at 15.3, the row after the match's first row. So 15.3-15.4 is found too.

The same with LEAD

Overlapping pairs are exactly what LEAD gives you: every six whose next ball was a six.

with faced as (
  select b.*,
         lead(runs_batter) over (order by seq_no) as next_runs,
         lead(over_ball)   over (order by seq_no) as next_ball
  from   cr_balls_v b
  where  match_id = 3
  and    (extra_type is null or extra_type <> 'WD')
  and    batter_id = (select player_id from cr_players where player_name = 'Karthik Raman')
)
select over_ball as from_ball, next_ball as to_ball
from   faced
where  runs_batter = 6
and    next_runs = 6
order  by seq_no;

There's no simple LEAD version of the non-overlapping count: deciding that 15.3 is "used up" depends on what happened before it, which is the kind of thing a pattern does naturally.

SKIP TO a variable

SKIP TO LAST var starts the next search at the last row mapped to var. With two variables, a b, skipping to b lets the end of one pair be the start of the next:

select m.from_ball, m.to_ball
from   (select *
        from   cr_balls_v
        where  match_id = 3
        and    (extra_type is null or extra_type <> 'WD')
        and    batter_id = (select player_id from cr_players where player_name = 'Karthik Raman'))
match_recognize (
  order by seq_no
  measures first(over_ball) as from_ball,
           last(over_ball)  as to_ball,
           first(seq_no)    as start_seq
  after match skip to last b
  pattern (a b)
  define a as runs_batter = 6,
         b as runs_batter = 6
) m
order  by m.start_seq;

For a two-row pattern that's the same as SKIP TO NEXT ROW. It earns its keep in longer patterns, like "skip to the bottom of the last V shape and look for the next one from there" (Part 13 has a stock-price example).

One rule: you can't skip to the first row of the match itself (for example SKIP TO FIRST a when a is the first row). That would find the same match forever, so Oracle stops with ORA-62517: Next match starts at the same point last match started.

19c and 26ai

The same on both.

Gotchas

• Counting "how many times" depends on overlap. Decide whether four sixes in a row are two pairs or three before you write the query, and say which in your report.
• SKIP TO NEXT ROW can be slow on big data with long patterns, because every row starts a new search. Use it when you need overlaps, not by habit.
• The default is SKIP PAST LAST ROW. If you don't write the clause, you don't get overlaps.

Try it yourself

Across the whole league, how many times was there a hat-trick-or-better if overlapping counts are allowed, using pattern (w w w) on each bowler's legal deliveries? And how many with the default?

select (select count(*)
        from   (select * from cr_balls_v where is_legal = 1)
        match_recognize (
          partition by match_id, bowler_id
          order by innings_no, seq_no
          measures match_number() as mno
          after match skip to next row
          pattern (w w w)
          define w as is_bowler_wicket = 1)) as with_overlap,
       (select count(*)
        from   (select * from cr_balls_v where is_legal = 1)
        match_recognize (
          partition by match_id, bowler_id
          order by innings_no, seq_no
          measures match_number() as mno
          pattern (w w w)
          define w as is_bowler_wicket = 1)) as without_overlap
from   dual;