Window frames — ROWS BETWEEN

By default, aggregate window functions accumulate from the start of the partition to the current row. A frame clause lets you control exactly which rows are included.

Syntax

function() OVER (
  PARTITION BY col
  ORDER BY     col
  ROWS BETWEEN <start> AND <end>
)

Frame boundary keywords

Running total (default frame)

SELECT
  hire_date, salary,
  SUM(salary) OVER (
    ORDER BY hire_date, id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM employees
ORDER BY hire_date, id;

This is the default frame when ORDER BY is present — writing it explicitly is optional but clarifies intent.

3-row moving average

SELECT
  hire_date, salary,
  ROUND(AVG(salary) OVER (
    ORDER BY hire_date, id
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ), 2) AS moving_avg_3
FROM employees
ORDER BY hire_date, id;

Full-partition aggregate (for ratio calculations)

SELECT
  department_id, salary,
  ROUND(
    salary / SUM(salary) OVER (
      PARTITION BY department_id
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) * 100, 2
  ) AS pct_of_dept
FROM employees
ORDER BY department_id, salary DESC;

Default frame behavior

Tips

• Use RANGE BETWEEN instead of ROWS BETWEEN when you want to include all rows with the same ORDER BY value as the current row.
• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING gives the full-partition total on every row — useful for percentage calculations.