题解 | #国庆期间近7日日均取消订单量#
国庆期间近7日日均取消订单量
https://www.nowcoder.com/practice/2b330aa6cc994ec2a988704a078a0703
select * from (select order_time, round( sum(finish_order_num) over ( order by order_time rows 6 preceding ) / 7, 2 ) as finish_rate, round( sum(unfinish_num) over ( order by order_time rows 6 preceding ) / 7, 2 ) as unfinish_rate from ( select date (order_time) as order_time, sum(if (fare is not null, 1, 0)) as finish_order_num, sum(if (fare is null, 1, 0)) as unfinish_num from tb_get_car_order as tgco left join tb_get_car_record as tgcr on tgcr.order_id = tgco.order_id group by date (order_time) ) as t1) as t2 where order_time between '2021-10-1' and '2021-10-3'
先算完成订单数,未完成订单数,并按照天来分类
计算每天的完成率,未完成率,使用窗口函数计算preceding获取当前行前n行
sum(unfinish_num) over ( order by order_time rows 6 preceding )
选择1号到3号