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

select 
dt,
round(finish_num_7d/7,2) finish_num_7d,
round(cancel_num_7d/7,2) cancel_num_7d
from 
(
	select
	*,
	sum(finish) over(order by dt range interval 7-1 day preceding) finish_num_7d,
	sum(cancel) over(order by dt range interval 7-1 day preceding) cancel_num_7d
	from 
	(
	    select
	    date(order_time) dt,
	    count(case when start_time is null then order_id end) cancel,
	    count(case when start_time is not null then order_id end) finish
	    from 
	    tb_get_car_order
	    group by date(order_time)
	) a
) b
where date_format(b.dt,'%Y-%m-%d') between '2021-10-01' and '2021-10-03'

1. 第一步：根据题目的提示，先计算出每天的订单取消和订单完成，取消和完成的判断根据题目理解就是start_time是否为null
2. 第二步：利用滑动窗口计算7天内的数据量的总和,这个时候不能加where条件，要用滑动窗口滑动一遍
3. 第三步：找到国庆前三天的数据并且求平均值

难点在于求7天内的数据量

sum(finish) over(order by dt range interval 7-1 day preceding) finish_num_7d