我们有这样的一个数据表test_sales,记录了每日的销售产品的销售数量数据。表中date表示销售日期,num表示销售数量,avg_5就是想要获得的5日移动平均销售量数据(这里直接列出是方便后期计算结果验证)。
Emmm,据说oracle有窗口函数可以直接出结果,体现出了付费产品的优势。Mysql则可以通过关联表本身,创造辅助列的办法来解决。
先看代码:
-
SELECT t.date,t.num,t.avg_5,
-
case when t.date<‘2018-01-05’ then ” else sum(t.avg_cnt)/count(1) end as avg_num
-
from
-
(select b.date,b.num,b.avg_5,a.num as avg_cnt from test_sales as a
-
right join test_sales b on b.date between a.date and date_add(a.date,interval 4 DAY)
-
order by b.date) t
-
group by t.date,t.num,t.avg_5
-
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日移动均值的。