题解 | 异常的邮件概率

select a.date, round(sum(a.cnt)/count(a.cnt),3) p from
(select date, case type when 'no_completed' then 1 else 0 end cnt from email
where send_id not in (select id from user where is_blacklist=1) and receive_id not in (select id from user
where is_blacklist=1)) a
group by a.date
order by a.date

一、(select id from user where is_blacklist=1) 其中这一步是选取出黑名单用户id，以确保发送人和收件人不在此名单里。

二、(select date, case type when 'no_completed' then 1 else 0 end cnt from email

where send_id not in (select id from user where is_blacklist=1) and receive_id not in (select id from user

where is_blacklist=1)) 给未发送成功和发送成功分别定义“1”，"0"