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

我的方法：n多个子查询，t1表：group by以后的失败次数，t2表：group by以后的总次数，join连接相除得到概率，子查询去除黑名单用户

with tmp as(
select * 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)
)
select t1.date,round(f/s,3)from (select date,count(id)f from tmp
where type='no_completed'
group by date)t1 join
(select date,count(id)s from tmp 
group by date
)t2 on t1.date=t2.date
order by t1.date

法二：sum+case when算失败次数,join去除黑名单用户

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

法三：sum直接算失败次数

select date, round(sum(type = "no_completed") / count(*), 3) as p
from email as t1
join user as t2 on t1.send_id = t2.id
join user as t3 on t1.receive_id = t3.id
where t2.is_blacklist = 0 and t3.is_blacklist = 0
group by date
order by date;