首页 > 试题广场 >

异常的邮件概率

[编程题]异常的邮件概率
  • 热度指数：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
算法知识视频讲解
添加回答
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)
Mysql
雨季不再来__头像 雨季不再来__
SELECT date,round(count(type='no_completed' or null)/count(*),3)
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 date
order by date
发表于 2024-09-19 09:55:16 回复(0)
Mysql
知北之游头像 知北之游 
-- 合并标注收发邮件者的身份,用于下一步筛选
with t1 as (
select 
    e.id,
    send_id,
    receive_id,
    type,
    date,
    u1.is_blacklist as send_b,
    u2.is_blacklist as recei_b
from email as e left join user as u1 on e.send_id = u1.id
left join user as u2 on e.receive_id = u2.id
)

select 
    date,
    round(avg(if(type='completed',0,1)),3) p
from t1 
where send_b = 0 and recei_b=0 -- 选出白名单上的id
group by date
order by date


发表于 2024-08-28 03:36:53 回复(0)
Mysql
牛客175304302号头像 牛客175304302号 
# 将send_id和receive_id中的黑名单用户所在行筛除，仅保留所有正常用户行
with t1 as (
select e.*
from email e join user u1 on e.send_id = u1.id and u1.is_blacklist = 0 
             join user u2 on e.receive_id = u2.id and u2.is_blacklist = 0)

# 根据日期分组，计算每个日期内正常用户发送失败邮件数/每个日期内邮件总数，结果保留3位小数 
select date,
       round(sum(if(type="no_completed", 1, 0)) / count(1), 3) as p
from t1
group by 1
order by 1;

发表于 2024-06-13 17:46:26 回复(0)
Mysql
SummerSue头像 SummerSue 
-- 统计正常用户发送给正常用户失败的概率
select
    e.date,
    round(sum(if(e.type='no_completed',1,0))/count(*),3) p
from email e join user u1
on e.send_id=u1.id and u1.is_blacklist=0
join user u2
on e.receive_id=u2.id and u2.is_blacklist=0
group by e.date
order by e.date
发表于 2024-06-10 09:25:39 回复(0)
Mysql
Campunion头像 Campunion 
select
    date,
    round(sum(if (type = 'completed', 0, 1)) / count(*), 3)
from
    email e
where
    e.send_id in (
        select
            id
        from
            user
        where
            is_blacklist = 0
    )
    and e.receive_id in (
        select
            id
        from
            user
        where
            is_blacklist = 0
    )
group by
    date
order by
    date;

发表于 2024-05-28 18:51:28 回复(0)
Mysql
牛客882255995号头像 牛客882255995号
select e.date,
round(sum(case when is_blacklist=0 and type="no_completed" then 1 else 0 end)
/sum(case when is_blacklist=0 then 1 else 0 end),3) p
from email e
left join user u
on e.send_id=u.id 
group by date
order by date
这样做对吗大佬们
编辑于 2024-04-10 21:10:00 回复(0)
Mysql
昱乎昼头像 昱乎昼 
-- 莫名其妙自测运行就报错，提交就能过，啊
select
    e.date,
    round(avg(if (type = 'no_completed', 1, 0)), 3) 
from
    email e
    join user u1 on e.send_id = u1.id
    and u1.is_blacklist = 0
    join user u2 on e.receive_id = u2.id
    and u2.is_blacklist = 0
group by
    e.date
order by
    e.date;

发表于 2024-03-26 21:18:30 回复(0)
Mysql
牛客510724326号头像 牛客510724326号
with t1 as (select * from user where is_blacklist = 0)
,t2 as (select * from email where send_id in (select id from t1) and receive_id in (select id from t1))
select date,round(avg(if(type = "completed",0,1)),3) from t2  group by date order by date
发表于 2024-03-26 16:19:22 回复(0)
Mysql
培根执事头像 培根执事
先用WITH AS是最舒服的，毕竟这么多变量要调用。一个WITH AS一个CASE WHEN基本就能解决这个题目了，注意一点要left join就行了，两次连表删黑名单先 
with a as(
    select u.id sid, uu.id rid, e.date , case e.type when 'completed' then 1 else 0 end type1
    from email e 
        left join user u on e.send_id = u.id
        left join user uu on e.receive_id = uu.id
    where u.is_blacklist = 0 and uu.is_blacklist = 0
        )

select a.date,round(1-sum(type1)/count(type1),3)
from a
group by a.date
order by a.date
编辑于 2024-03-09 20:28:50 回复(0)
Mysql
注意脚下头像 注意脚下
先查出失败的次数，再查出发送的次数，再将两个表连接起来，利用ifnull函数将null变成0
select aa.date,ifnull(round(bb.ff/aa.hh,3),0) p
from
(select a.date,count(a.id) hh
from email a
left join user b
on a.receive_id=b.id
left join user c
on a.send_id=c.id
where   b.is_blacklist != '1'
   and c.is_blacklist != '1'
group by a.date) aa
left join
(select a.date,count(a.type) ff
from email a
left join user b
on a.receive_id=b.id
left join user c
on a.send_id=c.id
where a.type='no_completed'  and b.is_blacklist != '1'
   and c.is_blacklist != '1'
group by a.date
) bb
on aa.date=bb.date
order by bb.date asc
编辑于 2024-03-08 16:09:33 回复(0)
提交观点

问题信息

难度：
186条回答 4045浏览

热门推荐

通过挑战的用户

查看代码

相关试题

异常的邮件概率

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

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