Either order: alternation, PERMUTE, anchors and SUBSET

Some patterns have choices in them: "a four then a six, or a six then a four". This page covers the pattern operators for that, plus two smaller tools: anchors and SUBSET. It ends with an honest lesson: sometimes the best MATCH_RECOGNIZE answer is not to use it.

The test case: a four and a six back to back

Per season, how many times was a four followed straight away by a six, or a six by a four, on consecutive deliveries of an innings? Count overlaps: four, six, four is two pairs.

Expected result

Way 1: PERMUTE

select season, count(*) as four_six_pairs
from   cr_balls_v
match_recognize (
  partition by season, match_id, innings_no
  order by seq_no
  measures match_number() as mno
  after match skip to next row
  pattern (permute(four, six))
  define four as runs_batter = 4,
         six  as runs_batter = 6
)
group  by season
order  by season;

PERMUTE(four, six) means "a four and a six, in either order". With three variables, PERMUTE(a, b, c) means all six orders, which is where it saves real typing.

season is in the PARTITION BY only so that it comes out of the MATCH_RECOGNIZE for the GROUP BY (page 6.2). Every match belongs to one season, so it doesn't change the partitions.

Way 2: alternation

select season, count(*) as four_six_pairs
from   cr_balls_v
match_recognize (
  partition by season, match_id, innings_no
  order by seq_no
  measures match_number() as mno
  after match skip to next row
  pattern (four six | six four)
  define four as runs_batter = 4,
         six  as runs_batter = 6
)
group  by season
order  by season;

| has the lowest precedence, so four six | six four is (four six) | (six four). Use parentheses when you mix it with anything else: x (four six | six four) y.

Way 3: LAG

with prev_ball as (
  select b.*,
         lag(runs_batter) over (partition by match_id, innings_no order by seq_no) as prev_runs
  from   cr_balls_v b
)
select season, count(*) as four_six_pairs
from   prev_ball
where  (runs_batter = 6 and prev_runs = 4)
or     (runs_batter = 4 and prev_runs = 6)
group  by season
order  by season;

For a two-row pattern, LAG is short and fast. Its count matches the overlapping version, because each row is checked against its own previous row, whatever happened before that.

Anchors, and when not to use a pattern

The test case

Per season, in how many overs was there at least one four and at least one six (anywhere in the over, in any order)?

Way 1: a pattern with an anchor

select season, count(*) as overs_with_both
from   cr_balls_v
match_recognize (
  partition by season, match_id, innings_no, over_no
  order by seq_no
  measures match_number() as mno
  pattern (^ other* (four other* six | six other* four))
  define four as runs_batter = 4,
         six  as runs_batter = 6
)
group  by season
order  by season;

other has no definition, so it matches any ball. The pattern says: from the start of the over, any balls, then a four, any balls, then a six (or the other way round).

The ^ matters. Without it, an over like 4 6 1 4 6 would match twice (4 6, then 4 6 again) and be counted twice. With ^, a match has to begin at the first ball of the over, so there can be only one per over.

Way 2: GROUP BY

select season, count(*) as overs_with_both
from   (select season
        from   cr_balls_v
        group  by season, match_id, innings_no, over_no
        having max(is_four) = 1
        and    max(is_six)  = 1)
group  by season
order  by season;

This is the better answer: a pattern that doesn't care about order is just GROUP BY in fancy whites. The question doesn't care about order ("at least one four and at least one six, anywhere"), and when order doesn't matter, it isn't a pattern question. MAX(is_four) = 1 reads as "there was a four". Use MATCH_RECOGNIZE when the order of rows is part of the question.

SUBSET: one name for several variables

SUBSET gives a name to a group of variables, so one measure can cover all of them.

The test case: boundary bursts

Find every run of four or more boundaries in a row (fours or sixes, on consecutive deliveries of an innings). Show how many were fours, how many sixes, and the runs they were worth.

select m.match_id, m.from_ball, m.to_ball, m.fours, m.sixes, m.boundary_runs
from   cr_balls_v
match_recognize (
  partition by match_id, innings_no
  order by seq_no
  measures first(over_ball)        as from_ball,
           last(over_ball)         as to_ball,
           count(four.*)           as fours,
           count(six.*)            as sixes,
           sum(bnd.runs_batter)    as boundary_runs
  pattern ((four | six){4,})
  subset bnd = (four, six)
  define four as runs_batter = 4,
         six  as runs_batter = 6
) m
order  by m.boundary_runs desc, m.match_id;

• count(four.*) counts only the rows mapped to four; count(six.*) only the sixes.
• sum(bnd.runs_batter) adds up the rows mapped to either, because bnd is the subset of both.

Why not sum(four.runs_batter) + sum(six.runs_batter)? Look at the first row: Karthik's burst has no fours, so sum(four.runs_batter) is null, and null plus 24 is null. The subset avoids that. (Here every row of the match is a four or a six, so plain sum(runs_batter) would work too. Subsets earn their place when the pattern has other variables you want to leave out, like the other rows on the pattern above.)

19c and 26ai

The same on both.

Gotchas

• Alternation tries the left side first. (a | b) when a row could be either becomes a, which matters for CLASSIFIER() and measures on a. or b..
• Unanchored patterns can match more than once in a partition. If the question is "does this partition contain...", anchor with ^ or count distinct partitions.
• If order doesn't matter, use GROUP BY. It's simpler and usually faster.