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

-- 请统计国庆头3天里，每天的近7日日均订单完成量和日均订单取消量，按日期升序排序。结果保留2位小数。
with t1 as (select date(order_time) dt,sum(case when start_time is not null then 1 else 0 end) finish_num,sum(case when start_time is null then 1 else 0 end) cancel_num
from tb_get_car_record a 
left join 
tb_get_car_order b 
on a.uid = b.uid 
and a.order_id = b.order_id
where date(order_time) between '2021-09-25' and '2021-10-03'
group by date(order_time)),
t2 as (select dt,date_add(dt,interval -6 day) dt_curr from t1
where dt between '2021-10-01' and '2021-10-03')
select t2.dt,round(sum(finish_num)/7,2) finish_num_7d,
round(sum(cancel_num)/7,2) cancel_num_7d
from t1,t2
where datediff(t2.dt,t1.dt) between 0 and 6
group by t2.dt
order by t2.dt ASC