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

1.第一种方法 冗杂 但通用，

2.第二种方法倘若不存在10-1到3号某一天的日期则会失效，具体还需要利用union加上这三个日期，再利用sum开窗即可。具体问题具体分析，本题由于这三天都存在数据，故直接开窗即可

# select '2021-10-01',round(sum(ant1)/7,2),round(sum(ant2)/7,2)
# from(
# select date_format(event_time,'%Y-%m-%d') dat,count(start_time) ant1,count(*)-count(start_time) ant2
# from tb_get_car_record join tb_get_car_order using(uid,order_id)
# where date_format(event_time,'%Y-%m-%d')between '2021-09-25' and '2021-10-01'
# group by dat
#     )as T1
# union all 
# select '2021-10-02',round(sum(ant1)/7,2),round(sum(ant2)/7,2)
# from(
# select date_format(event_time,'%Y-%m-%d') dat,count(start_time) ant1,count(*)-count(start_time) ant2
# from tb_get_car_record join tb_get_car_order using(uid,order_id)
# where date_format(event_time,'%Y-%m-%d')between '2021-09-26' and '2021-10-02'
# group by dat
#     )as T1
# union all
# select '2021-10-03',round(sum(ant1)/7,2),round(sum(ant2)/7,2)
# from(
# select date_format(event_time,'%Y-%m-%d') dat,count(start_time) ant1,count(*)-count(start_time) ant2
# from tb_get_car_record join tb_get_car_order using(uid,order_id)
# where date_format(event_time,'%Y-%m-%d')between '2021-09-27' and '2021-10-03'
# group by dat
#     )as T1

select *
from(
select dat
,round(sum(ant1)over(order by dat asc rows between 6 preceding and current row )/7,2)
,round(sum(ant2)over(order by dat asc rows between 6 preceding and current row )/7,2)
from(
select date_format(event_time,'%Y-%m-%d') dat,count(start_time) ant1,count(*)-count(start_time) ant2
from tb_get_car_record join tb_get_car_order using(uid,order_id)
where date_format(event_time,'%Y-%m-%d') between '2021-09-25' and '2021-10-03'
group by dat
    )as T1
    )as T2
where dat between '2021-10-01'and '2021-10-03'