sql / expert
Snippet
Advanced Sliding Window Frames
Window functions with specific frame clauses like 'ROWS BETWEEN' allow for precise control over the subset of data used for calculations, enabling complex analytics like moving averages or running totals over a defined range.
snippet.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 function over a specific window of rows.
2
ORDER BY sale_date
Determines the logical order of rows within the window.
3
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
Defines the physical frame: the current row and the two rows immediately before it.