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

自己第一次做的：

select t1.date,round(t2.n2/t1.n1,3)
from
(
    select date,count(*) n1 from email e  
    join user u1 on e.send_id=u1.id 
    join user u2 on e.receive_id=u2.id
    where u1.is_blacklist=0 and u2.is_blacklist=0
    group by date
) t1 join
(
    select date,count(*) n2 from email e  
    join user u1 on e.send_id=u1.id 
    join user u2 on e.receive_id=u2.id
    where u1.is_blacklist=0 and u2.is_blacklist=0
    and type='no_completed'
    group by date
) t2 on t1.date=t2.date
group by date
order by date

参考别人后写的：

select 
e.date
,round(sum(if(e.type='completed',0,1))/count(e.type),3) p
from email e  
join user u1 on e.send_id=u1.id 
join user u2 on e.receive_id=u2.id
where u1.is_blacklist=0 and u2.is_blacklist=0
group by date
order by date

思路：上面那个就是分别算，但是很多相同的条件写了俩次，下面的话就相当于把相同的步骤省略了，sum 函数里面嵌套if 或者case when，一半一个条件我都是用if ，如果多个的话采用case when。