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

首先在数据排除黑名单客户，在user表中找到黑名单

SELECT u.`id` from `user` u where is_blacklist=1

然后在date表中的where条件下排除,黑名单有可能是多个用户

where e.send_id !=(SELECT u.`id` from `user` u where is_blacklist=1) 
and   e.receive_id !=(SELECT u.`id` from `user` u where is_blacklist=1) 

按日期排列

group by e.date

用avg 和 case when then else 计算每组的平均值，即失败率，失败为1，成功为0。
失败率=sum（）/count（）正好和avg 一样

select e.date, 
       round(avg(case when e.type = 'completed' then 0
                      else 1 end),3)
from email e
where e.send_id !=(SELECT u.`id` from `user` u where is_blacklist=1) 
and   e.receive_id !=(SELECT u.`id` from `user` u where is_blacklist=1)
group by e.date