Window Frame: ROWS and RANGE

Control the window frame size for moving averages and rolling calculations.

Overview

The window frame clause (ROWS BETWEEN ... AND ...) controls which rows are included in the window calculation relative to the current row. By default, with ORDER BY, the frame includes all rows from the partition start to the current row. Explicit frame clauses let you define rolling windows (last 7 rows, last 30 days) for moving averages and rolling sums. Common frame boundaries: UNBOUNDED PRECEDING (start of partition), n PRECEDING (n rows/values before), CURRENT ROW, n FOLLOWING, UNBOUNDED FOLLOWING (end of partition). ROWS uses exact row counts; RANGE uses value ranges.

Key Points

•ROWS BETWEEN 2 PRECEDING AND CURRENT ROW last 3 rows
•ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW cumulative from start
•ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 3-row centered window
•RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW rolling 7 days
•Default frame with ORDER BY: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Examples

Moving average with frame clause

SELECT
  sale_date, amount,
  -- 3-day moving average
  AVG(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3d,
  -- Cumulative sum
  SUM(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_sum
FROM daily_sales;

Window Frame: ROWS and RANGE

Overview

Key Points

Examples

Exercises

Knowledge Check

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW defines a window of how many rows?