代码运行正确 | #国庆期间近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

1.首先构建中间表，统计每日的完成订单数，取消订单数。（选取是否有里程数作为订单判断标准）

2.通过中间表自连接，用where过滤数据为10-1——10.3日这三天的近7日数据，统计平均完成订单数/平均取消订单数

3.最后细节处理，输出结果小数位数处理和日期排序