sql / expert
Snippet
Logical Framing with RANGE and INTERVAL
The RANGE clause in window functions defines a logical window of rows based on the values in the ORDER BY column. Unlike ROWS, which counts physical positions, RANGE considers the actual values (e.g., dates), allowing for correct sliding window calculations even when there are gaps or multiple entries for the same date.
snippet.sql
1
2
3
4
5
6
SELECT order_date, total_amount,SUM(total_amount) OVER (ORDER BY order_dateRANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW) AS rolling_weekly_sumFROM orders;
Breakdown
1
SUM(total_amount) OVER (
Applies the aggregate SUM function as a window function over a set of rows.
2
ORDER BY order_date
Determines the sequence of rows and the basis for the logical range calculation.
3
RANGE BETWEEN INTERVAL '7' DAY PRECEDING
Defines the start of the window as any row with a date within 7 days before the current row.
4
AND CURRENT ROW
Sets the end of the window to the current row's value.