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

select * from (
    select
        M.ot,
        round((sum(M.ct) over(order by M.ot rows 6 preceding)) / 7,2),
        round((sum(M.nt) over(order by M.ot rows 6 preceding)) / 7,2)
    from (
        select
            date_format(t.order_time, "%Y-%m-%d") ot,
            count(t.start_time) ct,
            sum(case when t.start_time is null then 1 else 0 end) nt
        from tb_get_car_order t
        group by date_format(t.order_time, "%Y-%m-%d")
    ) M
) N
where N.ot >= "2021-10-01" and N.ot <= "2021-10-03"