MySQL实现移动平均计算

我们有这样的一个数据表test_sales,记录了每日的销售产品的销售数量数据。表中date表示销售日期,num表示销售数量,avg_5就是想要获得的5日移动平均销售量数据(这里直接列出是方便后期计算结果验证)。

Emmm,据说oracle有窗口函数可以直接出结果,体现出了付费产品的优势。Mysql则可以通过关联表本身,创造辅助列的办法来解决。

先看代码:

  1. SELECT t.date,t.num,t.avg_5,  
  2. case when t.date<‘2018-01-05’ then  else sum(t.avg_cnt)/count(1) end as avg_num  
  3. from   
  4. (select b.date,b.num,b.avg_5,a.num as avg_cnt from test_sales as a   
  5. right join test_sales b on b.date between a.date and date_add(a.date,interval 4 DAY)  
  6. order by b.date) t  
  7. group by t.date,t.num,t.avg_5  
  8. order by t.date;  

解释一下:

首先,看第4-6行,我们用test_sales关联自身,join的时候使用right join on between,可以理解为将a表的每一条数据都增加了4条记录,这四条记录的日期分别被加上了1/2/3/4天。此时,再使用right join时,从1月5日起,一条b表的记录都可以对应到5条a表的1月5日的记录。

接下来,对每一个b表的date而言,每天都会有对应5天内的avg_cnt数据。因此,按b.date对avg_cnt求均值即为五日移动平均值。

这是结果,avg_num为计算出的5日移动平均值,avg_5是原始数据,二者结果一致(由于保留小数位数问题有些不同)。

其中:case when t.date<‘2018-01-05’ then 是为了排除没有5日内移动均值的数据,因为数据从1月1日起,因此前4个日期是没有5日移动均值的。