A window frame selects the rows positioned relative to the current row for evaluating a window function. The frame moves through an ordered partition, using preceding or following rows defined by a row count or range expression. section 4.5.2
A frame supports practical analysis patterns:
- Specify a centered seven-day frame with three days preceding, the current day, and three days following. section 4.5.2
- Combine PARTITION BY system_id with ORDER BY day to calculate a moving average independently for each system. section 4.5.2
- Choose row-based distances or range-based intervals from the ordering expression. section 4.5.2
Listing 4.17 Using a range partition for applying a window function
SELECT system_id,
day,
kWh,
avg(kWh) OVER (
PARTITION BY system_id #1
ORDER BY day ASC #2
RANGE BETWEEN INTERVAL 3 Days PRECEDING #3
AND INTERVAL 3 Days FOLLOWING
) AS "kWh 7-day moving average"
FROM v_power_per_day
ORDER BY system_id, day;
Inspect a complete SQL window definition for a seven-day, range-based moving average over daily energy readings. section 4.5.2
