Conditions that look back: PREV, FIRST and running totals

Until now each DEFINE looked at one row on its own: "is this a six?". The interesting patterns compare a row with the rows before it: "this wicket fell soon after the last one", "the total so far is still under 10". Inside DEFINE (and MEASURES) you can use:

Without a variable name, col means the current row, and SUM(col) means every row in the match so far.

The test case: collapses

A collapse is when wickets fall in a heap. Find every run of five or more wickets in an innings where each wicket fell within 10 runs of the one before. Show the score at the first and last of those wickets (runs/wickets), how many wickets fell, and the runs scored between the first and the last.

First we need the fall of wickets: the team's score at the moment each wicket fell. A running total does that. Then the pattern works on just the wicket rows.

Expected result

Auckland in match 25 is the one we planted: 48/4 to 59/10, seven wickets for 11 runs. The scorer barely had time to sharpen his pencil. Notice all three end at /10: once a collapse starts, there's often nobody left to stop it.

Way 1: MATCH_RECOGNIZE with PREV

with fall_of_wickets as (
  select match_id, innings_no, batting_team_id, seq_no, is_wicket,
         sum(total_runs) over (partition by match_id, innings_no order by seq_no) as score,
         sum(is_wicket)  over (partition by match_id, innings_no order by seq_no) as wkts
  from   cr_balls_v
)
select m.match_id, m.innings_no, t.team_code, m.from_score, m.to_score, m.wickets, m.runs
from   (select * from fall_of_wickets where is_wicket = 1)
match_recognize (
  partition by match_id, innings_no, batting_team_id
  order by seq_no
  measures first(score) || '/' || first(wkts) as from_score,
           last(score)  || '/' || last(wkts)  as to_score,
           count(*)                           as wickets,
           last(score) - first(score)         as runs
  pattern (strt quick{4,})
  define quick as score - prev(score) <= 10
) m
join   cr_teams t on t.team_id = m.batting_team_id
order  by m.match_id, m.innings_no;

How it reads:

• The rows are the wickets of one innings, in order. SCORE is the total when that wicket fell.
• quick: a wicket that fell within 10 runs of the previous one. PREV(score) is the score at the previous row, and the rows are wickets, so it's the score at the previous wicket.
• strt isn't in DEFINE at all. A variable with no definition matches any row. It's the wicket the collapse starts from: it doesn't need to be quick itself.
• So strt quick{4,} is five or more wickets with every gap after the first one 10 runs or less.

PREV looks at the previous row in the partition, whatever variable it was mapped to. That's what we want here. On the first row of a partition PREV is null, and null <= 10 isn't true, which is fine: the first wicket of an innings can only ever be a strt.

Pick this when: the condition compares neighbours. It stays one line however the rest of the pattern changes.

Way 2: LAG, then the reset trick

with fall_of_wickets as (
  select match_id, innings_no, batting_team_id, seq_no, is_wicket,
         sum(total_runs) over (partition by match_id, innings_no order by seq_no) as score,
         sum(is_wicket)  over (partition by match_id, innings_no order by seq_no) as wkts
  from   cr_balls_v
),
gaps as (
  select f.*,
         case when score - lag(score) over (partition by match_id, innings_no order by seq_no) <= 10
              then 0 else 1 end as new_group                     -- 1 = this wicket starts a new run
  from   fall_of_wickets f
  where  is_wicket = 1
),
runs_of_wickets as (
  select g.*,
         sum(new_group) over (partition by match_id, innings_no order by seq_no) as grp
  from   gaps g
)
select r.match_id, r.innings_no, t.team_code,
       min(score) || '/' || min(wkts) as from_score,
       max(score) || '/' || max(wkts) as to_score,
       count(*)                       as wickets,
       max(score) - min(score)        as runs
from   runs_of_wickets r
join   cr_teams t on t.team_id = r.batting_team_id
group  by r.match_id, r.innings_no, t.team_code, r.grp
having count(*) >= 5
order  by r.match_id, r.innings_no;

MIN and MAX work here because score and wickets only ever go up within an innings. Three steps instead of one, but every step is plain SQL you can run on its own to see what it does. That's a real advantage when you're debugging.

Running totals inside DEFINE

Aggregates in DEFINE are running: they cover the rows of the match up to and including the current one.

The test case: slow starts

Which three innings took the most deliveries to reach 10 runs? Count the deliveries bowled while the score was still under 10.

Way 1: a running SUM in DEFINE

select m.match_id, m.innings_no, t.team_code, m.deliveries
from   cr_balls_v
match_recognize (
  partition by match_id, innings_no, batting_team_id
  order by seq_no
  measures count(*) as deliveries
  pattern (^ quiet+)
  define quiet as sum(quiet.total_runs) < 10
) m
join   cr_teams t on t.team_id = m.batting_team_id
order  by m.deliveries desc, m.match_id, m.innings_no
fetch  first 3 rows only;

• ^ anchors the match to the start of the partition: only the opening stretch counts, not any quiet spell later on.
• sum(quiet.total_runs) adds up total_runs over the rows mapped to quiet so far, including the one being tested. As soon as that would reach 10, the row isn't quiet and the match ends.

Way 2: a running total, then count

with running as (
  select b.*,
         sum(total_runs) over (partition by match_id, innings_no order by seq_no) as score
  from   cr_balls_v b
)
select r.match_id, r.innings_no, t.team_code, count(*) as deliveries
from   running r
join   cr_teams t on t.team_id = r.batting_team_id
where  r.score < 10
group  by r.match_id, r.innings_no, t.team_code
order  by deliveries desc, r.match_id, r.innings_no
fetch  first 3 rows only;

Simpler, and correct here because the score never goes down: the rows with a score under 10 are exactly the opening stretch. If the running value could go up and down (a price, a temperature), "the rows under 10" would also pick up later stretches, and you'd need the anchor that Way 1 has.

19c and 26ai

The same on both. FETCH FIRST has been there since 12c.

Gotchas

• An undefined variable matches anything. Handy for strt, but a typo in a DEFINE name gives you a variable that matches everything, with no error.
• PREV is about rows, not variables. PREV(score) is the previous row of the partition. If you need "the last row mapped to quick", that's LAST(quick.score).
• Aggregates in DEFINE are running. COUNT(*) in DEFINE is "rows so far", never the final count of the match.
• Nulls fail conditions. On the first row PREV(...) is null, so a condition built on it is not true. Usually that's what you want; when it isn't, use NVL.

Try it yourself

Relax the collapse: four or more wickets, each within 12 runs of the one before. How many collapses are there now?

with fall_of_wickets as (
  select match_id, innings_no, seq_no, is_wicket,
         sum(total_runs) over (partition by match_id, innings_no order by seq_no) as score
  from   cr_balls_v
)
select count(*) as collapses
from   (select * from fall_of_wickets where is_wicket = 1)
match_recognize (
  partition by match_id, innings_no
  order by seq_no
  measures count(*) as wickets
  pattern (strt quick{3,})
  define quick as score - prev(score) <= 12
);