邮件表记录了某邮件App的数据，包括：编号（主键）、寄信人编号、收信人编号、枚举类型（completed表示邮件发送成功，no_completed表示邮件发送失败）。
例如，表中第1行表示，寄信人（编号为2）在2020-01-11（日期）成功发送（枚举类型为completed）了一封邮件，这封邮件的收信人是编号为3的用户。
用户表里记录了该邮件App的所有用户信息。其中，用户编号为主键，是否为黑名单（值为0表示是正常用户，值为1表示是黑名单用户）。
现在需要分析出，每一个日期里，正常用户发送给正常用户邮件失败的概率是多少？
结果保留到小数点后面3位(3位之后的四舍五入)，并且按照日期升序排序。
【解题步骤】
题目要求的是正常用户发送给正常用户，而邮件表中包含寄信人编号和收信人编号2个用户编号，是否是正常用户在用户表中。
所有，需要用到两个表，要用到多表联结。
因为需要判断邮件表中2列（寄信人、收信人）是否是正常用户，因此需要和用户表联结两次。也就是分别将寄件人编号和用户编号匹配，再将收件人编号和用户编号匹配。
2.应该使用哪种联结呢？
因为要保留邮件表里的全部数据，所以使用左联结（邮件表为左表）
代码语言：javascript复制1 select 寄信人编号,收信人编号,枚举类型,日期,用户编号,
2 b.是否为黑名单 as 寄信人正常用户
3 from 邮件表 as a
4 left join 用户表 as b
5 on a.寄信人编号 = b.用户编号;
用上面查询结果再继续联结一次用户表，将收信人编号和用户编号匹配。
代码语言：javascript复制1 select 寄信人编号,收信人编号,枚举类型,日期,
2 b.是否为黑名单 as 寄信人正常用户,
3 c.是否为黑名单 as 收信人正常用户
4 from 邮件表 as a
5 left join 用户表 as b
6 on a.寄信人编号 = b.用户编号
7 left join 用户表 as c
8 on a.收信人编号 = c.用户编号;
3.我们需要筛选出正常用户发送给正常用户的邮件，即上面查询结果中，寄件人正常用户（b.是否为黑名单）和收信人正常用户（ c.是否为黑名单）这两列的值均为0。
可以用where子句筛选。这里需要注意的是sql的运行顺序。
由于sql先执行where子句再执行select子句，因此where中不能用select中列的别名：
where 寄件人正常用户 = 0 and 收件人正常用户 = 0)
而是要这样写：
where b.是否为黑名单 = 0 and c.是否为黑名单 = 0
代码语言：javascript复制1 select 寄信人编号,收信人编号,枚举类型,日期,
2 b.是否为黑名单 as 寄信人正常用户,
3 c.是否为黑名单 as 收信人正常用户
4 from 邮件表 as a
5 left join 用户表 as b
6 on a.寄信人编号 = b.用户编号
7 left join 用户表 as c
8 on a.收信人编号 = c.用户编号
9 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)。
代码语言：javascript复制1 select a.日期,
2 sum(case a.
3 枚举类型 when 'no_completed' then 1 else 0 end) / count(a.枚举类型)
4 from 邮件表 as a
5 left join 用户表 as b
6 on a.寄信人编号 = b.用户编号
7 left join 用户表 as c
8 on a.收信人编号 = c.用户编号
9 where b.是否为黑名单 = 0 and c.是否为黑名单 = 0
group by a.日期;查询结果：
5.最后，题目要求结果保留到小数点后面3位(3位之后的四舍五入)，并且按照日期升序排列。
我们使用format函数进行保留小数点，用order by进行排序。
代码语言：javascript复制1 format(N,D)format函数：
- N是要格式化的数字。
- D是要舍入的小数位数。
1 select a.日期,
2 format(
3 sum(case a.枚举类
4 型 when 'no_completed' then 1 else 0 end) / count(a.枚举类型)
5 ,3) as p
6 from 邮件表 as a left join 用户表 as b
7 on a.寄信人编号 = b.用户编号
8 left join 用户表 as c
9 on a.收信人编号 = c.用户编号
10 where b.是否为黑名单 = 0 and c.是否为黑名单 = 0
11 group by a.日期
order by a.日期;
【本题考点】
1.考查联结。需要知道什么情况下使用哪种联结。
2.考查如何进行分组汇总。
3.考查如何用format函数保留小数点位数
4.考察count函数，case语句的用法
5.考察如何使用多维度拆解分析方法，将复杂的业务问题拆解为一个一个可以解决的子问题
【举一反三】
以下是某班同学的成绩表，求该班成绩及格的概率（60及以上及格）并保留2位小数。
参考答案：
代码语言：javascript复制1 select format(sum(case when 成绩 >= 60
2 then 1
3 else 0
4 end) / count(成绩)
5 ,2) as p
6 from 成绩表;查询结果：
推荐：如何从零学会sql？
