牛客MySQL：统计正常用户发送给正常用户邮件失败的概率:

题目描述

现在有一个需求，让你统计正常用户发送给正常用户邮件失败的概率:
有一个邮件(email)表，id为主键， type是枚举类型，枚举成员为(completed，no_completed)，completed代表邮件发送是成功的，no_completed代表邮件是发送失败的。简况如下:

第1行表示为id为2的用户在2020-01-11成功发送了一封邮件给了id为3的用户;
...
第3行表示为id为1的用户在2020-01-11没有成功发送一封邮件给了id为4的用户;
...
第6行表示为id为4的用户在2020-01-12成功发送了一封邮件给了id为1的用户;


下面是一个用户(user)表，id为主键，is_blacklist为0代表为正常用户，is_blacklist为1代表为黑名单用户，简况如下: 

第1行表示id为1的是正常用户;
第2行表示id为2的不是正常用户，是黑名单用户，如果发送大量邮件或者出现各种情况就会容易发送邮件失败的用户
。。。
第4行表示id为4的是正常用户
 

现在让你写一个sql查询，每一个日期里面，正常用户发送给正常用户邮件失败的概率是多少，结果保留到小数点后面3位(3位之后的四舍五入)，并且按照日期升序排序，上面例子查询结果如下:

 

---

题解:

要想到题目要求求的是  正常用户发送邮件失败的数目/正常用户发送邮件总数目，所以必然要连接user表排除掉黑名单用户,

所以第一时间写出来的是:

select xxx 
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)

这样就联立user表并且排除掉了所有黑名单用户，然后就是找到日期，和每个日期邮件发送的成功率,日期就select email.date就行了，但是成功率不太好写，我们这里用一个函数叫case....when ..then ...else ...end

就很简单了，如下:

sum(case email.type when'completed' then 0 else 1 end)
    

 这个代码的意思就是，当email.type为completed时，就为0，如果不为completed就为1，然后把这一列所有的和加起来，我们就得到了失败的发送邮件的数目，然后使用round函数保留后面3位:

round
(
    sum(case email.type when'completed' then 0 else 1 end)*1.0/count(email.type),3
) 

最后再按照日期分组，得到每一天的数据，并且按照日期升序排序:

 group by email.date order by email.date;

联立所有的sql得

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;

开始我没有想到使用case when,想得有点复杂，不过结果是是对的 

下面是自己开始想到的：

select 
  e.date,  FORMAT(count(e1.id)/count(e.id),3 )as p
 from email e 
 left join
 (select * from email where send_id in
                   (select id from user where is_blacklist =0 ) 
 and receive_id in (select id from user where is_blacklist =0 ) and
  type ='no_completed') e1
  on e.id = e1.id
  where e.send_id in
                   (select id from user where is_blacklist =0 ) 
 and e.receive_id in (select id from user where is_blacklist =0 )
 group by e.date