异常的邮件的概率题解
异常的邮件概率
http://www.nowcoder.com/questionTerminal/d6dd656483b545159d3aa89b4c26004e
要想到题目要求求的是 正常用户发送邮件失败的数目/正常用户发送邮件总数目，所以必然要连接user表排除掉黑名单用户,
所以第一时间写出来的是:
select xxx 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)这样就联立user表并且排除掉了所有黑名单用户，然后就是找到日期，和每个日期邮件发送的成功率,日期就select email.date就行了，但是成功率不太好写，我们这里用一个函数叫case....when ..then ...else ...end
就很简单了，如下:
sum(case email.type when'completed' then 0 else 1 end)这个代码的意思就是，当email.type为completed时，就为0，如果不为completed就为1，然后把这一列所有的和加起来，我们就得到了失败的发送邮件的数目，然后使用round函数保留后面3位:
round ( sum(case email.type when'completed' then 0 else 1 end)*1.0/count(email.type),3 )最后再按照日期分组，得到每一天的数据，并且按照日期升序排序:
group by email.date order by email.date;
select email.date, round( sum(case email.type when'completed' then 0 else 1 end)*1.0/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;
查看24道真题和解析
腾讯开奖369人在聊