Row-Based Versus Range-Based Framing

In this lesson, you will learn how to precisely control the boundaries of your sliding window calculations by choosing between counting physical rows or evaluating data values.

When using window functions, the ROWS and RANGE keywords define the "frame"—the slice of data the function looks at for each calculation. Choosing the wrong one can lead to logic errors or unexpected performance costs.

Row-Based Framing (ROWS)

Think of ROWS as a physical counter. It treats your result set like a list of numbered items. When you define ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING, you are telling the database: "Look at the physical row physically before this one and the one physically after it, regardless of their values."

This is highly predictable and performant because the database doesn't need to look at the actual data values to determine the frame; it simply moves the pointer along the result set indices.

Range-Based Framing (RANGE)

Think of RANGE as a value threshold. Instead of counting rows, the database looks at the values in the ORDER BY column.

If you have a column of dates and use RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND CURRENT ROW, the database checks: "Which rows have a date value within one day of my current row's date?"

This is much more powerful for time-series data or financial records where "gaps" in data matter. However, it is more complex for the engine:

SELECT 
    sale_date, 
    SUM(amount) OVER (
        ORDER BY sale_date 
        RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW
    ) as weekly_running_total
FROM sales;

In this example, RANGE correctly captures all sales occurring within the last 7 days, even if some days have zero sales (missing rows). If you had used ROWS 7 PRECEDING, you would get the last 7 transactions, which might span over several weeks or just a few hours depending on your sales volume.

Key Takeaway: Use ROWS when you need a fixed count of preceding/succeeding records regardless of their values, and use RANGE when you need to group data based on the logical distance between values, such as time intervals or currency thresholds.