首页 > 试题广场 >

异常的邮件概率

[编程题]异常的邮件概率
  • 热度指数：153257 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 32M，其他语言64M
  • 算法知识视频讲解
现在有一个需求，让你统计正常用户发送给正常用户邮件失败的概率:
有一个邮件(email)表，id为主键， type是枚举类型，枚举成员为(completed，no_completed)，completed代表邮件发送是成功的，no_completed代表邮件是发送失败的。简况如下:
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
牛客306059284号头像 牛客306059284号
请问为什么sum 后面要乘以1.0？ 
round(
    sum(type = "no_completed")*1.0/count(*),3)as p

发表于 2022-08-10 15:29:09 回复(0)
Sqlite
潘伟靖头像 潘伟靖
还是这样好理解，要是连表，我都怀疑我是不是理解问题 
select date, round(count(case e.type when'no_completed' then 1 else null end) * 1.0 / count(e.type), 3) rate
from email e
where e.receive_id not in (select id from user where is_blacklist = 1)
and e.send_id not in (select id from user where is_blacklist = 1)
group by date
order by date;


发表于 2022-06-13 09:52:27 回复(0)
Sqlite
赵木枝头像 赵木枝
没有想出来用case 
select date, round(sum(case email.type when 'completed' then 0 else 1 end)*1.0/count(id),3)
from email
where send_id not in(select id from user where is_blacklist=1) 
     and receive_id not in(select id from user where is_blacklist=1)
group by date


发表于 2022-05-21 16:06:46 回复(0)
Sqlite
Achilles_a头像 Achilles_a 
//首先根据user表和email表筛选出send_id是正常用户的数据
with t1 as (
    select e.id, e.send_id, e.receive_id, e.type, e.date
    from email e left join user u on e.send_id = u.id
    where u.is_blacklist = 0
),
//在t1的前提下，同样筛选出receive_id为正常用户的数据
 t2 as (
     select t1.id, t1.send_id, t1.receive_id, t1.type, t1.date
     from t1 left join user u on t1.receive_id = u.id
     where u.is_blacklist = 0
 )
 //最后根据邮件类型计算失败的概率.
select t2.date, round(
     sum(case t2.type when 'completed' then 0 else 1 end)*1.0/count(t2.type),3 
) as p
from t2
group by t2.date
发表于 2021-12-11 10:39:53 回复(0)
Sqlite
牛客229523309号头像 牛客229523309号
请问为什么要*1.0呢？没有的话数学逻辑上是没问题的啊
发表于 2021-10-28 10:49:37 回复(0)
Sqlite
牛客768480990号头像 牛客768480990号 
select date,p 
from
(select date,type,count(*) over (partition by type)*1.0/count(*) as p
from email
where (send_id and receive_id) not in (select user.'id' from user where is_blacklist=1)) 
where type="no_completed"
group by date
order by date asc 
求大佬帮忙看看，用例1/4不通过
发表于 2021-10-18 15:30:57 回复(0)
Sqlite
李玉婷的旁听生头像 李玉婷的旁听生

1.理解题目最关键：发送&接受者都是正常用户 

SELECT DATE,
		round(COUNT(CASE WHEN e.type='no_completed' THEN e.type
		ELSE NULL END)/COUNT(*),3) AS p
FROM email e
where e.send_id not in (select u.id from user u where is_blacklist=1)
and e.receive_id not in (select u.id from user u where is_blacklist=1)
group by DATE
ORDER BY DATE asc

2、理解错误的答案： 
SELECT DATE,
		ROUND(COUNT(CASE WHEN e.type='no_completed' THEN e.type
		ELSE NULL END)/COUNT(*),3) AS p
FROM email e
LEFT JOIN USER AS u ON e.send_id=u.id AND u.is_blacklist=0
LEFT JOIN USER AS uu ON e.receive_id=uu.id AND uu.is_blacklist=0
GROUP BY DATE
ORDER BY DATE ASC






发表于 2021-10-09 22:10:13 回复(0)
Sqlite
牛客897730645号头像 牛客897730645号 
SELECT e.date, round(sum(case e.type when 'completed' then 0 else 1 end)*1.0/count(e.type),3) as p
FROM email AS e, user as u
where e.send_id=u.id
     AND e.receive_id=u.id
     AND u.is_blacklist=0
group by e.date 
ORDER BY e.date
请问我这个错在哪里了，一直通不过。
发表于 2021-10-08 22:54:56 回复(4)
Sqlite
高级资深攻城狮头像 高级资深攻城狮
select date,
(
    select count(*) from 
    (
        select date,type
        from email e  
        where e.send_id in
        (
            select id from user 
            where user.is_blacklist=0 
        )  
        and e.receive_id in 
        (
            select id from user 
            where user.is_blacklist=0 
        )
    )as n_u1
    where n_u1.date=normal_user.date and n_u1.type='no_completed'
)*1.0/

count(type)  
from
(
    select date,type
    from email e  
    where e.send_id in
    (
        select id from user 
        where user.is_blacklist=0 
    )  
    and e.receive_id in 
    (
        select id from user 
        where user.is_blacklist=0 
    )
    
)as normal_user
group by date

发表于 2021-09-29 10:21:01 回复(1)
Sqlite
阿里国际化内推头像 阿里国际化内推
select date, 
round(avg(case when type = "no_completed" then 1 else 0 end),3) as p
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
order by date asc 
发表于 2021-08-30 23:52:06 回复(0)
Sqlite
如果就这样头像 如果就这样
SELECT e.date,round(sum(
    case e.type 
    when 'completed'then 0 
    else 1 end)*1.0/count(e.type),3)AS p
FROM email 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 ASC
发表于 2021-08-20 17:23:58 回复(0)
Sqlite
牛客986949341号头像 牛客986949341号
搞人心态，自己的mysql里面可以正确输出，牛客就是不行
select
    tot.date,
    round(sum(tot.type)/count(tot.id),3) as p
from
(
    select 
        a.id,
        a.send_id,
        a.receive_id,
        case
            when a.type in ('no_completed') then 1
            else 0
        end as type,
        a.date,
        su.is_blacklist as send_black,
        ru.is_blacklist as receive_black
    from
        email as a 
    left join 
        user as su
    on
        a.send_id=su.id 
    left join 
        user as ru
    on
        a.receive_id=ru.id
    )as tot
where
    tot.send_black in (0)
    and tot.receive_black in (0)
group by 
    tot.date


发表于 2021-07-18 22:56:08 回复(0)
提交观点

问题信息

难度：
14条回答 4046浏览

热门推荐

通过挑战的用户

查看代码

相关试题

异常的邮件概率

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号