Java教程

SQL65 异常的邮件概率

本文主要是介绍SQL65 异常的邮件概率,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

 

条件

· 有一个邮件(email)表,id为主键, type是枚举类型,枚举成员为(completed,no_completed),completed代表邮件发送是成功的,no_completed代表邮件是发送失败的

· 用户(user)表,id为主键,is_blacklist为0代表为正常用户,is_blacklist为1代表为黑名单用户

 

要求

1、每一个日期里面,正常用户发送给正常用户邮件失败的概率

2、结果保留到小数点后面3位(3位之后的四舍五入)

3、按照日期升序排序

上面例子查询结果如下:

 

解析

根据题目要求,需要求正常用户发送到正常用户的失败率,所以先连接user表求出正常用户

select xxx
from email as e 
join user as u1 on e.send_id = u1.id and u1.is_blacklist = 0
join user as u2 on e.receive_id = u2.id and u2.is_blacklist = 0

然后求出失败的概率 = 失败的次数 / 总次数

总次数为count(e.type);失败的次数可以使用case..when..函数去计算,得sum (case when e.type = completed then 0 else 1 end)

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

联立其它条件可得

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

 

这篇关于SQL65 异常的邮件概率的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!