统计正常用户发送给正常用户邮件失败的概率

两种方法：

1. 使用联结，分别计算正常用户发送邮件的总次数和发送失败的次数，再计算失败的比率：
2. 使用case计算正常用户发送邮件失败的次数，再计算失败的比率

使用联结，这是比较容易想起来的方法：

计算正常用户发送邮件失败的次数

SELECT date,COUNT(id) AS cnt1
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)
AND type="no_completed"
GROUP BY date

再计算总次数和比率，和前面的代码放在一起：

SELECT email.date,ROUND(cnt1/COUNT(id),3) AS p
FROM email
JOIN 
(SELECT date,COUNT(id) AS cnt1
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)
AND type="no_completed"
GROUP BY date) AS e1
ON e1.date=email.date
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 email.date
ORDER BY email.date

但是使用联结，代码太繁杂了，这里有个判定条件，type是否是no_completed，可以使用case

SELECT date,ROUND(SUM(CASE  WHEN type='no_completed' THEN 1 ELSE 0 END)/COUNT(email.id),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 email.date
ORDER BY email.date