sql / expert
Snippet
Advanced Window Frame Specifications for Moving Averages
Window frames refine window functions by defining a specific subset of rows relative to the current row. Using 'ROWS BETWEEN' allows the calculation of moving averages or running totals over a sliding window (e.g., the last 3 days of sales), providing deeper temporal analysis than simple aggregations.
snippet.sql
sql
1
2
3
4
5
6
7
8
SELECTsale_date,amount,AVG(amount) OVER (ORDER BY sale_dateROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avgFROM sales;
Breakdown
1
AVG(amount) OVER (
Applies the average aggregate as a window function.
2
ORDER BY sale_date
Defines the logical sequence for the window calculation.
3
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
Defines the frame: includes the two previous rows and the current one (a 3-row sliding window).