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

with
    temp as (
        select
            date(order_time) as order_time,
            count(start_time) as finish_num,
            sum(if(start_time is null, 1, 0)) as cancel_num
        from
            tb_get_car_record
            join tb_get_car_order using (order_id)
        where
            date(order_time) between '2021-09-25' and '2021-10-03'
        group by
            date(order_time)
        order by
            date(order_time)
    )
select
    dt,
    finish_num_7d,
    cancel_num_7d
from
    (
        select
            order_time as dt,
            round(
                sum(finish_num) over (
                    order by
                        order_time asc rows between 6 preceding
                        and current row
                ) / 7,
                2
            ) as finish_num_7d,
            round(
                sum(cancel_num) over (
                    order by
                        order_time asc rows between 6 preceding
                        and current row
                ) / 7,
                2
            ) as cancel_num_7d
        from
            temp
    ) as t1
where
    dt between '2021-10-01' and '2021-10-03'
order by
    dt