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

select 
    *
from 
(
select 
    dt 
    ,round(finish_num_7d,2) finish_num_7d
    ,round(cancel_num_7d,2) cancel_num_7d
from 
(
select 
    dt 
    ,avg(finish)over(rows between 6 preceding and current row) finish_num_7d
    ,avg(cancel)over(rows between 6 preceding and current row) cancel_num_7d
    
from 
(
    select 
    date(order_time) dt
    ,count(order_time)-sum(case when start_time is null then 1 else 0 end) finish
    ,sum(case when start_time is null then 1 else 0 end) cancel
from 
    tb_get_car_order
where 
    date(order_time) between '2021-09-25' and '2021-10-03'
group by 
    date(order_time)
    ) t0
group by 
    dt 
order by 
    dt desc
) t1 
limit 3
) t2 
order by 
    dt