邮件发送失败的概率，除去发件人或收件人为黑名单的情况
异常的邮件概率
http://www.nowcoder.com/questionTerminal/d6dd656483b545159d3aa89b4c26004e
方法1（常规思路）：表1（日期，失败个数）表2（日期，邮件总数） ，最后用日期连接两表，分子除以分母计算概率。
select t2.date ,round(t1.c1/t2.c2,3)as p from (select date,count(id) as c1 from email where send_id in(select id from user where is_blacklist=0) and receive_id in(select id from user where is_blacklist=0) and type='no_completed' group by date )t1 join (select date,count(id) as c2 from email where send_id in(select id from user where is_blacklist=0) and receive_id in(select id from user where is_blacklist=0) group by date )t2 on t1.date=t2.date order by t2.date;方法2:sum+case方法计算失败次数，使用子查询过滤黑名单。
select e.date ,round(sum(case type when 'no_completed' then 1 else 0 end)/count(id),3) as p from email e where send_id in(select id from user where is_blacklist=0) and receive_id in(select id from user where is_blacklist=0) group by e.date order by e.date;方法3:sum+case方法计算失败次数,使用join过滤黑名单。同一个表可以join多次，取不同别名即可。
select e.date ,round(sum(case type when 'no_completed' then 1 else 0 end)/count(e.id),3) as p from email e join user u1 on e.send_id = u1.id join user u2 on u2.id = e.receive_id where u1.is_blacklist = 0 and u2.is_blacklist=0 group by e.date order by e.date;
投递BOSS直聘等公司10个岗位 >