Running vs final, and the gotchas in one place

One idea trips up nearly everyone who uses ALL ROWS PER MATCH: a measure can be worked out as of each row (running) or for the whole match (final). Then this page collects every gotcha from Part 6 in one list, for reference.

RUNNING and FINAL

• RUNNING (the default in ALL ROWS PER MATCH): the measure is computed over the rows of the match up to the current row. COUNT(*) on the second row of a match is 2.
• FINAL: computed over the whole match, so every row of the match shows the same value.

With ONE ROW PER MATCH there's only one row, at the end of the match, so running and final are the same thing. And in DEFINE, everything is running, because Oracle is still deciding where the match ends.

The test case

For Logan Pryce's hat-trick (match 20), show each of the three balls with: how many wickets of the hat-trick had fallen by then, how many it had in total, and the ball it ended on.

Way 1: RUNNING and FINAL in MEASURES

select m.over_ball, m.so_far, m.in_total, m.ends_at
from   (select * from cr_balls_v where is_legal = 1 and match_id = 20)
match_recognize (
  partition by bowler_id
  order by innings_no, seq_no
  measures running count(*)       as so_far,
           final   count(*)       as in_total,
           final   last(over_ball) as ends_at
  all rows per match
  pattern (w{3,})
  define w as is_bowler_wicket = 1
) m
order  by m.innings_no, m.seq_no;

Without the FINAL keyword, last(over_ball) would just be the current row's ball: on the first row of the match, the "last row so far" is the first row.

Way 2: window functions over the matched rows

The same columns as ordinary analytics, once you know which rows the hat-trick is made of:

with hat_trick as (
  select *
  from   (select * from cr_balls_v where is_legal = 1 and match_id = 20)
  match_recognize (
    partition by bowler_id
    order by innings_no, seq_no
    measures match_number() as mno
    all rows per match
    pattern (w{3,})
    define w as is_bowler_wicket = 1
  )
)
select over_ball,
       count(*) over (partition by bowler_id, mno order by seq_no)            as so_far,
       count(*) over (partition by bowler_id, mno)                            as in_total,
       max(over_ball) keep (dense_rank last order by seq_no)
                  over (partition by bowler_id, mno)                          as ends_at
from   hat_trick
order  by innings_no, seq_no;

RUNNING is a window with ORDER BY (a running frame); FINAL is a window over the whole partition. Thinking of them that way makes the two keywords easy to remember.

Every gotcha in Part 6

Read the list once now, and again the day a query returns almost the right answer.

Order and partition

1. Order by something unique. If two rows tie on the ORDER BY, which comes first can change from run to run, and so can your matches. Here: seq_no, never just over_no.
2. The partition defines "in a row". Per innings or per bowler gives different hat-tricks (6.3). Decide what "consecutive" means first.
3. Filter before, not after. WHERE on the input decides which rows the pattern sees. A filter on the output can't put rows back or take them out of a sequence.

What comes out

4. ONE ROW PER MATCH returns only partition columns and measures (6.2). Anything else you need later has to be one of those.
5. ALL ROWS PER MATCH returns only matched rows unless you add WITH UNMATCHED ROWS (6.9).
6. Measures are running in ALL ROWS PER MATCH. Use FINAL for whole-match values (this page).
7. MATCH_NUMBER() restarts in each partition (6.3).

The pattern

8. An undefined variable matches any row (6.5). Useful, and also what happens silently when you misspell a variable name in DEFINE.
9. * and ? can make empty matches (6.4): rows with a count of 0 and null measures.
10. Greedy by default, reluctant with a trailing ? (6.4).
11. No overlaps by default. AFTER MATCH SKIP TO NEXT ROW allows them, and it changes counts (6.6).
12. Unanchored patterns can match several times per partition. Use ^ for "starts at the beginning" (6.7).
13. Avoid function names as variables (first, last, prev, next, final, running).

Choosing the tool

14. If order doesn't matter, it isn't a pattern. "Overs with a four and a six" is a GROUP BY (6.7).
15. For one or two rows of look-back, LAG/LEAD are fine. Reach for MATCH_RECOGNIZE when the pattern has several steps, repeats (+, {3,}), or might grow.

Performance in one paragraph

MATCH_RECOGNIZE sorts each partition once by its ORDER BY and then reads it in a single pass, like a window function. The cost is mostly that sort, so: filter early (fewer rows to sort), keep partitions small (by match, by bowler), and be careful with SKIP TO NEXT ROW plus long greedy patterns like x* y, which can make Oracle re-read many rows for every starting point. Part 14 shows how to read the plan (look for the step whose name starts with MATCH RECOGNIZE).

19c and 26ai

MATCH_RECOGNIZE works the same way in 19c and 26ai. Everything in Part 6 runs on both.