题解 | #国庆期间近7日日均取消订单量#
国庆期间近7日日均取消订单量
https://www.nowcoder.com/practice/2b330aa6cc994ec2a988704a078a0703
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窗口函数和窗口移动找到最近七天的订单总和,最后输出前三天所需的要求即可