How to calculate moving average value using every nth row (e.g. 24th,48th and 72nd) in sql?

Here is the snip of my database I want to calculate average energy consumption for the last three days in the exact hour. So if I have consumption at 24.10.2016. 10h, I want to add column with average consumption for the last three days at the same hour, so for 23.10.2016. 10h, 22.10.2016. 10h and 21.10.2016. 10h. My records are measured every hour, so in order to calculate this average I have to look at every 24th row and haven't found any way. How can I modify my query to get what I want:

    select avg(consumption) over (order by entry_date rows between 72
    preceding and 24 preceding) from my_data;

Or is there some other way?

Solution

Maybe try this one:

select entry_date, EXTRACT(HOUR FROM entry_date),
     avg(consumption) over (PARTITION BY EXTRACT(HOUR FROM entry_date) 
         order by entry_date rows between 72 preceding and 24 preceding) 
from my_data;

and you may use RANGE BETWEEN INTERVAL '72' HOUR PRECEDING AND INTERVAL '24' HOUR PRECEDING instead of ROWS. This covers situation when you have gaps or duplicate time values.