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

with per_day as (
select 
date(order_time) order_time,
count(fare) finish,
sum(case when start_time is null then 1 end) cancel
from tb_get_car_order
group by date(order_time)
)

,7ds as (
select 
order_time
,round(sum(finish)over(order by order_time rows between 6 preceding and current row)/7,2) as finish_num_7d
,round(sum(cancel)over(order by order_time rows between 6 preceding and current row)/7,2) as cancel_num_7d
from per_day
)

select *
from 7ds
where order_time between '2021-10-01' and '2021-10-03'
order by order_time