SQL65 异常的邮件概率

考察 多次JOIN 和 条件聚合，归入错题本。

1. 题目

• 题目来源：SQL65 异常的邮件概率

• 题目描述
  写一个sql查询，每一个日期里面，正常用户发送给正常用户邮件失败的概率是多少，结果保留到小数点后面3位(3位之后的四舍五入)，并且按照日期升序排序

• 两张表
  

• 所需结果
  

2. 题解

思路：
方法一：在子查询中新增一列（not_completed），表示满足条件与否，再进行概率求值。
方法二：表连接之后，直接条件判断并求概率，需要注意的是，这里连接的字段并非 id 和 id，而是send_id和receive_id分别对应 id ，然后两次连接。

2.1 子查询

SELECT t.date, 
#        ROUND(SUM(t.not_completed) / SUM(t.send_id), 3)
       ROUND(AVG(t.not_completed) , 3) # 直接使用 AVG函数

FROM (SELECT *, 
             IF(type = 'completed', 0, 1) AS not_completed
      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)
     ) AS t
GROUP BY date
ORDER BY date ASC

2.2 表连接

正解：

# 正解
SELECT date, ROUND(SUM(type = 'no_completed') / COUNT(*), 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 date
ORDER BY date ASC;

很疑惑，下面代码报错，仅仅是条件聚合语句的方式不同（暂时还未搞明白）：

SELECT date, ROUND(SUM(IF(type = 'completed', 0, 1) / COUNT(*), 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 date
ORDER BY date ASC;

2.3 反思

1、发现对表连接概念理解得还是不够深入：
按指定条件连接之后，则会进行列合并，即将副表相应字段加入主表，形成一个全新的表格，即 FROM table_a a JOIN table_b c，这里表格c代表已经是全新的表格了 ；
若主表有多个字段需要满足，则可以复用多个副表进行多次连接。

2、条件聚合函数掌握还不够熟练
有三种形式：
（第一种还不够熟悉）

select count(num > 200 or null) from a;
select count(if(num > 200, 1, null)) from a
select count(case when num > 200 then 1 end) from a

参考：点击