Window Function Frames

In this lesson, you will learn how to fine-tune the boundaries of your window functions to perform dynamic, localized analysis on specific subsets of data.

The Problem with Default Frames

When you use a window function like SUM(sales) OVER (PARTITION BY region), the default frame includes all rows from the start of the partition to the current row (or the end of the partition, depending on the function). This "unbounded" view is great for running totals, but analytical pipelines often require more granular control—such as calculating a moving average of the last three days or comparing a current value to only its immediate neighbors.

Defining the Window Frame

A window frame is a physical subset of the current partition. You define it using the ROWS or RANGE clause. By specifying offsets, you transform your aggregation from a global calculation into a sliding, dynamic window.

Consider this example where we calculate a simple moving average for a 3-day window:

SELECT 
    sale_date, 
    daily_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY sale_date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS three_day_moving_avg
FROM daily_sales;

Breaking Down the Syntax

• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW: This explicitly tells the database engine to only include the two rows before the current record and the record itself.
• Dynamic Adaptation: As the query progresses row-by-row, the "frame" shifts. At the first row, it only calculates the average of one record; at the third row, it finally hits the full 3-day sample.
• RANGE vs ROWS: While ROWS looks at physical row offsets, RANGE looks at logical values (e.g., if you have timestamps, RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND CURRENT ROW allows you to aggregate based on actual time distance rather than just the number of entries).

By controlling these boundaries, you avoid the overhead of self-joins and complex subqueries, allowing you to perform sophisticated time-series analysis directly within your analytical pipeline.

Key Takeaway: Window function frames allow you to transition from static partition-wide aggregations to precise, sliding-window calculations by defining specific, relative boundaries for each record.