题解 | #国庆期间近7日日均取消订单量#

1.我们将问题简化，要求七日日均取消，只需要求出这七天每天的取消量，相加起来再除7即可。 因此我们先将每天的值求出

with t as 
(select date(order_time) as dt,count(start_time) as com,
count(order_time)-count(start_time) as ucom from  tb_get_car_order 
group by dt)

2.求累加和使用sum()over(...rows n preceding)即可

select dt,round(sum(com)over(order by dt rows 6 preceding)/7,2) as fn,
round(sum(ucom)over(order by dt rows 6 preceding)/7,2) as fm
from t

3.然后再取出国庆三天的结果即可。完整代码如下

with t as 
(select date(order_time) as dt,count(start_time) as com,
count(order_time)-count(start_time) as ucom from  tb_get_car_order 
group by dt)
select * from 
(select dt,round(sum(com)over(order by dt rows 6 preceding)/7,2) as fn,
round(sum(ucom)over(order by dt rows 6 preceding)/7,2) as fm
from t) as u 
where dt between '2021-10-01' and '2021-10-03'
order by dt

易错点：该题在日期的选择上一定要想清楚mysql的执行顺序，如果执行如下代码

select dt,round(sum(com)over(order by dt rows 6 preceding)/7,2) as fn,
round(sum(ucom)over(order by dt rows 6 preceding)/7,2) as fm
from t where between '2021-10-01' and '2021-10-03'
order by dt

则结果不正确。原因在于mysql中的where在select之前执行，如果先选择日期，那么除了这三天之外的其他记录其实都没有计算进来。因此我们需要再做一次子查询。