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

with a as(
    select date(order_time) od,
    count(start_time) as deal,
    count(order_id)-count(start_time) nodeal
    from tb_get_car_order t1
    group by od
    order by od),
b 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')
select dt,
(select round(sum(deal)/count(od),2) from a where od between DATE_ADD(dt,interval -6 day) and dt) as finish_num_7d,
(select round(sum(nodeal)/count(od),2) from a where od between DATE_ADD(dt,interval -6 day) and dt) as cancel_num_7d
from b