Control the Window Frame With ROWS BETWEEN

Use `ROWS BETWEEN` in a window function to aggregate only the rows immediately surrounding each row.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Compute a rolling 3-sale sum for a salesperson's daily results

Day 1 has no preceding rows so the window contains only itself: 100. Day 2 looks back one row: 100+150=250. Day 3 looks back two rows: 100+150+200=450. Day 4 drops day 1 from the frame: 150+200+250=600. Day 5 drops day 2: 200+250+300=750. The frame slides forward by one row each time, keeping the window at most 3 rows wide.

Source table data Rows loaded before the example query runs.

Setup

CREATE TABLE sales (DAY INT, amount INT);

INSERT INTO
  sales (DAY, amount)
VALUES
  (1, 100),
  (2, 150),
  (3, 200),
  (4, 250),
  (5, 300);

Validated query Shared across supported engines.

SQL

SELECT
  DAY,
  amount,
  SUM(amount) OVER (
    ORDER BY
      DAY ROWS BETWEEN 2 PRECEDING
      AND CURRENT ROW
  ) AS rolling_3
FROM
  sales
ORDER BY
  DAY;

Expected result Returned rows for the shared example.

day	amount	rolling_3
1	100	100
2	150	250
3	200	450
4	250	600
5	300	750

Output is identical across all engines.

Useful when

Where this command helps.

computing a rolling 3-day or 3-period sum or average over time-series data
smoothing noisy metrics by averaging each point with its immediate neighbors

Explanation

What the command is doing.

A window frame clause restricts which rows the aggregate sees within each partition. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means the function considers the current row and the two rows before it — a sliding window of three. Without a frame clause, the default frame runs from the start of the partition to the current row, which is a cumulative total. Explicit frame clauses are supported in MySQL 8+, MariaDB 10.2+, SQL Server, and PostgreSQL.

Control the Window Frame With ROWS BETWEEN

Compute a rolling 3-sale sum for a salesperson's daily results

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.