SQLite window frames can now describe groups and value ranges

A moving calculation needs a precise definition of its neighborhood. The previous ten rows, the previous ten distinct ordering groups and observations within ten units of the current value can describe different data. SQLite 3.28.0, released on 16 April, adds GROUPS frames and offset boundaries for RANGE, alongside frame exclusions and window chaining. Those additions let SQL express more of that definition inside the calculation itself.

The practical problem is visible when observations share an ordering value. Two measurements can have the same timestamp, or several transactions can have the same category rank. A row-counted frame treats those as separate positions. Peer-group frames treat equal ordering values together. With a value-range frame, the distance between ordering values determines membership. Choosing a frame therefore requires deciding what the application's notion of proximity means.

I like having these operations available in SQLite because many useful analyses fit within a database file. Moving the calculation into SQL keeps the selection rules beside the data. That only helps when the query states the intended rule accurately, so the distinction between row positions and peer groups is more consequential than the amount of new syntax suggests.

Groups preserve ties

The window regression tests in this release exercise GROUPS with repeated text ordering values. Rows sharing the ordering value receive the same group-based cumulative result. Adding another ordering expression subdivides those groups according to the combined ordering values. A peer group belongs to the window's ordering definition, rather than to an independent application category.

Consider a small illustrative dataset containing two observations at position 10, one at 20 and one at 40. A frame containing the preceding peer group and the current peer group can be expressed directly.

WITH observations(position, amount) AS (
  VALUES (10, 2), (10, 3), (20, 7), (40, 11)
)
SELECT position, amount,
       sum(amount) OVER (
         ORDER BY position
         GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
       ) AS neighboring_total
FROM observations
ORDER BY position, amount;

Both rows at position 10 have a total of 5. The row at 20 has 12 because its preceding group contains both rows at 10. At position 40 the total is 18 because the preceding group is the row at 20. The gap between 20 and 40 does not matter to GROUPS. Its offset counts groups, not the numerical distance between their ordering values.

Using ROWS changes that definition. A boundary of one preceding row identifies a position in the sorted rows, so it cannot promise to include every observation sharing that predecessor's ordering value. Adding a unique ordering term can make the row sequence unambiguous, but it also changes which rows count as peers. That is a semantic change, even if it looks like an innocent attempt to stabilize output.

The outer ORDER BY in the example makes the displayed result easy to follow. It does not supply the frame's ordering. The window's own ORDER BY position determines its peer groups. Keeping those two clauses separate allows the query to calculate one relationship and display the resulting rows in another useful order.

RANGE uses distance in an ordering value

The new offset form of RANGE supports a different calculation. Using the same dataset, a ten-unit lookback includes position 10 when evaluating position 20. It excludes position 20 when evaluating position 40 because that distance is twenty units.

WITH observations(position, amount) AS (
  VALUES (10, 2), (10, 3), (20, 7), (40, 11)
)
SELECT position, amount,
       sum(amount) OVER (
         ORDER BY position
         RANGE BETWEEN 10 PRECEDING AND CURRENT ROW
       ) AS nearby_total
FROM observations
ORDER BY position, amount;

The totals for these rows are 5, 5, 12 and 11. A group-counted frame reaches the preceding distinct value regardless of the gap. This numeric range reaches only ordering values within the stated boundary. The difference becomes particularly useful for unevenly spaced observations, where a fixed number of rows says little about the interval they cover.

The 3.28.0 window implementation requires exactly one ordering expression for RANGE with offset boundaries. That restriction gives the arithmetic a single value on which to operate. It also checks that a range offset is a nonnegative number. Integer offsets are required for the corresponding row and group counts, while range offsets use the numeric validation path.

Units belong to the ordering expression. In the example, ten means ten units of position. It does not acquire a calendar interpretation from the word RANGE. The source's range comparison code applies addition or subtraction to numeric ordering values and has separate handling for nonnumeric values. An application wanting a numeric time interval needs to order by a suitable numeric time representation.

Exclusions refine an existing frame

EXCLUDE operates after the frame boundaries identify the candidate neighborhood. The EXCLUDE CURRENT ROW option removes the current observation. Using EXCLUDE GROUP removes its peer group. With EXCLUDE TIES, other peers disappear but the current row remains. The implementation compares ordering values when deciding peer membership, and it checks row identity separately for the current-row and ties cases.

A leave-one-out average demonstrates why this belongs in the frame definition. With an entire-partition frame, each row can obtain an average of the other observations without a self-join.

SELECT position, amount,
       avg(amount) OVER (
         ORDER BY position
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
         EXCLUDE CURRENT ROW
       ) AS other_observations_mean
FROM observations;

The table name here represents the same pair of columns used in the earlier examples. Excluding the current row leaves its peers available. Excluding the group would remove all observations sharing its ordering value, even though the frame uses ROWS. This distinction matters for repeated timestamps, where excluding one measurement and excluding its entire timestamp group answer different questions.

The release's tests combine exclusions with several frame forms. Their results make clear that exclusion does not expand a frame to find replacement rows. A row removed from an already selected neighborhood simply stops contributing to that calculation. A preceding-only frame may already omit the current row, in which case excluding that row changes nothing.

Chaining shares the common definition

Window chaining reduces repetition when calculations share a partition and ordering but use different boundaries. A base window can define the common grouping and order. Derived definitions add their own frames.

SELECT account_id, sequence_no, amount,
       sum(amount) OVER (
         account_order ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total,
       avg(amount) OVER (
         account_order ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS recent_mean
FROM entries
WINDOW account_order AS (
  PARTITION BY account_id ORDER BY sequence_no
);

The restrictions are deliberate. The chaining code rejects a new partition clause in the derived window. It also rejects overriding an ordering clause already present in the base. A base with an explicit frame cannot be extended this way. Defining the shared partition and ordering in a frameless base makes the relationship between the calculations visible without introducing inheritance rules for conflicting frames.

Shared spelling does not establish identical execution cost. The source describes a rewrite that places partition and ordering work in a subquery, with the outer query retaining its presentation ordering. Identical window definitions can share processing. Different definitions may require additional transformations. Some functions and frames need an entire partition cached in a temporary table before returning rows.

The source also distinguishes aggregate processing from returning a result row. It generates virtual machine instructions that advance the aggregate and, where appropriate, remove an observation's contribution as a frame moves. Exclusions have a path that scans the relevant frame and checks which observations to admit. This provides a mechanical reason to avoid assuming that two syntactically similar frames perform the same work. The membership rule can change which implementation strategy is available.

Changing partition size has a corresponding meaning for execution. The source's description of partition caching refers to the partition defined by the query, not necessarily the whole input table. A window partitioned by account can have different buffering requirements from one window over every account. That follows from where SQLite groups the work, independently of whether the application later requests only a short displayed result.

These details also explain why a window calculation retains individual output rows. The generated code invokes a subroutine to return each current row with its computed window results. It does not replace every partition with a single grouped result. A report can therefore preserve an observation's identity while attaching a group-based running sum or a value-based moving average. That is the useful distinction for applications that need both individual measurements and their surrounding context in one query result.

I see the value of chaining primarily in making related calculations agree on their partition and order. A correction to the shared account ordering has one definition to change. Each calculation still declares its own frame, which keeps the mathematical difference visible at the point of use.

The new syntax also does not make every built-in window function obey an arbitrary frame. The implementation assigns particular internal frames to functions such as row_number and rank. Aggregate windows such as the sums and averages above are the clearest place to see the newly expressible neighborhoods. For those calculations, 3.28.0 can distinguish peer-group adjacency from numeric proximity and can remove selected peers without discarding the original output rows.