Showing the rows around a match: WITH UNMATCHED ROWS and {- -}

ALL ROWS PER MATCH shows the rows that matched and nothing else. Two options change what you see:

The test case: the hat-trick over, every ball

Show all six balls of over 18 in the first innings of match 1 (Callum Reid's hat-trick over). Mark the three hat-trick balls, and leave the other balls unmarked.

Expected result

Way 1: WITH UNMATCHED ROWS

select m.over_ball, m.runs_batter, m.wicket_kind, m.mno, m.cls
from   (select * from cr_balls_v where match_id = 1 and innings_no = 1 and over_no = 18)
match_recognize (
  order by seq_no
  measures match_number() as mno,
           classifier()   as cls
  all rows per match with unmatched rows
  pattern (w{3})
  define w as is_bowler_wicket = 1
) m
order  by m.seq_no;

The unmatched rows come back with MNO and CLS null. This is the view you want when you're checking a pattern by eye: the whole sequence, with the matches marked in it.

Way 2: a pattern-free version with window functions

with marked as (
  select b.*,
         count(case when is_bowler_wicket = 1 then 1 end)
           over (order by seq_no rows between 2 preceding and 2 following) as wkts_near
  from   cr_balls_v b
  where  match_id = 1 and innings_no = 1 and over_no = 18
)
select over_ball, runs_batter, wicket_kind,
       case when is_bowler_wicket = 1 and wkts_near >= 3 then 1 end   as mno,
       case when is_bowler_wicket = 1 and wkts_near >= 3 then 'W' end as cls
from   marked
order  by seq_no;

It gives the same rows here, but look at the shortcut it takes: "a wicket with at least three wickets within two balls either side" isn't the same as "part of three wickets in a row" (W x W W would also pass), and it can't number matches. Fine for a quick look at one over; the pattern is the one that's right by construction.

Exclusion: match on rows you don't return

The test case

For the same hat-trick, show only the ball just before it started and the ball just after it ended.

select m.over_ball, m.runs_batter, m.cls
from   (select * from cr_balls_v where match_id = 1 and innings_no = 1 and over_no = 18)
match_recognize (
  order by seq_no
  measures classifier() as cls
  all rows per match
  pattern (lead_in {- w{3} -} follow)
  define w as is_bowler_wicket = 1
) m
order  by m.seq_no;

The exclusion brackets {- -} look like a batter ducking a bouncer, which suits them: the rows inside duck out of the output. The three wickets still have to be there for the pattern to match: {- w{3} -} is part of the pattern. They just aren't returned. lead_in and follow have no definition, so they're "any ball", and the result is the context around the hat-trick.

Exclusion only changes the output of ALL ROWS PER MATCH. With ONE ROW PER MATCH there are no matched rows in the output to leave out, so it makes no difference there.

19c and 26ai

The same on both.

Gotchas

• Unmatched rows have null measures, including MATCH_NUMBER() and CLASSIFIER(). Filter on cls is null to see what didn't match.
• Exclusion can't be combined with WITH UNMATCHED ROWS. Try it and you get ORA-62515: Exclusion syntax cannot be used with WITH UNMATCHED ROWS.