Quantifiers: how many rows a variable can match

A quantifier after a pattern variable says how many rows in a row it may match. They're the same ones you know from text regular expressions:

All of them are greedy by default: they take as many rows as they can, like openers in the powerplay taking everything on offer.

See them side by side

One over again, and a variable d for a dot ball:

The test case

For each of six patterns, d+, d*, d?, d{2}, d{2,}? and d+?, list the matches it finds in this over: the match number, its first and last ball, and how many balls it covers.

Expected result

The query

with one_over as (
  select 1 as ball, 0 as runs from dual union all
  select 2, 0 from dual union all
  select 3, 4 from dual union all
  select 4, 0 from dual union all
  select 5, 0 from dual union all
  select 6, 0 from dual
)
select 1 as n, 'd+' as pattern, m.* from one_over
  match_recognize (order by ball
    measures match_number() as mno, first(ball) as from_ball, last(ball) as to_ball, count(*) as balls
    pattern (d+) define d as runs = 0) m
union all
select 2, 'd*', m.* from one_over
  match_recognize (order by ball
    measures match_number() as mno, first(ball) as from_ball, last(ball) as to_ball, count(*) as balls
    pattern (d*) define d as runs = 0) m
union all
select 3, 'd?', m.* from one_over
  match_recognize (order by ball
    measures match_number() as mno, first(ball) as from_ball, last(ball) as to_ball, count(*) as balls
    pattern (d?) define d as runs = 0) m
union all
select 4, 'd{2}', m.* from one_over
  match_recognize (order by ball
    measures match_number() as mno, first(ball) as from_ball, last(ball) as to_ball, count(*) as balls
    pattern (d{2}) define d as runs = 0) m
union all
select 5, 'd{2,}?', m.* from one_over
  match_recognize (order by ball
    measures match_number() as mno, first(ball) as from_ball, last(ball) as to_ball, count(*) as balls
    pattern (d{2,}?) define d as runs = 0) m
union all
select 6, 'd+?', m.* from one_over
  match_recognize (order by ball
    measures match_number() as mno, first(ball) as from_ball, last(ball) as to_ball, count(*) as balls
    pattern (d+?) define d as runs = 0) m
order by 1, 3;

Reading the results

• d+ finds the two runs of dots, 1-2 and 4-6, each as long as possible.
• d* finds the same two, plus an empty match at ball 3. d* is allowed to match zero rows, and at ball 3 (a four) zero rows is the best it can do. An empty match covers no rows: COUNT(*) is 0 and FIRST(ball) is null. Then the search moves on one row.
• d? takes at most one row each time, so every dot is its own match, and ball 3 is an empty match again.
• d{2} takes exactly two: 1-2 and 4-5. Ball 6 is left on its own and can't make a pair.
• d{2,}? is reluctant: "two or more, but as few as possible", which is two. Same as d{2} here.
• d+? is reluctant +: one row each time.

Empty matches are why patterns that can match nothing at all (d*, d?) are rarely what you want on their own. They're useful inside a bigger pattern, like w d* w: "a wicket, any number of dots, another wicket".

Reluctant quantifiers matter most in the middle of a pattern: a.*?b in text stops at the first b. At the end of a pattern, as here, "as few as possible" just means the minimum.

A real one: the bounce-back

The test case

A bowler gets hit for a boundary (a four or a six) and then bowls at least two dot balls straight after it, in the same over. Which bowlers did that five times or more?

Expected result

Way 1: MATCH_RECOGNIZE

select p.player_name as bowler, count(*) as bounce_backs
from   cr_balls_v
match_recognize (
  partition by match_id, innings_no, over_no, bowler_id     -- one over at a time
  order by seq_no
  measures match_number() as mno
  pattern (boundary dot{2,})
  define boundary as runs_batter in (4, 6),
         dot      as is_dot = 1
) m
join   cr_players p on p.player_id = m.bowler_id
group  by p.player_name
having count(*) >= 5
order  by bounce_backs desc, p.player_name;

The pattern is two variables in a row: one boundary row, then two or more dot rows. Rows must match in exactly that order, with nothing in between, because a pattern is a sequence.

Partitioning by over keeps "the same over" true: the pattern can't run into the next over.

Way 2: LEAD

with ahead as (
  select b.*,
         lead(is_dot, 1) over (partition by match_id, innings_no, over_no order by seq_no) as dot1,
         lead(is_dot, 2) over (partition by match_id, innings_no, over_no order by seq_no) as dot2
  from   cr_balls_v b
)
select p.player_name as bowler, count(*) as bounce_backs
from   ahead a
join   cr_players p on p.player_id = a.bowler_id
where  a.runs_batter in (4, 6)
and    a.dot1 = 1
and    a.dot2 = 1
group  by p.player_name
having count(*) >= 5
order  by bounce_backs desc, p.player_name;

Here {2,} turns into "the next two are dots": the third and later dots don't change the count, so we don't need to look further. If the question became "...then three dots and a wicket", Way 2 would need a LEAD for every position. Way 1 would just change the pattern to boundary dot{3} w.

Pick Way 1 when the pattern might grow; Way 2 is fine for a fixed look-ahead of one or two rows.

Skipping rows inside the pattern

On page 6.3 we filtered out wides and no-balls before looking for hat-tricks. You can let the pattern skip them instead: allow any number of illegal deliveries between the wickets.

select m.match_id, bw.player_name as bowler, m.from_ball, m.to_ball
from   cr_balls_v                                    -- every delivery, extras included
match_recognize (
  partition by match_id, bowler_id
  order by innings_no, seq_no
  measures first(over_ball) as from_ball,
           last(over_ball)  as to_ball
  pattern (w (x* w){2,})
  define w as is_bowler_wicket = 1 and is_legal = 1,
         x as is_legal = 0
) m
join   cr_players bw on bw.player_id = m.bowler_id
order  by m.match_id;

(x* w){2,} is a group with its own quantifier: "any number of illegal balls, then a wicket", two or more times. Parentheses group variables exactly as in text regex.

Gotchas

• Empty matches happen with * and ?. If you get rows with a count of 0, a pattern that can match nothing is why.
• A pattern is a strict sequence. boundary dot{2,} won't skip a single run between them. If you want to allow something in between, say so in the pattern (boundary x* dot).
• Greedy is the default. d+ in the middle of a pattern grabs as much as it can, then gives rows back only if the rest of the pattern needs them.

Try it yourself

Which pattern finds "a run of two to four dots, but no longer"? Try d{2,4} on the over above in your head: what does it return for balls 4-6?