Window Frames

A Window Frame is a sub-clause that defines a moving subset of rows within the current partition. While PARTITION BY defines the broad group, the FRAME defines the specific rows used for a calculation relative to the current row.

Definition: The ROWS Clause

The frame is defined using the ROWS or RANGE keyword.

• PRECEDING: Rows before the current row.
• FOLLOWING: Rows after the current row.
• CURRENT ROW: The current row being processed.
• UNBOUNDED: From the very beginning or to the very end.

Context: Moving Averages

In finance and data science, we often use Moving Averages to smooth out volatile data. For example, a “3-Day Moving Average” is the average price of the current day and the previous two days.

Without Window Frames, calculating a moving average in SQL would require complex, multi-way joins.

Detailed Explanation: Syntax

Frame Syntax

ROWS BETWEEN [start] AND [end]

Common Frame Patterns

1. Standard Running Total: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
2. 3-Day Moving Window: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
3. Centered Window (1 before, 1 after): ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
4. Entire Partition: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Example: Smoothing Sales Data

We have a DailySales table with high day-to-day fluctuations:

Day	Sales
1	100
2	200
3	10
4	300

Task: Calculate a 3-day moving average (Current + 2 previous).

Moving Average in SQL

SELECT
  Day,
  Sales,
  AVG(Sales) OVER(
      ORDER BY Day
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS MovingAvg
FROM DailySales;

Output

Day	Sales	MovingAvg
1	100	100.0
2	200	150.0
3	10	103.3
4	300	170.0

(Note: For Day 1, the “2 preceding” don’t exist, so it just averages the single row. For Day 2, it averages Day 1 and 2.)

Under the Hood: ROWS vs. RANGE

This is a subtle but important distinction.

1. ROWS

Treats rows as individual units. If you say 1 PRECEDING, it looks exactly one row back in the result set.

2. RANGE

Treats rows as logical values. If your ORDER BY is on a Date, and you say RANGE BETWEEN INTERVAL '1' DAY PRECEDING, it will look for rows with a date exactly 24 hours ago, even if there are 10 rows on that date or zero rows.

Danger

Performance Note: ROWS is generally much faster than RANGE. The database can calculate a ROWS offset by simply moving its pointer in memory. A RANGE calculation requires the database to evaluate the logical value of the data, which is more CPU-intensive.

Summary of Windowing Logic

Requirement	Function	Component
Unique Row ID	ROW_NUMBER()	ORDER BY
% of Total	SUM()	Empty OVER()
Running Total	SUM()	ORDER BY
Growth Rate	LAG()	ORDER BY
Moving Average	AVG()	ROWS BETWEEN