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

先挑选出国庆前三天的日期，

再选出7日内的所有订单完成记录、取消记录，两个表左连接会得到国庆前三天每一天的近七日记录，进行平均即可

select a.dt,
	   round(sum(订单完成量)/count(b.dt),2) as 日均订单完成量,
       round(sum(订单取消量)/count(b.dt),2) as 日均订单取消量
       from
(select distinct(date(order_time)) as dt from tb_get_car_order
       where date(order_time) between '2021-10-01' and '2021-10-03') as a
       left join
       (select date(order_time) as dt,
               count(case when start_time is not null then date(order_time) end) as 订单完成量,
               count(case when start_time is null then date(order_time) end) as 订单取消量
               from tb_get_car_order
               group by date(order_time)) b
               on datediff(a.dt,b.dt) between 0 and 6
               group by a.dt;