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

with a as (select  sum(case when start_time is not null then 1 else 0 end) finish_day,
        sum(case when start_time is null then 1 else 0 end) cancel_day,
        date(order_time) as dt
from tb_get_car_order
where DATE(order_time) BETWEEN '2021-09-25' AND '2021-10-03'
group by date(order_time))

select  dt,
        round(sum(finish_day)over(order by dt rows between  6 preceding and current row)/7,2),
        round(sum(cancel_day)over(order by dt rows between  6 preceding and current row)/7,2)
from a 
limit 6, 3

步骤一：计算每天完成与取消的订单，通过date() between 形成每日的订单序列

步骤二：利用over(rows between)锁定当前行并向前提取六行，sum求和，取10-01到10-03