SQL实战 8.异常的邮件概率 sum、count、round和case when结合
描述
现在有一个需求,让你统计正常用户发送给正常用户邮件失败的概率:
有一个邮件(email)表,id为主键, type是枚举类型,枚举成员为(completed,no_completed),completed代表邮件发送是成功的,no_completed代表邮件是发送失败的。简况如下:
![](https://uploadfiles.nowcoder.com/images/20200817/557336_1597652115615_43081841018939871F6352EF230E6D8E)
第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代表为黑名单用户,简况如下:
![](https://uploadfiles.nowcoder.com/images/20200817/557336_1597652932880_7440E658C1F32DF28A6F4360EAB2D9BB)
第1行表示id为1的是正常用户;
第2行表示id为2的不是正常用户,是黑名单用户,如果发送大量邮件或者出现各种情况就会容易发送邮件失败的用户
。。。
第4行表示id为4的是正常用户
(注意: sqlite 1/2得到的不是0.5,得到的是0,只有1*1.0/2才会得到0.5,sqlite四舍五入的函数为round)
有一个邮件(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位之后的四舍五入),并且按照日期升序排序,上面例子查询结果如下:
结果表示:
2020-01-11失败的概率为0.500,因为email的第1条数据,发送的用户id为2是黑名单用户,所以不计入统计,正常用户发正常用户总共2次,但是失败了1次,所以概率是0.500;
2020-01-12没有失败的情况,所以概率为0.000.(注意: sqlite 1/2得到的不是0.5,得到的是0,只有1*1.0/2才会得到0.5,sqlite四舍五入的函数为round)
数据准备如下
drop table if exists email; drop table if exists user; CREATE TABLE `email` ( `id` int(4) NOT NULL, `send_id` int(4) NOT NULL, `receive_id` int(4) NOT NULL, `type` varchar(32) NOT NULL, `date` date NOT NULL, PRIMARY KEY (`id`)); CREATE TABLE `user` ( `id` int(4) NOT NULL, `is_blacklist` int(4) NOT NULL, PRIMARY KEY (`id`)); INSERT INTO email VALUES (1,3,2,'completed','2020-01-11'), (2,1,3,'completed','2020-01-11'), (3,1,4,'no_completed','2020-01-11'), (4,3,1,'no_completed','2020-01-12'), (5,3,4,'completed','2020-01-12'), (6,4,1,'completed','2020-01-12'), (7,4,3,'completed','2020-01-12'), (8,4,2,'completed','2020-01-12'), (9,4,1,'completed','2020-01-12'), (10,3,2,'completed','2020-01-13'), (11,4,1,'no_completed','2020-01-13'), (12,4,1,'no_completed','2020-01-13'), (13,1,3,'no_completed','2020-01-13'), (14,3,1,'no_completed','2020-01-11'), (15,3,1,'no_completed','2020-01-11'), (16,3,1,'no_completed','2020-01-11'), (17,3,1,'no_completed','2020-01-11'), (18,4,1,'completed','2020-01-12'), (19,4,1,'completed','2020-01-12'), (20,4,1,'completed','2020-01-12'), (21,1,3,'no_completed','2020-01-13'), (22,1,3,'no_completed','2020-01-13'), (23,1,3,'completed','2020-01-13'); INSERT INTO user VALUES (1,0), (2,1), (3,0), (4,0);
sql如下
SELECT DATE ,round(SUM(CASE WHEN TYPE='completed' THEN 0 ELSE 1 END)/COUNT(*), 3) AS p FROM ( SELECT a.*,b.is_blacklist AS send_black, c.is_blacklist AS receive_black FROM email a LEFT JOIN user b ON a.send_id=b.id LEFT JOIN user c ON a.receive_id=c.id ) a WHERE send_black='0' AND receive_black ='0' GROUP BY DATE order by DATE