题解 | #国庆期间近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#进行排序

全部评论

相关推荐

ZywOo_求职版:谁问你了....
投递字节跳动等公司8个岗位
点赞 评论 收藏
分享
不愿透露姓名的神秘牛友
07-07 13:47
机械打工仔:你自己匿名可以,这么好的公司就别给它匿名了
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务