Understanding the FRAME Clause in MySQL 8.0 Window Functions

Previously we introduced MySQL 8.0 window functions without detailing the full syntax; this article focuses on the FRAME clause, which defines how rows inside a window are selected for calculation.

A window function processes each row within a defined partition, unlike an aggregate function that collapses the window into a single summary value.

The FRAME clause follows the PARTITION BY and ORDER BY clauses and has the syntax:

frame_clause:
frame_units frame_extent
frame_units:
{ROWS | RANGE}

frame_extent:
{frame_start | frame_between}
frame_between:
BETWEEN frame_start AND frame_end
frame_start, frame_end: {
CURRENT ROW
| UNBOUNDED PRECEDING
| UNBOUNDED FOLLOWING
| expr PRECEDING
| expr FOLLOWING
}

frame_units can be ROWS (specifies a number of rows) or RANGE (specifies a value range).

frame_extent defines the boundaries of the frame, such as CURRENT ROW , UNBOUNDED PRECEDING , UNBOUNDED FOLLOWING , or an expression with PRECEDING / FOLLOWING .

Using a sample table t1 :

+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id    | int  | YES  |     | NULL    |       |
| r1    | int  | YES  |     | NULL    |       |
| r2    | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+

1. CURRENT ROW – limits the frame to the current row:

select id,r1,sum(r1) over(partition by id order by r1 asc range current row) as wf_result from t1 where id = 1;

Result shows each row’s sum equals its own r1 value.

2. UNBOUNDED PRECEDING – frame starts at the first row:

select id,r1,sum(r1) over(partition by id order by r1 asc rows unbounded preceding) as wf_result from t1 where id = 1;

Each row accumulates the sum from the first row up to the current row.

3. UNBOUNDED FOLLOWING – frame ends at the last row, producing the total sum for every row:

select id,r1,sum(r1) over(partition by id order by r1 asc rows between unbounded preceding and unbounded following) as wf_result from t1 where id = 1;

4. Expression PRECEDING / FOLLOWING – specify a numeric offset:

select id,r1,sum(r1) over(partition by id order by r1 asc rows 1 preceding) as wf_result from t1 where id = 1;

This sums the current row with the previous row only.

select id,r1,sum(r1) over(partition by id order by r1 asc rows 2 preceding) as wf_result from t1 where id = 1;

Extending the offset to two rows includes the two preceding rows in the sum.

select id,r1,sum(r1) over(partition by id order by r1 asc rows between 2 preceding and 4 following) as wf_result from t1 where id = 1;

This combines two rows before and four rows after the current row.

5. RANGE PRECEDING / FOLLOWING – operates on value ranges rather than row counts. For example:

select id,r1,sum(r1) over(partition by id order by r1 asc range 1 preceding) as wf_result from t1 where id = 1;

Each row sums values whose r1 is within one unit less than the current row’s r1 .

select id,r1,sum(r1) over(partition by id order by r1 asc range between 1 preceding and 1 following) as wf_result from t1 where id = 1;

Here the frame includes rows whose r1 is within ±1 of the current row’s value.

These examples illustrate the various ways the FRAME clause can be used in MySQL 8.0 to control the rows participating in window calculations, a useful reference for advanced SQL querying.