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

# select sum(case when b.dt between DATE_SUB(b.dt,INTERVAL 7 DAY) and b.dt then cancel_num else 0 end) cancel_num_7d,b.dt
select '2021-10-01',round(sum(finish_num)/7,2),round(sum(cancel_num)/7,2) from(
select b.dt,(case when b.dt between DATE_SUB('2021-10-01',INTERVAL 6 DAY) and '2021-10-01' then 1 end) flag_1,(case when b.dt between DATE_SUB('2021-10-02',INTERVAL 6 DAY) and '2021-10-02' then 1 end) flag_2,(case when b.dt between DATE_SUB('2021-10-03',INTERVAL 6 DAY) and '2021-10-03' then 1 end) flag_3,cancel_num,finish_num   from(
select sum(case when start_time is null then 1 else 0 end) cancel_num,sum(case when start_time is not null then 1 else 0 end) finish_num,a.dt dt from(
select *,date_format(order_time,'%Y-%m-%d') dt from tb_get_car_order
)a group by a.dt 
)b
)c where flag_1 = 1
union
select '2021-10-02',round(sum(finish_num)/7,2),round(sum(cancel_num)/7,2) from(
select b.dt,(case when b.dt between DATE_SUB('2021-10-01',INTERVAL 6 DAY) and '2021-10-01' then 1 end) flag_1,(case when b.dt between DATE_SUB('2021-10-02',INTERVAL 6 DAY) and '2021-10-02' then 1 end) flag_2,(case when b.dt between DATE_SUB('2021-10-03',INTERVAL 6 DAY) and '2021-10-03' then 1 end) flag_3,cancel_num,finish_num   from(
select sum(case when start_time is null then 1 else 0 end) cancel_num,sum(case when start_time is not null then 1 else 0 end) finish_num,a.dt dt from(
select *,date_format(order_time,'%Y-%m-%d') dt from tb_get_car_order
)a group by a.dt 
)b
)c where flag_2 = 1
union
select '2021-10-03',round(sum(finish_num)/7,2),round(sum(cancel_num)/7,2) from(
select b.dt,(case when b.dt between DATE_SUB('2021-10-01',INTERVAL 6 DAY) and '2021-10-01' then 1 end) flag_1,(case when b.dt between DATE_SUB('2021-10-02',INTERVAL 6 DAY) and '2021-10-02' then 1 end) flag_2,(case when b.dt between DATE_SUB('2021-10-03',INTERVAL 6 DAY) and '2021-10-03' then 1 end) flag_3,cancel_num,finish_num   from(
select sum(case when start_time is null then 1 else 0 end) cancel_num,sum(case when start_time is not null then 1 else 0 end) finish_num,a.dt dt from(
select *,date_format(order_time,'%Y-%m-%d') dt from tb_get_car_order
)a group by a.dt 
)b
)c where flag_3 = 1 

把我逼疯了有什么好处？（恼）