如何分析异常邮件？

邮件表记录了某邮件App的数据，包括：编号（主键）、寄信人编号、收信人编号、枚举类型（completed表示邮件发送成功，no_completed表示邮件发送失败）。

例如，表中第1行表示，寄信人（编号为2）在2020-01-11（日期）成功发送（枚举类型为completed）了一封邮件，这封邮件的收信人是编号为3的用户。

用户表里记录了该邮件App的所有用户信息。其中，用户编号为主键，是否为黑名单（值为0表示是正常用户，值为1表示是黑名单用户）。

现在需要分析出，每一个日期里，正常用户发送给正常用户邮件失败的概率是多少？

结果保留到小数点后面3位(3位之后的四舍五入)，并且按照日期升序排序。

【解题步骤】

题目要求的是正常用户发送给正常用户，而邮件表中包含寄信人编号和收信人编号2个用户编号，是否是正常用户在用户表中。

所有，需要用到两个表，要用到多表联结。

因为需要判断邮件表中2列（寄信人、收信人）是否是正常用户，因此需要和用户表联结两次。也就是分别将寄件人编号和用户编号匹配，再将收件人编号和用户编号匹配。

2.应该使用哪种联结呢？

因为要保留邮件表里的全部数据，所以使用左联结（邮件表为左表）

select 寄信人编号,收信人编号,枚举类型,日期,用户编号,
       b.是否为黑名单 as 寄信人正常用户
from 邮件表 as a 
left join 用户表 as b
on a.寄信人编号 = b.用户编号;

用上面查询结果再继续联结一次用户表，将收信人编号和用户编号匹配。

select 寄信人编号,收信人编号,枚举类型,日期,
       b.是否为黑名单 as 寄信人正常用户,
       c.是否为黑名单 as 收信人正常用户
from 邮件表 as a 
left join 用户表 as b
on a.寄信人编号 = b.用户编号
left join 用户表 as c
on a.收信人编号 = c.用户编号;

3.我们需要筛选出正常用户发送给正常用户的邮件，即上面查询结果中，寄件人正常用户（b.是否为黑名单）和收信人正常用户（ c.是否为黑名单）这两列的值均为0。

可以用where子句筛选。这里需要注意的是sql的运行顺序。

由于sql先执行where子句再执行select子句，因此where中不能用select中列的别名：

where 寄件人正常用户 = 0 and 收件人正常用户 = 0)

而是要这样写：

where b.是否为黑名单 = 0 and c.是否为黑名单 = 0

select 寄信人编号,收信人编号,枚举类型,日期,
       b.是否为黑名单 as 寄信人正常用户,
       c.是否为黑名单 as 收信人正常用户
from 邮件表 as a 
left join 用户表 as b
on a.寄信人编号 = b.用户编号
left join 用户表 as c
on a.收信人编号 = c.用户编号
where b.是否为黑名单 = 0 and c.是否为黑名单 = 0;

查询结果：

4.计算每一个日期里，正常用户发送给正常用户邮件失败的概率：

每一个日期，看到“每”字，我们要想到用分组汇总来实现，这里按日期分组（group by）。

分组后，如何汇总呢？

因为要分析的是每天发送邮件失败的概率。

某一天发送邮件失败的概率=发送邮件失败数（枚举类型这一列中值为no_completed的个数）/发送邮件总数（枚举类型这一类的总数）。

以下图的日期2020-01-11举例，这一天邮件发送失败的概率=发送失败的邮件数（1）/发送邮件总数（2）。

这里涉及到统计个数，要用到count函数。计算发送邮件总数，直接用count(a.枚举类型)即可。

计算发送邮件失败数，也就是枚举类型这一列中值为no_completed的个数，相当于根据条件判断来计数，要用到case语句。

枚举类型值为'no_completed' 的为1否则为0，再用sum函数求和：

sum(case  枚举类型 when 'no_completed' then 1 else 0 end)。

select a.日期,
       sum(case a.枚举类型 when 'no_completed' then 1 else 0 end) / count(a.枚举类型)
from 邮件表 as a 
left join 用户表 as b
on a.寄信人编号 = b.用户编号
left join 用户表 as c
on a.收信人编号 = c.用户编号
where b.是否为黑名单 = 0 and c.是否为黑名单 = 0
group by a.日期;

查询结果：

5.最后，题目要求结果保留到小数点后面3位(3位之后的四舍五入)，并且按照日期升序排列。

我们使用format函数进行保留小数点，用order by进行排序。

format(N,D)

format函数：

• N是要格式化的数字。

• D是要舍入的小数位数。

‍

select a.日期,
       format(
       sum(case a.枚举类型 when 'no_completed' then 1 else 0 end) / count(a.枚举类型)
       ,3) as p
from 邮件表 as a left join 用户表 as b
on a.寄信人编号 = b.用户编号
left join 用户表 as c
on a.收信人编号 = c.用户编号
where b.是否为黑名单 = 0 and c.是否为黑名单 = 0
group by a.日期
order by a.日期;

【本题考点】

1.考查联结。需要知道什么情况下使用哪种联结。

2.考查如何进行分组汇总。

3.考查如何用format函数保留小数点位数

4.考察count函数，case语句的用法

5.考察如何使用多维度拆解分析方法，将复杂的业务问题拆解为一个一个可以解决的子问题

【举一反三】

以下是某班同学的成绩表，求该班成绩及格的概率（60及以上及格）并保留2位小数。

参考答案：

‍

select format(sum(case when 成绩 >= 60 
                       then 1 
                       else 0
                    end) / count(成绩)
                    ,2) as p
from 成绩表;

查询结果：

推荐：如何从零学会sql？