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

1、子查询的方法（实际干活能不用就不要用）效率低

select a.date, round(sum(a.type = 'no_completed')/count(*),3) as p from email a 
where a.send_id in (select id from user where is_blacklist = 0) 
and a.receive_id in (select id from user where is_blacklist = 0)
group by a.date order by a.date;

2、联结+case when

select a.date, 
round( 
    sum(case when a.type = 'no_completed' then 1 else 0 end)*1.0 / count(*)
    , 3) 
from email a 
join user b1 on (a.send_id = b1.id and b1.is_blacklist = 0)
join user b2 on (a.receive_id = b2.id and b2.is_blacklist = 0)
group by a.date 
order by a.date;