SQL - SQL Window Frame Clauses (ROWS vs RANGE)

Window functions in SQL allow you to perform calculations across a set of rows related to the current row without collapsing the result set like aggregate functions do. While many users understand basic window functions such as ROW_NUMBER(), RANK(), or SUM() OVER(), a more advanced and often misunderstood concept is the window frame clause, particularly the difference between ROWS and RANGE.

What is a Window Frame?

A window frame defines a subset of rows within the partition (defined by PARTITION BY) and ordering (defined by ORDER BY) that the window function operates on for each row.

In simple terms, for every row, SQL determines:

  • Which rows are included in the calculation

  • Based on the frame specification

The general syntax looks like:

<function>() OVER (
    PARTITION BY column
    ORDER BY column
    <frame_clause>
)

The frame clause typically includes:

  • ROWS or RANGE

  • BETWEEN ... AND ...

Understanding ROWS

The ROWS clause defines the window frame based on the physical position of rows relative to the current row.

Example:

SUM(sales) OVER (
    ORDER BY date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

Explanation:

  • For each row, SQL looks at:

    • The current row

    • The two rows before it

  • The calculation is strictly based on row count, not on the actual values in the ORDER BY column

Key characteristics of ROWS:

  • Precise and predictable

  • Based on row numbers, not values

  • Works well when you need fixed-size sliding windows (e.g., last 3 records)

Understanding RANGE

The RANGE clause defines the window frame based on the value of the ORDER BY column, not row positions.

Example:

SUM(sales) OVER (
    ORDER BY date
    RANGE BETWEEN INTERVAL '2' DAY PRECEDING AND CURRENT ROW
)

Explanation:

  • For each row, SQL includes:

    • All rows where the date falls within 2 days before the current row’s date

  • The number of rows included can vary depending on how many rows share values within that range

Key characteristics of RANGE:

  • Based on value comparison, not row count

  • Can include multiple rows with the same ORDER BY value

  • Less predictable in terms of number of rows included

Key Differences Between ROWS and RANGE

  1. Basis of calculation

    • ROWS uses physical row positions

    • RANGE uses logical value ranges

  2. Determinism

    • ROWS always includes a fixed number of rows

    • RANGE may include a variable number of rows

  3. Handling duplicates

    • ROWS treats duplicate values as separate rows

    • RANGE groups rows with equal ORDER BY values together

  4. Use cases

    • ROWS is ideal for moving averages and fixed window calculations

    • RANGE is useful for time-based or value-based ranges

Example Comparison

Consider a table with sales data:

date sales
Jan 1 100
Jan 2 200
Jan 2 150
Jan 3 300

Using ROWS:

SUM(sales) OVER (
    ORDER BY date
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
)

  • Each row considers exactly one previous row

Using RANGE:

SUM(sales) OVER (
    ORDER BY date
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

  • All rows with the same date are included together

  • Duplicate dates are aggregated as a group

When to Use ROWS

Use ROWS when:

  • You need a fixed number of rows in calculations

  • You want precise control over the window size

  • You are calculating moving averages or rolling metrics

When to Use RANGE

Use RANGE when:

  • You are working with time intervals or numeric ranges

  • You want to group rows with similar ORDER BY values

  • You are performing cumulative calculations based on value thresholds

Important Notes

  • Not all databases support full RANGE functionality, especially with intervals

  • RANGE requires an ORDER BY clause

  • Default frame behavior differs between databases, so it is best to define it explicitly

  • Using RANGE with non-numeric or non-date columns may have limitations

Conclusion

Window frame clauses are a powerful extension of SQL window functions, allowing fine control over how data is aggregated across rows. The choice between ROWS and RANGE depends on whether you want calculations based on physical row positions or logical value ranges. Understanding this distinction is essential for writing accurate and efficient analytical queries in SQL.