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

#先查出要的日期
with t1 as(
select distinct date(order_time) as dt
from tb_get_car_order
where date(order_time) between '2021-10-01' and '2021-10-03'),

#查询要的字段
t2 as(
select 
date(order_time) as dt,
count(fare) as finish_num,
count(case when fare is null then 1 else null end) as cancel_num

from tb_get_car_order
group by date(order_time))

#主查询
select t1.dt,
round(avg(finish_num),2) as finish_num_7d,
round(avg(cancel_num),2) as cancel_num_7d
from t1 
left join t2
on datediff(t1.dt,t2.dt) between 0 and 6
group by t1.dt

之前做过类似的题