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

with a as(
    select distinct date(order_time) dt,
    count(start_time) total_f_order,
    sum(if(isnull(start_time), 1,0)) total_n_order
    from tb_get_car_order
    group by date(order_time)
), 
b as(
    select dt ,
	# 所有包含n天内的数量聚合都适用
    round(avg(total_f_order) over(order by dt range interval 6 day preceding), 2) finish_num_7d,
    round(avg(total_n_order) over(order by dt range interval 6 day preceding),2 )cancel_num_7d
    from a
    order by dt
)
select * from b where dt between '2021-10-01' and '2021-10-03' 
order by dt