首页 > 试题广场 >

异常的邮件概率

[编程题]异常的邮件概率

热度指数：153120 时间限制：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

牛客200725243号

先把黑名单id剔除，然后这样，再这样，最后那样。

select rt.date,round(sum(if(rt.type='no_completed',1,0))/count(*),3)

from (select a.*
from (select e.* from email e left join user u
on e.send_id=u.id
where u.is_blacklist!=1) a left join user u
on a.receive_id=u.id where u.is_blacklist!=1) rt
group by rt.date order by rt.date

发表于 2022-07-21 23:34:46 回复(0)

牛客944544689号

高天宇写法

select

date,1.0-(stypes*1.0/alls*1.0)
from(select
date,count(*) alls,sum(types) stypes
from (select
*,
case
when type='completed' then 1
else 0
end as types
from (select
*
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))t)t1
group by date

)

发表于 2022-06-13 20:41:17 回复(0)

Mysql

Ron1997

select
t.date,round(count(case when type="no_completed" then type else null end)/ count(type),3) as p
from(
select
e.id,e.type,e.date
from email e
left join user u1 on e.send_id=u1.id
left join user u2 on e.receive_id=u2.id
where u1.is_blacklist=0 and u2.is_blacklist=0)t
group by t.date
order by date

发表于 2022-03-19 16:45:07 回复(0)

Sqlite

牛客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)

cedricd

SELECT date
,ROUND(SUM(CASE WHEN e.type = 'no_completed' THEN 1 ELSE 0 END)/COUNT(date),3) gl
FROM email e LEFT JOIN user u1 ON e.send_id = u1.id LEFT 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

CASE WHEN 的用法在这很好用。然后这里使用了LEFT JOIN，把最后的结果放到了最后WHERE里做了筛选。

发表于 2021-04-23 23:25:12 回复(0)

CrackerJack

SELECT date, ROUND(1*(SUM(CASE type WHEN 'no_completed' THEN 1 ELSE 0 END)/COUNT(type)),3)
FROM email e
INNER JOIN user u1 ON u1.id = e.send_id 
JOIN user u2 ON u2.id = e.receive_id
WHERE u1.is_blacklist = 0 AND u2.is_blacklist = 0
GROUP BY date
ORDER BY date

分解需求：每一个日期里面，正常用户发送给正常用户邮件失败的概率是多少，结果保留到小数点后面3位(3位之后的四舍五入)，并且按照日期升序排序

获取目标：日期，对应日期正常用户发送给正常用户邮件失败的概率

连接条件(JOIN ...ON...)：题目有两个表user包含用户是否正常的名单，email包含邮件收发对象，发送结果.要找到正常用户发送给正常用户失败的数据，因此要把user表和Email表相互连接，收发都要得知用户状态，故连接条件user.id=send_id, user.id=receive_id

筛选条件(WHERE)：正常用户即user.is_blacklist = 0

失败结果的统计：1. 用CASE... WHEN...语句进行判断返回值，2. 使用SUM对返回值进行累加

发表于 2021-03-26 14:56:28 回复(0)

Tina要成为数据分析师呀

最开始用if写的，查了一下sqlite不支持if，改成case when 通过了

select e.date,
round(sum(case e.type when 'completed' then 0
    else 1 end)/count(e.type),3) as 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

发表于 2021-01-06 20:03:13 回复(0)

horsemuzi

知识点：

Round(数值，保留的小数位数)
case when 判断条件 then 值 else 值 end (相当于if-else if-else形式逻辑语句)🤔

SELECT 
  e.date,
  ROUND(
    SUM(
      CASE
        WHEN e.type = 'no_completed'  -- 每天失败的sum/总count
        THEN 1 
        ELSE 0 
      END
    ) * 1.0 / COUNT(e.type),3) AS p 
FROM
  email e 
  INNER JOIN USER u1 
    ON e.send_id = u1.id 
    AND u1.is_blacklist = 0 
  INNER JOIN USER u2 
    ON e.receive_id = u2.id 
    AND u2.is_blacklist = 0  -- 连接两个表
GROUP BY e.date 
ORDER BY e.date ;  -- 分组排序

发表于 2020-11-02 17:11:23 回复(1)

明明铭

我主要是在COUNT函数中直接写入筛选条件，从而筛选掉非正常用户：

SELECT t.date,ROUND((t.uncom*1.0 / t.total),3) AS p 
FROM 
(SELECT date,COUNT((type = 'no_completed' AND u.is_blacklist = 0 AND us.is_blacklist = 0)&nbs***bsp;NULL) AS uncom,
               COUNT((u.is_blacklist = 0 AND us.is_blacklist = 0)&nbs***bsp;NULL) AS total 
               FROM email e JOIN user u ON e.send_id = u.id
               JOIN user us ON e.receive_id = us.id
               GROUP BY date) t
