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

select *
from(
select order_day,
round(sum(order_num)over(order by order_day rows 6 preceding)/7,2) as finish_num_7d,
round(sum(cancel_order)over(order by order_day rows 6 preceding)/7,2) as cancel_num_7d
from(
select date(order_time)as order_day,
count(case when start_time is null then 1 else null end) as cancel_order,
count(case when start_time is not null then 1 else null end) as order_num
from tb_get_car_order
where datediff('2021-10-03',date(order_time)) between 0 and 8
group by date(order_time)
order by date(order_time)) as t1) as t2
where order_day between '2021-10-01' and '2021-10-03'

先统计每天的订单数量和取消订单的数量，然后使用sum窗口函数和窗口移动找到最近七天的订单总和，最后输出前三天所需的要求即可