0

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?

Lucija Tokić
  • 13
  • 3
  • 1
    plz show ur table structure – Shibon Jul 20 '17 at 12:37
  • Please do not use tags that do not apply to your question. I removed the database tags as it is unclear which one you are actually using. Please add the tag of *only* the database you are actually using – John Conde Jul 20 '17 at 12:37
  • 1
    I think filtering on the hour would be safer, and easier e.g. `..Where DATEPART(HOUR, entry_date) = @hour` every nth record falls down if you ever have any gaps in your data (or duplicate data) – JeffUK Jul 20 '17 at 12:40
  • Row_number() numbers the rows, (Row_number() -1)%24 = 0 filters every 24th row. – Serg Jul 20 '17 at 12:44
  • Is 3 set in stone (always 3 days?) Also, are you guaranteed to always have the needed rows in the table, or may they be missing - and if they may be missing, how do you want to handle? –  Jul 20 '17 at 12:47
  • @Shibon I edited the post and put a photo of my database. – Lucija Tokić Jul 20 '17 at 13:02
  • @mathguy It is always 3 days. If it's missing just skip it. – Lucija Tokić Jul 20 '17 at 13:02

2 Answers2

0

I think you can do this another way by using filters. 

Select avg(consumption) from my_data
 where 
     entry_date between @StartDate and @EndDate
 and datepart(HOUR, entry_date)=@hour

If you're on MySQL

Select avg(consumption) from my_data
 where 
     entry_date between @StartDate and @EndDate
 and HOUR(entry_date)=@hour
JeffUK
  • 4,107
  • 2
  • 20
  • 34
0

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.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110