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

题目要我们找到邮件异常的概率:

简单分析分为三个步骤

1. 找到那天所有发送的邮件总数 sum 表一
2. 找到那天所有发送失败的邮件总数 fal 表二
3. sum/fal 就是发送失败的概率,所以我们要把两张表连接起来

   ---

找到那天所有发送的邮件总数 sum 表一

select date,count(*) as su from email
where receive_id!=2 
and send_id!=2
group by date

找到那天所有发送失败的邮件总数 fal 表二

select date,count(*) as fa from email
where receive_id!=2 
and send_id!=2
and type='no_completed'
group by date

用内连接将两张表连接起来, 概率:round(b.fa/a.su,3) p

select a.date ,round(b.fa/a.su,3) p 
from
(select date,count(*) as su from email
where receive_id!=2 
and send_id!=2
group by date) a
,
(select date,count(*) as fa from email
where receive_id!=2 
and send_id!=2
and type='no_completed'
group by date) b
where a.date=b.date;