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

select 
    *
from(
    select 
        dt,
        round(avg(wc_cnt) over (order by dt asc rows between 6 preceding and current row),2) as finish_num_7d,
        round(avg(wwc_cnt) over (order by dt asc rows between 6 preceding and current row),2) as cancel_num_7d
    from(
        select 
            date(order_time) as dt,
            sum(if(mileage is not null,1,0)) as wc_cnt,
            sum(if(mileage is null,1,0)) as wwc_cnt
        from 
            tb_get_car_order
        group by 1
        order by dt) as a
    order by dt) as b
where dt between '2021-10-01' and '2021-10-03'