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

with a as(select t1.uid,date(event_time)event_time,t1.order_id,driver_id,date(start_time)start_time,date(order_time)order_time
from tb_get_car_record t1 left join tb_get_car_order t2
on t1.order_id=t2.order_id
where order_time between '2021-09-25' and '2021-10-04'
),
b as(select order_time, count(start_time)finish_num,(count(*)-count(start_time))cancle_num
from a
group by order_time
order by order_time desc
)
select * from(
select distinct order_time,round(sum(finish_num)over( ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING)/7,2)as finish_num,round(sum(cancle_num)over( ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING)/7,2)as cancle_num
from b
group by order_time

limit 3)c
order by order_time asc