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

难点一：近7日订单完成量和取消量，参考牛客@盐咸咸同学的处理方式，分组后，使用窗口函数求和；

难点二：国庆头3天里，需要统计日均订单完成量和取消量之后，条件筛选头3天，切记不要基础数据中提前筛选头3天！

select dt,finish_num_7d,ancel_num_7d
from 
(select date(order_time) dt,
round(sum(sum(if(start_time is not null,1,0))) over(order by date(order_time) rows 6 preceding)/7,2) finish_num_7d,
round(sum(sum(if(start_time is null,1,0))) over(order by date(order_time) rows 6 preceding)/7,2) ancel_num_7d
from tb_get_car_order
group by date(order_time)
order by dt asc)t1
where dt between '2021-10-01' and '2021-10-03'
;