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

# 知识点：
# sum() over(order by dt rows 6 preceding) 用于计算按照日期升序排列后，前6行的总和
select * from (
select t2.dt
,round(sum(sum_finish) over(order by t2.dt rows 6 preceding)/7,2) as sum_finish_7
,round(sum(sum_cancel) over(order by t2.dt rows 6 preceding)/7,2) as sum_cancel_7 
from (
select date_format(t1.order_time,'%Y-%m-%d') as dt 
,sum(case when t1.start_time is not null then 1 else 0 end) as sum_finish
,sum(case when t1.start_time is null then 1 else 0 end) as sum_cancel 
from tb_get_car_order as t1 
group by date_format(t1.order_time,'%Y-%m-%d')) t2 ) t3 
where t3.dt between '2021-10-01' and '2021-10-03'