How to query avg for every past 7 days in sql, MySQL?

Question

Say I have a dataset of :

|dateid     | value |

|20150101   |   1   |
|20150102   |   2   |
|20150103   |   3.1 |
|20150104   |   4.3 |
|20150105   |   3.1 |
|20150106   |   1   |
|20150107   |   1   |
|20150108   |   1   |
|....       |      |
|....       |   ...   |
|20151001   |   10.3|

I want to query the average of every past 7 days based on a date range.

say for dateid from 20150707 and 20150730, when I select row of 20150707, I also need the average value between 20150701 and 20150707( (1+2+3.1+4.3+1+1+1+1)/7) as well as the value for 20150707(1) like:

select dateid, value , avg(value) as avg_past_7 from mytable where dateid between 20150707 and 20150730GROUP BY every past_7days.

And when the records are less than 7 rows to count, the avg remains null. That means if I only have records from 20150707-20150730 in the table, the past_7_day avg for 20150707/8/9/10/11/12 remains null.

http://stackoverflow.com/questions/25922379/sql-query-for-7-day-rolling-average-in-sql-server — Piotr, Nov 02 '15 at 09:10
I don't think this is a duplicate of [SQL Query for 7 Day Rolling Average in SQL Server](http://stackoverflow.com/questions/25922379/sql-query-for-7-day-rolling-average-in-sql-server) because this is not asking for a **Rolling Average**. Rather, looking for averages of multiple distinct previous 7-day periods. — Mogsdad, Nov 13 '15 at 02:10

score 1 · Answer 1 · edited Nov 03 '15 at 03:13

1

Correlated sub-select:

select dateid, value, (select avg(value) from mytable t2
                       where t2.dateid between (DATE_SUB(date(t1.dateid),INTERVAL 6 day)+0)
                                       and t1.dateid) as avg_past_7
from mytable t1
where dateid between 20150101 and 20150201 order by dateid;

edited Nov 03 '15 at 03:13

JaskeyLam

15,405
21
114
149

answered Nov 02 '15 at 09:14

jarlh

42,561
8
45
63

But what want is not an avg of all the range, what i want is every past 7 days for every dateid. The SQL will give me the same value for the column of avg_past_7 – JaskeyLam Nov 02 '15 at 09:39
Sorry, didn't get that. – jarlh Nov 02 '15 at 09:51
I adjust your function and it works basically, but what if I need one more: when there are no pass 7 days records, the avg value remains null; That means: if I have records from 20150103-20151201, and when I select the row of 20150107, I can't find 20150101->20150107, so it remains null. The same as to 20150104/5/6/7/8. – JaskeyLam Nov 03 '15 at 03:15
Do you want 0 in that case? Try "coalesce(avg(value),0)". – jarlh Nov 03 '15 at 07:29

score 0 · Answer 2 · answered Nov 02 '15 at 09:47

0

Use Date_SUB With Interval of 7 Days

answered Nov 02 '15 at 09:47

Surekha

87
6

score 0 · Accepted Answer · answered Nov 03 '15 at 08:46

I solve the problem by :

select t1.dateid, t1.value, if(count(1)>=7,avg(t2.value),null) 
from mytable t1 , mytable t2
where t2.dateid between DATE_SUB(date(t1.dateid),INTERVAL 6 day)+0 and t1.dateid and 
      t1.dateid between 20150105 and 20150201
group by t1.dateid ,t1.value
order by dateid;

How to query avg for every past 7 days in sql, MySQL?

3 Answers3