sql / intermediate
Snippet
Calculating Moving Averages using ANSI Window Frame Clauses
The ROWS BETWEEN clause in window functions limits calculation frames to specific relative offsets. This computes sliding aggregates without forcing subqueries or self-joins.
snippet.sql
sql
1
2
3
4
5
6
7
SELECT sale_date, amount,AVG(amount) OVER (PARTITION BY store_idORDER BY sale_dateROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avgFROM sales;
Breakdown
1
AVG(amount) OVER (
Applies an aggregate average function across a dynamically bounded window slice.
2
PARTITION BY store_id
Groups calculation sets independently for each unique store identifier.
3
ORDER BY sale_date
Establishes strict chronological ordering for window frame boundary determination.
4
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
Constrains computation strictly to the current row and its two prior chronological predecessors.