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

with a as (
select date(order_time) as dt
,sum(if(mileage >0,1,0)) as finish_cnt
,sum(if(mileage is null,1,0)) as cancel_cnt
# ,count(if(mileage is null,order_id,"")) as cancel_cnt 
# if的空也会计数，导致统计错误，所以不能这么使用
from tb_get_car_order
where date(order_time) between '2021-09-25' and '2021-10-03'
group by date(order_time)
)
select t1.dt as dt 
,round(avg(t2.finish_cnt),2) as finish_num_7d
,round(avg(t2.cancel_cnt),2) as cancel_num_7d
from a as t1,a as t2
where datediff(t1.dt,t2.dt) between 0 and 6  # 不可以用0<=datediff<=6
and t1.dt between '2021-10-01' and '2021-10-03'
group by t1.dt
order by dt