SQL实战 8.异常的邮件概率 sum、count、round和case when结合

描述

现在有一个需求，让你统计正常用户发送给正常用户邮件失败的概率:
有一个邮件(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

 

 本文SQL题目来源于牛客网