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

select dt,finish_num_7d,cancel_num_7d from (select dt,
    round((sum(com) over(order by dt rows 6 preceding))/7,2) as finish_num_7d,
    round((sum(del) over(order by dt rows 6 preceding))/7,2) as cancel_num_7d
from (select 
    date(order_time) as dt,
    count(start_time) as com,
    sum(if(start_time is null,1,0)) as del
from tb_get_car_order group by date(order_time)) t) p where dt between '2021-10-01' and '2021-10-03'