SQL65 异常的邮件概率
2021/9/11 19:07:05
本文主要是介绍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 异常的邮件概率的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-05-15鸿蒙生态设备数量超8亿台
- 2024-05-13TiDB + ES:转转业财系统亿级数据存储优化实践
- 2024-05-09“2024鸿蒙零基础快速实战-仿抖音App开发(ArkTS版)”实战课程已上线
- 2024-05-09聊聊如何通过arthas-tunnel-server来远程管理所有需要arthas监控的应用
- 2024-05-09log4j2这么配就对了
- 2024-05-09nginx修改Content-Type
- 2024-05-09Redis多数据源,看这篇就够了
- 2024-05-09Google Chrome驱动程序 124.0.6367.62(正式版本)去哪下载?
- 2024-05-09有没有大佬知道这种数据应该怎么抓取呀?
- 2024-05-09这种运行结果里的10.100000001,怎么能最快改成10.1?