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

通过子查询选出不在黑名单的id，并限制receive_id，send_id在子查询结果中。然后用group by根据date分组汇总，选出字段date并且计算每组中type="no_completed"的比例，这里计算方法是在sum()中嵌套if(),通过if来获得将type="no_completed"的情况标为1，然后用sum累加算出type="no_completed"的个数，最后除以总数量的count获得比例，这里用round()将结果限制为三位小数。

select e.date, round(sum(if(type="no_completed", 1, 0))/count(type), 3)
from email as e, user as u
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
order by date