题解 | #异常的邮件概率#
异常的邮件概率
http://www.nowcoder.com/practice/d6dd656483b545159d3aa89b4c26004e
1.分析
方法一:
利用case when 对状态进行赋值, completed为0, no_completed为1,然后利用sum函数把它俩加起来,就是发送失败的次数;利用count函数对所有状态进行计数,就是发送邮件的总次数。
发送失败的次数 / 发送邮件的总次数 = 发送邮件失败的概率
round函数取概率值3位小数
方法二:
sum直接可以计算出状态值的总次数
select email.date, round( sum(case email.type when'completed' then 0 else 1 end) / count(email.type),3 ) as p from email join user as u1 on (email.send_id=u1.id and u1.is_blacklist=0) join user as u2 on (email.receive_id=u2.id and u2.is_blacklist=0) group by email.date order by email.date; select date, round(sum(type = "no_completed") / count(*), 3) as p from email as t1 join user as t2 on t1.send_id = t2.id join user as t3 on t1.receive_id = t3.id where t2.is_blacklist = 0 and t3.is_blacklist = 0 group by date order by date;