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

select
dt
,round(finish_num_7d,2) as finish_num_7d
,round(cancel_num_7d,2) as cancel_num_7d
from
    (select
    dt
    ,avg(s1)over(order by dt rows 6 preceding) as finish_num_7d
    ,avg(s2)over(order by dt rows 6 preceding) as cancel_num_7d
    from(select
        date(order_time) as dt
        ,sum(if(start_time is not null,1,0))  as s1
        ,sum(if(start_time is null,1,0)) as s2
        from tb_get_car_order
        group by 1 ) a)b
where dt between '2021-10-01' and '2021-10-03'
order by dt;