GROUP BY t.date

发表于 2020-09-06 15:43:42 回复(0)

MikeFan

请问这么写为什么不通过？一定要把case when放在round里面吗？
SELECT a.date, ROUND(SUM(a.completed)*1.0/COUNT(a.completed),3) AS p
FROM (SELECT id, send_id, receive_id,
CASE WHEN type = 'not_completed' THEN 1 ELSE 0 END AS completed, date
FROM email
WHERE e.send_id NOT IN (SELECT id FROM user WHERE is_blacklist = 1) AND
e.receive_id NOT IN (SELECT id FROM user where is_blacklist = 1)) AS a
GROUP BY a.date
ORDER BY a.date ASC

编辑于 2020-08-30 23:47:51 回复(2)

蹲啊蹲蘑菇

为什么这样写会报错呀

select e.date,round(count(if(e.type='no_completed',1,null)) / count(e.type) * 1.0,3) as p from email e

where e.send_id not in (select u.id from user u where u.is_blacklist = 1)
and e.received_id not in (select u.id from user u where u.is_blacklist = 1)
group by e.date

发表于 2020-08-18 16:38:19 回复(2)

牛客929812510号

为什么啊，明明12号没有发送失败啊，为什么这一天的异常率是0.333才能通过

发表于 2021-11-16 09:15:51 回复(0)

SunburstRun

要想到题目要求求的是正常用户发送邮件失败的数目/正常用户发送邮件总数目，所以必然要连接user表排除掉黑名单用户,

所以第一时间写出来的是:

select xxx 
from email
join user as u1 on (email.send_id=u1.id and u1.is_blacklist=0)
join user as u2 on (email.receive_id=u2.id and u2.is_blacklist=0)

这样就联立user表并且排除掉了所有黑名单用户，然后就是找到日期，和每个日期邮件发送的成功率,日期就select email.date就行了，但是成功率不太好写，我们这里用一个函数叫case....when ..then ...else ...end

就很简单了，如下:

sum(case email.type when'completed' then 0 else 1 end)

这个代码的意思就是，当email.type为completed时，就为0，如果不为completed就为1，然后把这一列所有的和加起来，我们就得到了失败的发送邮件的数目，然后使用round函数保留后面3位:

round
(
    sum(case email.type when'completed' then 0 else 1 end)*1.0/count(email.type),3
)

最后再按照日期分组，得到每一天的数据，并且按照日期升序排序:

group by email.date order by email.date;

联立所有sql得:

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

编辑于 2020-09-01 10:27:08 回复(17)

cekio

select date,
ROUND(AVG(CASE WHEN type = 'no_completed' THEN 1
     ELSE 0 END),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

编辑于 2020-09-24 23:19:56 回复(13)

斯曦巍峨

首先筛选出接收方都是合法用户的记录，然后利用group by + case语句来求失败记录以及总发送次数并相除，最后再四舍五入即可

select date,round(sum(case e.type when 'no_completed' then 1 else 0 end)*1.0/count(e.type),3) as p 
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;

发表于 2020-08-24 12:49:54 回复(1)

Mysql

jzrz

count if

select date,
(round((count(if(type = 'no_completed',1,null)) / 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

发表于 2021-08-17 20:32:05 回复(0)

多吃早睡

一个复杂的解法，这个有点无脑，但是最直接

select a.date, round(a.number/b.number, 3)
from (
    select e.date, count(*) as number
    from email as e
    inner join user as u
    on e.send_id=u.id
    inner join user as u1
    on e.receive_id=u1.id
    where u.is_blacklist = 0 and u1.is_blacklist = 0
    and e.type = 'no_completed'
    group by e.date
) as a
inner join (
    select e.date, count(*) as number
    from email as e
    inner join user as u
    on e.send_id=u.id
    inner join user as u1
    on e.receive_id=u1.id
    where u.is_blacklist = 0 and u1.is_blacklist = 0
    group by e.date
) as b
on a.date=b.date

或者这样：但是我没想到竟然可以在group by分组内使用case函数之后，再使用聚合函数，这是关键点

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

编辑于 2020-11-11 18:25:07 回复(8)

Mysql

牛客592465033号

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;

发表于 2021-11-03 21:33:30 回复(0)

BWTNjIrMFoan

向大佬学习：保留三位小数使用 round(x, y)，x代表数，y代表保留的位数。通过使用case和sum来计算未成功的邮件。

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

发表于 2021-03-28 10:58:28 回复(1)

求个offer，让我毕业吧

SELECT
    e.`date`,
    ROUND((SUM(CASE e.type WHEN "no_completed" THEN 1 ELSE 0 END) * 1.0 / COUNT(*)),3) p 
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`;

发表于 2020-09-22 19:05:02 回复(2)