Every hat-trick, ball by ball

So far each match came back as one summary row. Sometimes you want the matched rows themselves: the three deliveries of a hat-trick, with who got out on each. That's ALL ROWS PER MATCH. This page also shows the most important decision in any pattern query: what goes in PARTITION BY.

The test case

Find every hat-trick: a bowler taking wickets with three of their own legal deliveries in a row, in one match. List each of those deliveries: the match, the bowler, the ball, how the batter got out, and who it was.

Remember from page 0.2: the three balls can be spread over two of the bowler's overs, with somebody else's over in between. Run outs don't count (the bowler doesn't get credit), and wides and no-balls in between don't break a hat-trick.

Expected result

Two hat-tricks in two seasons is well above the real-world average. (We planted them. Don't tell the selectors.) Callum Reid's is the textbook kind, three balls in one over. Logan Pryce's is the other kind: the last ball of over 17, then the first two balls of over 19. Over 18 was bowled by someone else.

Way 1: MATCH_RECOGNIZE with ALL ROWS PER MATCH

select m.match_id, bw.player_name as bowler, m.over_ball, m.wicket_kind,
       po.player_name as batter_out
from   (select * from cr_balls_v where is_legal = 1)
match_recognize (
  partition by match_id, bowler_id          -- each bowler's deliveries in a match
  order by innings_no, seq_no
  all rows per match
  pattern (w{3,})
  define w as is_bowler_wicket = 1
) m
join   cr_players bw on bw.player_id = m.bowler_id
join   cr_players po on po.player_id = m.player_out_id
order  by m.match_id, m.innings_no, m.seq_no;

With ALL ROWS PER MATCH you get every row of every match, with all its columns (over_ball, wicket_kind, player_out_id...), plus any measures. Rows that aren't part of a match still don't come out (page 6.9 shows how to keep them).

PARTITION BY match_id, bowler_id is what makes this right. Inside a partition there are only that bowler's balls, in order, so "three in a row" means three of his balls in a row. Over 18 isn't his, so it isn't in his partition and can't get in the way.

ORDER BY innings_no, seq_no: a bowler only bowls in one innings per match, but saying innings_no first makes the order correct by itself, without relying on that.

Filtering is_legal = 1 first means wides and no-balls are simply not there, so they can't break the run. (Page 6.4 shows a way to skip them inside the pattern instead.)

The partition mistake

Partition by the innings instead, the way you would for a batting streak, and look what happens:

select m.match_id, bw.player_name as bowler, m.over_ball, m.wicket_kind
from   (select * from cr_balls_v where is_legal = 1)
match_recognize (
  partition by match_id, innings_no         -- every ball of the innings, any bowler
  order by seq_no
  all rows per match
  pattern (w{3,})
  define w as is_bowler_wicket = 1
) m
join   cr_players bw on bw.player_id = m.bowler_id
order  by m.match_id, m.seq_no;

Logan Pryce's hat-trick is gone: the six balls of over 18 sit between his wickets. And it's worse than missing one: this version would also report three wickets in a row by two different bowlers as a "hat-trick". The pattern is the same in both queries. Only the partition changed, and with it the meaning of "in a row".

Way 2: LAG and LEAD

Look two balls back and two balls ahead, per bowler. A wicket ball is part of a hat-trick if it's the last, the middle or the first of three wickets.

with legal as (
  select b.*,
         lag (is_bowler_wicket, 2) over (partition by match_id, bowler_id order by innings_no, seq_no) as back2,
         lag (is_bowler_wicket, 1) over (partition by match_id, bowler_id order by innings_no, seq_no) as back1,
         lead(is_bowler_wicket, 1) over (partition by match_id, bowler_id order by innings_no, seq_no) as next1,
         lead(is_bowler_wicket, 2) over (partition by match_id, bowler_id order by innings_no, seq_no) as next2
  from   cr_balls_v b
  where  is_legal = 1
)
select l.match_id, bw.player_name as bowler, l.over_ball, l.wicket_kind,
       po.player_name as batter_out
from   legal l
join   cr_players bw on bw.player_id = l.bowler_id
join   cr_players po on po.player_id = l.player_out_id
where  l.is_bowler_wicket = 1
and    (   (l.back2 = 1 and l.back1 = 1)        -- third of three
        or (l.back1 = 1 and l.next1 = 1)        -- middle
        or (l.next1 = 1 and l.next2 = 1))       -- first
order  by l.match_id, l.innings_no, l.seq_no;

It works, but count the moving parts: four window functions and three conditions for a pattern of length three. A four-in-four would need six window functions. The pattern version just says w{4,}.

26ai lets you name the window once with the WINDOW clause instead of repeating it four times:

-- 26ai only
select ..., lag(is_bowler_wicket, 2) over w as back2, lag(is_bowler_wicket, 1) over w as back1, ...
from   cr_balls_v b
where  is_legal = 1
window w as (partition by match_id, bowler_id order by innings_no, seq_no)

Part 5 covers it. On 19c you repeat the window each time, as above.

Labels: CLASSIFIER() and MATCH_NUMBER()

With ALL ROWS PER MATCH two functions tell you about each row:

• CLASSIFIER(): which pattern variable the row matched, as text in capitals.
• MATCH_NUMBER(): which match the row belongs to, 1, 2, 3... counted separately in each partition.

Give each of the three wickets its own variable and the classifier names them:

select m.match_id, bw.player_name as bowler, m.over_ball, m.mno, m.which
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,
           classifier()   as which
  all rows per match
  pattern (w1 w2 w3)
  define w1 as is_bowler_wicket = 1,
         w2 as is_bowler_wicket = 1,
         w3 as is_bowler_wicket = 1
) m
join   cr_players bw on bw.player_id = m.bowler_id
order  by m.match_id, m.innings_no, m.seq_no;

Both hat-tricks are match number 1: each is the first match in its own partition (its own match and bowler). MATCH_NUMBER() restarts in every partition.

The pattern w1 w2 w3 is exactly three rows, like w{3}. Three names for rows that meet the same condition look odd here, but it's how you'd write "a dot, then a boundary, then a wicket", where each name has its own condition. (Avoid names like first or next for variables: they're also the names of functions you use inside MATCH_RECOGNIZE.)

19c and 26ai

Everything on this page runs on both, except the WINDOW clause sketch, which is 26ai only.

Gotchas

• The partition defines "in a row". By innings: consecutive balls of the innings. By bowler: consecutive balls of that bowler. Decide which one the question means before you write the pattern.
• ALL ROWS PER MATCH doesn't show the rows in between. Logan Pryce's output jumps from 16.6 to 18.1. Those balls weren't in his partition at all.
• Classifier names come back in capitals (W1, not w1), like any unquoted identifier.

Try it yourself

A "two in two" is the near miss: wickets with two of a bowler's balls in a row. How many times did a bowler take a wicket with the ball straight after one of their wicket balls? (A hat-trick contains two of those.)

select count(*) as two_in_twos
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
  one row per match
  after match skip to next row
  pattern (w w)
  define w as is_bowler_wicket = 1
);