Defining Preceding and Following Boundaries

In this lesson, you will learn how to create "sliding windows" that capture a specific local neighborhood of data by explicitly defining how many rows before or after your current position to include in your calculations.

Moving Beyond Cumulative Totals

Previously, we looked at cumulative aggregates (like running totals) that included everything from the start of the partition up to the current row. However, real-world analysis often requires a tighter focus. For example, if you are tracking the temperature of a sensor, a "3-day rolling average" is much more meaningful than a cumulative average of every reading since the sensor was turned on.

We define these boundaries using the ROWS clause combined with PRECEDING and FOLLOWING keywords.

How to Configure the Frame

When you define a window, you tell the database exactly how far to look. Consider this query calculating a 3-row moving average (one row before, the current row, and one row after):

SELECT 
    sensor_id,
    reading,
    AVG(reading) OVER (
        ORDER BY reading_time
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS moving_avg
FROM sensor_data;

• 1 PRECEDING: Includes the row immediately above the current one.
• CURRENT ROW: The row currently being processed (implied by the window definition).
• 1 FOLLOWING: Includes the row immediately below the current one.

If you omit the BETWEEN syntax and just use ROWS 2 PRECEDING, the database defaults the window to end at the CURRENT ROW. By using BETWEEN, you gain the flexibility to create "look-ahead" windows, "look-behind" windows, or perfectly centered windows like the one above.

Why This Matters

By adjusting these boundaries, you transform a rigid global calculation into a dynamic, local observation. You are no longer measuring the "whole" dataset; you are measuring the "neighborhood." This is the core mechanism for smoothing out noise in data streams or calculating localized trends without needing to self-join the table multiple times, which keeps your execution plans efficient and avoids unnecessary memory pressure.

Key Takeaway: Using ROWS BETWEEN [N] PRECEDING AND [M] FOLLOWING allows you to isolate a specific local context for your calculations, enabling precise trend analysis like rolling averages.