题解 | #异常的邮件概率#
异常的邮件概率
https://www.nowcoder.com/practice/d6dd656483b545159d3aa89b4c26004e
第一步：left join on 连接用户表，连接两次，筛选出发送用户和接收用户都不是黑名单的正常用户。
筛选条件: select *
from email left join user as t on
email.send_id=t.id #发送id等于用户id
from email left join user as t on
email.send_id=t.id #发送id等于用户id
left join user as t1 on
email.receive_id=t1.id #接收id=用户id
email.receive_id=t1.id #接收id=用户id
where t.is_blacklist=0 and t1.is_blacklist=0;
第二步：因为count函数只能计算整个一列的数据，但是我们只需要筛选出
没有发送成功的邮件数，所以我们用case when then end语句给未发送成功的用户赋值为1，对1求和。
sum(case when type='no_completed' then 1 else 0 end)
总发送量：count(type)
小数点后3位四舍五入：round(值，保留几位小数）
函数嵌套：round(sum(case when type='no_completed' then 1
else 0 end )/count(type),3) as p
else 0 end )/count(type),3) as p
第三步：对日期进行分组和排序
group by date order by date asc
这里的题目要求是求出每天的邮件发送失败率，所以要对日期进行分组。
完整的代码：select date,round(sum(case when type='no_completed' then 1
else 0 end )/count(type),3) as p
from email left join user as t on
email.send_id=t.id left join user as t1 on
email.receive_id=t1.id where t.is_blacklist=0 and t1.is_blacklist=0
group by date order by date asc;
#解题#else 0 end )/count(type),3) as p
from email left join user as t on
email.send_id=t.id left join user as t1 on
email.receive_id=t1.id where t.is_blacklist=0 and t1.is_blacklist=0
group by date order by date asc;
查看6道真题和解析