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

with dt_num as
(
select substr(order_time,1,10) as dt
,sum(case when start_time is null then 1 else 0 end) as cancel_num
,sum(case when start_time is not null then 1 else 0 end) as finish_num
from tb_get_car_order
group by substr(order_time,1,10)
)

select dt
,round(sum(finish_num)/count(before_dt),2) as finish_num_7d
,round(sum(cancel_num)/count(before_dt),2) as cancel_num_7d
from
(
    select a.dt as dt
    ,b.dt as before_dt
    ,b.cancel_num
    ,b.finish_num
    ,rank() over(partition by a.dt order by b.dt desc) as rk
    from dt_num a
    left join dt_num as b
    on b.dt<= a.dt 
)t
where rk>=1 and rk<=7
and dt>='2021-10-01' and dt <='2021-10-03'
group by dt