异常的邮件概率

id	send_id	receive_id	type	date
1 2 3 4 5 6	2 1 1 3 3 4	3 3 4 1 4 1	completed completed no_completed completed completed completed	2020-01-11 2020-01-11 2020-01-11 2020-01-12 2020-01-12 2020-01-12

第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为主键（注意这里id代表用户编号），is_blacklist为0代表为正常用户，is_blacklist为1代表为黑名单用户，简况如下:

id	is_blacklist
1 2 3 4	0 1 0 0

第1行表示id为1的是正常用户;
第2行表示id为2的不是正常用户，是黑名单用户，如果发送大量邮件或者出现各种情况就会容易发送邮件失败的用户
...
第4行表示id为4的是正常用户

现在让你写一个sql查询，每一个日期里面，正常用户发送给正常用户邮件失败的概率是多少，结果保留到小数点后面3位(3位之后的四舍五入)，并且按照日期升序排序，上面例子查询结果如下:

date	p
2020-01-11 2020-01-12	0.500 0.000

结果表示:

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)

示例1

输入:

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,2,3,'completed','2020-01-11'),
(2,1,3,'completed','2020-01-11'),
(3,1,4,'no_completed','2020-01-11'),
(4,3,1,'completed','2020-01-12'),
(5,3,4,'completed','2020-01-12'),
(6,4,1,'completed','2020-01-12');

INSERT INTO user VALUES
(1,0),
(2,1),
(3,0),
(4,0);

输出:

2020-01-11|0.500
2020-01-12|0.000

原站题解

上次编辑到这里，代码来自缓存点击恢复默认模板

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3364KB, 提交时间: 2021-09-11

select date, round(sum(type = "no_completed")*1.0 / count(*), 3) as p
from email as t1
    join user as t2 on t1.send_id = t2.id
    join user as t3 on t1.receive_id = t3.id
where t2.is_blacklist = 0 and t3.is_blacklist = 0
group by date
order by date;

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3456KB, 提交时间: 2021-11-22

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

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3456KB, 提交时间: 2021-09-10

select a.date,
       round(sum(case when a.type='no_completed' then 1 else 0 end)*1.0/count(a.type),3) as p
from email a
join user b on a.send_id=b.id
join user c on a.receive_id=c.id
where b.is_blacklist='0'
and c.is_blacklist='0'
group by 1
order by 1

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3492KB, 提交时间: 2021-09-07

SELECT date, ROUND(SUM(CASE WHEN type = 'completed' THEN 0 ELSE 1 END)*1.0/COUNT(*), 3)
FROM email e
WHERE send_id IN (SELECT id FROM user WHERE is_blacklist = 0)
    AND receive_id IN (SELECT id FROM user WHERE is_blacklist = 0)
GROUP BY date
ORDER BY date

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3496KB, 提交时间: 2021-09-10

select date,
round(sum(case when type="completed" then 0
    else 1 end
)*1.0/count(type),3)
as co from email
where 
send_id in(
select id from user where is_blacklist=0
)and receive_id in(
select id from user where is_blacklist=0
)group by date

上一题

下一题

详情