select t.order_time, round(sum(if(tb_get_car_order.start_time is null,0,1))/7,2)as finish_num_7d, round(sum(if(tb_get_car_order.start_time is null,1,0))/7,2) as cancel_num_7d from (select date(order_time) as order_time, count(start_time) as finishi_cnt, sum(if(start_time is null,1,0)) as unfinishi_cnt from tb_get_car_order group by date(order_time)) as t left outer join tb_get_car_order on datediff(t.order_time,date(tb_get_car_order.order_time)) BETWEEN 0 and 6 where t.order_time BETWEEN '2021-10-01' and '2021-10-03' group by t.order_time;