首页 > 试题广场 >

异常的邮件概率

[编程题]异常的邮件概率
  • 热度指数：162638 时间限制：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 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

第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 0
2 1
3 0
4 0

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

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

date p
2020-01-11 0.500
2020-01-12 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
算法知识视频讲解
添加回答
Mysql
jacky酱头像 jacky酱
select
        a.date,
        round((case
            when sum(a.p1)+sum(a.p2) = 0 then  0
            else sum(a.p2)/(sum(a.p1)+sum(a.p2)) end ),3) as p
    from (
    select
        e.date,
        if(e.type = 'completed',1,0) as p1,
        if(e.type = 'no_completed',1,0) as p2
    from email e
    join user sender on e.send_id = sender.id
    join user receive on e.receive_id = receive.id
    where sender.is_blacklist <> 1 and receive.is_blacklist <> 1
    ) as a
group by a.date
order by a.date;

发表于 2025-07-01 18:23:15 回复(0)
Mysql
求职iing头像 求职iing
使用sum(if(type='no_completed',1,0))来统计发送失败个数 
select date,
    round(sum(if(type='no_completed',1,0))/count(id),3) as p
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
order by date


发表于 2025-06-28 17:10:41 回复(0)
Mysql
清影LC头像 清影LC
select date, round(sum(case when type = 'completed' then 0 else 1 end) / count(type), 3) as p
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
order by date;
发表于 2025-06-03 15:45:05 回复(0)
Mysql
胖咪是个宝头像 胖咪是个宝
select date,
round(sum(if(type = 'no_completed', 1, 0)) / count(*), 3) p
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
order by date;
发表于 2025-05-16 21:31:47 回复(0)
Mysql
halosun头像 halosun 
select
    date
    ,round(avg(if(type = 'no_completed',1,0)),3) as p
from    
    email e
    inner join user u on e.id = u.id
where
    is_blacklist = 0
group by
    date
order by
    date
;

发表于 2025-04-12 15:22:16 回复(0)
Mysql
梁智豪头像 梁智豪
select e.date,round(sum(if(type='no_completed',1,0))/count(type),3) from email as e ,user as u where e.send_id=u.id and u.is_blacklist<>1 group by e.date

发表于 2025-04-09 15:23:33 回复(0)
Mysql
海边的微风头像 海边的微风 
select date, round(sum(if(type="no_completed",1,0))/count(*),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

发表于 2025-03-11 18:04:29 回复(0)
Mysql
勇敢妞妞不怕困难2号头像 勇敢妞妞不怕困难2号
with res as (select
date,
count(*) as total_cnt,
sum(case when type='no_completed' then 1 else 0 end) as suc_cnt
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)

select
date,
round(suc_cnt/total_cnt,3) from res
order by date asc

发表于 2025-03-03 18:57:34 回复(0)
Mysql
牛客360950828号头像 牛客360950828号
用 case when 和 not in 子查询
select date,
round(sum(case when type="completed" then 0 else 1 end)/count(type),3) as P
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
发表于 2025-02-19 15:20:16 回复(0)
Mysql
牛客68947020号头像 牛客68947020号 
select a.date	
,round(sum(if(type='completed',0,1))/count(type),3) p
from email a 
join user b on a.send_id=b.id and b.is_blacklist=0
join user c on a.receive_id=c.id and c.is_blacklist=0
GROUP BY a.date
order by a.date

发表于 2025-01-16 16:49:34 回复(0)
Mysql
刷牛客的coder很爱吃香菜头像 刷牛客的coder很爱吃香菜 
select
    e.date,
    round(
        sum(
            case
                e.type
                when 'no_completed' then 1
                else 0
            end
        ) / count(e.type),
        3
    ) as p
from
    email e
    inner join user u on e.send_id = u.id
    join user u2 on e.receive_id = u2.id
where
    u.is_blacklist <> 1
    and u2.is_blacklist <> 1
group by
    e.date

发表于 2025-01-07 17:11:00 回复(0)
Mysql
青衣白马少年时头像 青衣白马少年时 
select distinct date,
round(avg(case when type = 'no_completed' then 1 else 0  end) over(partition by date),3) f
from email e
join user u on e.send_id = u.id
join user u2 on e.receive_id = u2.id
where u.is_blacklist = 0 and u2.is_blacklist = 0
本来想用group by 进行分类，但是逻辑理不清，最后只能投机取巧用distinct
发表于 2025-01-05 10:41:12 回复(1)
Mysql
炎煬头像 炎煬
with t1 as (select send_id,receive_id,type,date
from email a
left join user b
on a.send_id=b.id
left join user c
on a.receive_id=c.id
where b.is_blacklist !=1 and c.is_blacklist !=1)
select date,round(sum(if(type="no_completed",1,0))/count(type),3) as p
from t1
group by date
order by 1 
发表于 2024-12-01 14:43:53 回复(1)
Mysql
快门回溯X头像 快门回溯X
with t1 as
(
select
date,type,send_id,receive_id
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)
)

