题解 | #异常的邮件概率#

异常的邮件概率

http://www.nowcoder.com/practice/d6dd656483b545159d3aa89b4c26004e

1、子查询的方法(实际干活能不用就不要用)效率低

select a.date, round(sum(a.type = 'no_completed')/count(*),3) as p from email a 
where a.send_id in (select id from user where is_blacklist = 0) 
and a.receive_id in (select id from user where is_blacklist = 0)
group by a.date order by a.date;

2、联结+case when

select a.date, 
round( 
    sum(case when a.type = 'no_completed' then 1 else 0 end)*1.0 / count(*)
    , 3) 
from email a 
join user b1 on (a.send_id = b1.id and b1.is_blacklist = 0)
join user b2 on (a.receive_id = b2.id and b2.is_blacklist = 0)
group by a.date 
order by a.date;
全部评论

相关推荐

06-10 21:15
门头沟学院 Java
宁阿:好多这种没🧠的公司,他们估计都不知道毕业的人不能给安排实习岗
实习吐槽大会
点赞 评论 收藏
分享
自学java狠狠赚一...:骗你点star的,港卵公司,记得把star收回去
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

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