Window functions, upgraded: WINDOW, GROUPS, EXCLUDE

Three additions to window functions (Part 5) that arrived in 21c, so they're in 26ai but not in 19c:

Part 1: the last three overs

The test case

In the first innings of the 2026 final (match 32), show the runs in each over from over 14 on, and the runs in that over plus the two before it.

The tricky part: an over has six legal balls but can have seven or eight deliveries. (Six balls, except when it isn't. Bowlers, eh.) "The last three overs" isn't "the last 18 rows".

Way 1: GROUPS on the raw deliveries

26ai only

select distinct over_no, over_runs as runs, last_3_overs
from   (select over_no,
               sum(total_runs) over (order by over_no groups current row)                        as over_runs,
               sum(total_runs) over (order by over_no groups between 2 preceding and current row) as last_3_overs
        from   cr_balls_v
        where  match_id = 32 and innings_no = 1)
where  over_no >= 14
order  by over_no;

With ORDER BY over_no, all the deliveries of one over have the same value, so they form one group. GROUPS BETWEEN 2 PRECEDING AND CURRENT ROW means "this over and the two before it", however many deliveries each had. GROUPS CURRENT ROW is just this over. Every delivery of an over gets the same two numbers, so DISTINCT keeps one row per over.

Way 2: one row per over first, then ROWS (19c)

select over_no, runs, last_3_overs
from   (select over_no, runs,
               sum(runs) over (order by over_no rows between 2 preceding and current row) as last_3_overs
        from   (select over_no, sum(total_runs) as runs
                from   cr_balls_v
                where  match_id = 32 and innings_no = 1
                group  by over_no))
where  over_no >= 14
order  by over_no;

Squash each over into one row, then ROWS really does mean overs. Note the over_no >= 14 filter is outside: filter first and over 14 would lose overs 12 and 13 from its total.

Way 3: RANGE (19c)

select distinct over_no, over_runs as runs, last_3_overs
from   (select over_no,
               sum(total_runs) over (order by over_no range current row)                        as over_runs,
               sum(total_runs) over (order by over_no range between 2 preceding and current row) as last_3_overs
        from   cr_balls_v
        where  match_id = 32 and innings_no = 1)
where  over_no >= 14
order  by over_no;

RANGE counts by value: "over numbers from this one minus 2 up to this one". It agrees with GROUPS here because over numbers have no gaps. If an over were missing (say the innings was cut short and restarted), RANGE 2 PRECEDING would still mean "over numbers within 2", while GROUPS 2 PRECEDING would mean "the two previous overs that exist". Pick the one that matches your question.

Part 2: naming the window

The same three-overs question with the WINDOW clause, so each window is written once:

26ai only

select distinct over_no, over_runs as runs, last_3_overs
from   (select over_no,
               sum(total_runs) over this_over  as over_runs,
               sum(total_runs) over last_three as last_3_overs
        from   cr_balls_v
        where  match_id = 32 and innings_no = 1
        window by_over    as (order by over_no),
               this_over  as (by_over groups current row),
               last_three as (by_over groups between 2 preceding and current row))
where  over_no >= 14
order  by over_no;

last_three as (by_over groups ...) builds on by_over: it inherits its ORDER BY and adds a frame. With three or four window columns on the same partition and order, this is a big readability win.

Part 3: EXCLUDE

The test case

For Chennai's batters in the first innings of the 2026 final, show each batter's runs and the runs scored by the rest of the batters.

Way 1: EXCLUDE CURRENT ROW

26ai only

with by_batter as (
  select batter_id, min(seq_no) as first_ball, sum(runs_batter) as runs
  from   cr_balls_v
  where  match_id = 32 and innings_no = 1
  group  by batter_id
)
select p.player_name, b.runs,
       sum(b.runs) over (order by b.first_ball
                         rows between unbounded preceding and unbounded following
                         exclude current row) as rest_of_team
from   by_batter b
join   cr_players p on p.player_id = b.batter_id
order  by b.first_ball;

The frame is the whole innings (UNBOUNDED PRECEDING to UNBOUNDED FOLLOWING), minus the current row. EXCLUDE needs an explicit frame, which needs an ORDER BY; here first_ball puts the batters in batting order.

Way 2: total minus own (19c)

with by_batter as (
  select batter_id, min(seq_no) as first_ball, sum(runs_batter) as runs
  from   cr_balls_v
  where  match_id = 32 and innings_no = 1
  group  by batter_id
)
select p.player_name, b.runs,
       sum(b.runs) over () - b.runs as rest_of_team
from   by_batter b
join   cr_players p on p.player_id = b.batter_id
order  by b.first_ball;

For a sum, subtracting works just as well. EXCLUDE earns its keep with functions you can't subtract: "the best score by anyone else" is max(runs) over (... exclude current row), and there's no max(...) - something that does that.

The other EXCLUDE options: EXCLUDE GROUP leaves out the current row and all its peers (rows with the same ORDER BY value), EXCLUDE TIES leaves out the peers but keeps the current row, and EXCLUDE NO OTHERS (the default) leaves out nothing.

19c and 26ai

Gotchas

• DISTINCT after a window function is how you collapse "same value on every row of the group". Make sure every selected column really is the same within the group, or DISTINCT won't collapse it.
• Filter after windowing. A WHERE in the same query runs before the window functions and changes what they see. Filter outside, or use QUALIFY (12.2).
• RANGE with a number offset needs a numeric or date ORDER BY; GROUPS works with any type.