select
date
,round((sum(if(type='no_completed',1,0))/count(1)),3) p
from t1
group by date
order by date
发表于 2024-12-01 10:52:28 回复(0)
Mysql
冲22头像 冲22
select date,round(sum(case when type='no_completed' then 1 else 0 end )/count(*),3) p
from email left join user on email.send_id=user.id where is_blacklist=0  group by 1    大佬们这样可以嘛
发表于 2024-11-12 16:20:30 回复(0)
Mysql
咦哇啦头像 咦哇啦 
select date,round(avg(type='no_completed'),3) p
from email e
join user u1
on e.send_id=u1.id
join user u2
on e.receive_id=u2.id
where u1.is_blacklist=0
and  u2.is_blacklist=0
group by 1
order by 1

发表于 2024-11-11 00:14:16 回复(0)
Mysql
牛客440842856号头像 牛客440842856号
#首先，将email表与用户表内联结，联结字段为send_id和id，筛出正常发送用户
#在此基础上再联结user表，联结字段为receiveid和id，筛出同时是正常收发用户
#在此基础上按日期分组统计type的数量作为分母，分子用sum（case）语句组合，即能算出概率
#最后对概率四舍五入，再按照日期升序排序即可
select t.date,
round(sum(case when type='no_completed' then 1 else 0 end)/count(type),3) as p from
(select type,date from email as e
inner join user as us on e.send_id=us.id and us.is_blacklist=0
inner join user as ur on e.receive_id=ur.id and ur.is_blacklist=0) as t
group by t.date order by t.date;
发表于 2024-10-19 17:28:10 回复(0)
Mysql
牛客303380887号头像 牛客303380887号
自测可以用简单的表连接做出来 
select date
, round(
    sum(
        case when type = 'no_completed' then 1 else 0 
        end
        ) / count(date)
  ,3)
from
email a join user b on a.id = b.id
where b.is_blacklist = 0
group by date


发表于 2024-10-15 19:52:20 回复(0)
Mysql
不是秃头头像 不是秃头
select
    date,
    round((num2/num1),3) as p
from
    (select date,count(id) as num1,
    sum(case when type ='no_completed' then 1 else 0 end) as num2
    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)u1
order by
    date
嘻嘻，纪念困难级自己独立一遍想通关，加油！
发表于 2024-09-26 15:03:40 回复(0)
Mysql
yyyyyyyyui头像 yyyyyyyyui
select
    date,
    round(avg(type = 'no_completed'), 3) as p
from
    email e
    left join user u on e.send_id = u.id
    left join user us on e.receive_id = us.id
where
    u.is_blacklist = 0
    and us.is_blacklist = 0
group by
    date
order by
    date

发表于 2024-09-19 23:41:36 回复(0)
提交观点

问题信息

SQL
难度：
200条回答 4412浏览

热门推荐

通过挑战的用户

查看代码

相关试题

异常的邮件概率

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

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