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

with
    t1 as(
        #1、获取最近三日日期
        select 
           distinct
           date(finish_time) `dt`
        from tb_get_car_order
        where date(finish_time) between '2021-10-01' and '2021-10-03' 
    ),
    t2 as(
        #2、获取订单完成(flag=1)和取消订单(flag=0)的记录
        select 
            tr.order_id,
            finish_time `dt`,
            if(start_time is null,0,1) `flag`
        from tb_get_car_record tr
        join tb_get_car_order `to`
        on tr.order_id=to.order_id
    )


select 
    t1.dt,
    round(sum(if(flag=1,1,0))/7,2) `finish_num_7d`,
    round(sum(if(flag=0,1,0))/7,2) `cancel_num_7d`
from t1
left join t2
on datediff(t1.dt,t2.dt) between 0 and 6
group by t1.dt
order by t1.dt