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

with t1 as(
select date(order_time) as dt,count(start_time) finish,
count(order_time)-count(start_time) as cancel
from tb_get_car_order
group by date(order_time)
),

t2 as(
select *,
round(sum(finish)over(order by dt rows 6 preceding)/7,2) finish_num_7d,
round(sum(cancel)over (order by dt rows 6 preceding)/7,2) cancel_num_7d from t1 )

select dt,finish_num_7d,cancel_num_7d
from t2 where dt between '2021-10-01' and '2021-10-03'