明确题意

• 统计国庆头3天
• 每天的近7日 日均订单完成量 和 日均订单取消量
• 按日期升序排序
• 结果保留2位小数

问题拆解

1. 统计出每天的订单完成量、订单取消量；生成子表 tb

• 对时间进行 GROUP BY
• 订单完成量：COUNT(start_time)
• 订单取消量：SUM(IF(start_time IS NULL,1,0))

2. 求近7天平均订单完成量、订单取消量；生成子表tb1

• 近7天日均订单完成量：AVG(order_done) over(ORDER BY dt ROWS 6 preceding)
• 近7天日均订单取消量：AVG(order_cancel) over(ORDER BY dt ROWS 6 preceding)

3. 国庆头三天近7天日均订单完成量、订单取消量

• 筛选出国庆前3天：WHERE dt BETWEEN '2021-10-01' AND '2021-10-03'
• 结果均保留2位小数：ROUND(X,2)
• 按日期升序排序:ORDER BY dt

代码

 SELECT 
	dt,
    ROUND(finish_num_7d,2) finish_num_7d,
    ROUND(cancel_num_7d,2) cancel_num_7d
 FROM (
	  SELECT 
		dt,
		AVG(order_done) over(ORDER BY dt ROWS 6 preceding) finish_num_7d,
		AVG(order_cancel) over(ORDER BY dt ROWS 6 preceding) cancel_num_7d
	 FROM (
		  SELECT 
			DATE(order_time) dt, 
			COUNT(start_time) order_done,  -- 订单完成量
			SUM(IF(start_time IS NULL,1,0)) order_cancel -- 订单取消量
		 FROM tb_get_car_order gco
		 GROUP BY DATE(order_time)
         ) tb
 ) tb1
WHERE dt BETWEEN '2021-10-01' AND '2021-10-03'
ORDER BY dt