题解 | #国庆期间近7日日均取消订单量#
国庆期间近7日日均取消订单量
https://www.nowcoder.com/practice/2b330aa6cc994ec2a988704a078a0703?tpId=268&tags=&title=&difficulty=0&judgeStatus=0&rp=0&sourceUrl=%2Fexam%2Foj%3Fpage%3D1%26tab%3DSQL%25E7%25AF%2587%26topicId%3D268
select t1.pday, round( t1.pwc,2), round( t1.pqx,2) from( select t.pday, avg(t.wc)over(order by t.pday range interval 6 day PRECEDING) as pwc,#求每日完成单前7天的平均数 avg(t.qx)over(order by t.pday range interval 6 day PRECEDING) as pqx #求每日未完成单前7天平均数 from( select date(tr.order_time) as pday,#每日的订单日期 count(case when tr.start_time is not null then tr.order_id end) as wc,#求每日的完成订单的数量 sum(case when tr.start_time is null then 1 else 0 end) as qx #求每日取消订单 from tb_get_car_record td left join tb_get_car_order tr on td.uid=tr.uid and td.order_id=tr.order_id group by pday#分组每日订单信息 ) as t ) as t1 where t1.pday between "2021-10-01" and "2021-10-03"#提取2021-10-01到2021-10-03数据 order by t1.pday#进行排序