What is the purpose of the ROWS BETWEEN clause in SQL window functions?

Question

Aryan Kumar · Answer

The "ROWS BETWEEN" clause in SQL window functions is used to define a window or a subset of rows within the result set over which the window function operates. It allows you to specify the range or frame of rows that should be considered when performing calculations with window functions.

The "ROWS BETWEEN" clause typically includes the following options:

UNBOUNDED PRECEDING: This option includes all rows from the start of the partition up to and including the current row.
n PRECEDING: Here, 'n' represents a specific number of rows preceding the current row, including the current row itself. For example, if you specify "2 PRECEDING," it includes the current row and the two rows before it.
CURRENT ROW: This refers to the current row being processed by the window function.
n FOLLOWING: Similar to "n PRECEDING," this option includes the current row and 'n' rows following the current row.
UNBOUNDED FOLLOWING: It includes all rows from the current row up to the end of the partition.

The "ROWS BETWEEN" clause is particularly useful for performing calculations that involve a specific range of rows within a partition. For example, you can calculate rolling averages, cumulative sums, or other aggregates over a defined window of rows.

Here's an example of how you might use the "ROWS BETWEEN" clause in SQL:

SELECT
    OrderDate,
    OrderAmount,
    SUM(OrderAmount) OVER (PARTITION BY CustomerID ORDER BY OrderDate ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS RollingSum
FROM
    Orders

In this example, the "ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING" clause specifies a window that includes the current row and the row immediately before and after it. This allows you to calculate a rolling sum of order amounts for each customer within this specific window.

forum

What is the purpose of the ROWS BETWEEN clause in SQL window functions?

Can you answer this question?

1 Answers