Understanding the "ROWS BETWEEN" Clause in SQL

Abstract

In SQL, the "ROWS BETWEEN" clause is a powerful feature that allows you to define a window of rows for analysis or calculation within a query. By specifying a range of rows, you can perform calculations or apply aggregate functions to a subset of data rather than the entire result set. This article aims to demystify the "ROWS BETWEEN" clause and provide a clear understanding of its usage through an example.

ROWS BETWEEN Clause

The "ROWS BETWEEN" clause is typically used in conjunction with window functions, which are special functions in SQL that operate on a specific subset of rows known as a window. The "ROWS BETWEEN" clause enables you to define the boundaries of the window, specifying which rows should be included in the calculation. 

The syntax is:

ROWS BETWEEN lower_bound AND upper_bound

The bounds can be any of these five options:

• UNBOUNDED PRECEDING – All rows before the current row.
• n PRECEDING – n rows before the current row.
• CURRENT ROW – Just the current row.
• n FOLLOWING – n rows after the current row.
• UNBOUNDED FOLLOWING – All rows after the current row.

Example

Let's consider a table named "Sales" with columns "Product", "Date", and "Revenue". Suppose we want to calculate the average revenue for each product over the last three days. We can achieve this using the "ROWS BETWEEN" clause with the following query:

SELECT 

    Product,

    Date,

    Revenue,

    AVG(Revenue) OVER (

        PARTITION BY Product

        ORDER BY Date

        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

    ) AS AverageRevenue

FROM Sales;

In this example, we partition the data by the "Product" column and order it by the "Date" column. The "ROWS BETWEEN 2 PRECEDING AND CURRENT ROW" clause specifies that we want to include the current row and the two preceding rows in the window frame.

Conclusion

The "ROWS BETWEEN" clause in SQL allows you to define the boundaries of a window frame, enabling focused calculations and aggregations on subsets of data. By specifying the starting and ending points of the window, you can perform powerful analyses.