这个问题与我之前写的一篇文章有关:MySQL:用一个月的偏移量从表中计算数据
但现在我需要构建一个过程,查询按周存储的联系人数据表。下面是我正在使用的表格的一个简化示例:
+-----------------+------------+
| week_start_date | contacts |
+-----------------+------------+
| 2015-03-08 | 12 |
| 2015-03-01 | 20 |
| 2015-02-22 | 5 |
| 2015-02-15 | 17 |
| 2015-02-08 | 8 |
| 2015-02-01 | 2 |
| 2015-01-25 | 16 |
| 2015-01-18 | 10 |
| 2015-01-11 | 4 |
| ... | ... |
+-----------------+------------+
我需要弄清楚的是如何计算一个4周移动平均线,它也有4周的偏移量。例如,如果我想获得2015年3月8日这一周的平均联系人,那就是1月18日至2月8日的平均联系人。在上面的例子中,我的平均值是:(10+16+2+8)/4=9。如果我想找到2015年3月1日这一周的平均值,那么使用上面的样本表,它将是1月11日至2月1日的平均值。
从我的上一篇文章中,我知道我可以通过在类似于以下的week_start_date将表与自己连接来处理4周的偏移:
SELECT s1.week_start_date, s2.Total_Contacts
FROM sample_table s1
LEFT JOIN (SELECT week_start_date, sum(contacts) AS Total_Contacts
FROM sample_table
GROUP BY week_start_date) s2
ON s1.week_start_date =
date_add(s2.week_start_date, INTERVAL 4 WEEK)
WHERE s1.week_start_date = '2015-03-08'
GROUP BY s1.week_start_date;
但让它来计算四周的平均值也是我陷入困境的地方。我原以为参加一系列的约会会奏效,但我得到的平均值比预期的要高得多。我猜这是由于week_start_date的分组方式。(请注意,每周可以有多条记录。为了减少混乱,我在样本表上每周只显示一条记录。)
在一个日期范围内加入是正确的方法吗?或者我需要在某个地方添加另一个联接吗?
谢谢你的帮助。
我建议使用相关的子查询:
select st.*,
(select avg(contacts)
from sample_table st2
where st2.week_start_date >= st.week_start_date - interval 7 * 7 days and
st2.week_start_date <= st.week_start_date - interval 4 * 7 days
) as avg_4week_delayed
from sample_table st;
我会使用DATE_SUB()函数,只减去所需的必要周数。因此,在3月8日的例子中,尝试这样的东西:
SELECT AVG(contacts)
FROM myTable
WHERE week_start_date <= DATE_SUB('2015-03-08', INTERVAL 4 WEEK) AND week_start_date >= DATE_SUB('2015-03-08', INTERVAL 7 WEEK);
它在SQL Fiddle中起作用。