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

# 先求出国庆前三天各自的最近7天订单成交/取消量
# 要实现这个目标，首先要对每一行数据打个标记，标记他是成交还是取消订单。
# 再进一步，计算出每天的成交/取消量
with t1 as (
select date(order_time) dt,sum(case when start_time is not null then 1 else 0 end) finish_num,
sum(case when start_time is  null then 1 else 0 end) cancel_num
from tb_get_car_order
group by dt
)
select *
from (
    select dt,round(sum(finish_num) over(order by dt rows 6 preceding)/7,2) finish_order_num,
    round(sum(cancel_num) over(order by dt rows 6 preceding)/7,2) cancel_order_num
    from t1
    group by dt
) t2
where dt between '2021-10-01' and '2021-10-03'