题解 | #异常的邮件概率#

我的做法 第一步：求出发送成功的邮件数（排除黑名单）

	SELECT DATE,COUNT(`type`) c1
    FROM email
    WHERE `type` = "completed"
    AND 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`

第二步：求出总邮件数（排除黑名单）

	SELECT DATE,COUNT(`type`) c2
    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`

第三步：两表连接，做除法，完整代码如下 题目要求发送邮件失败的概率，根据我的直觉，发送邮件失败的概率 = 1 - 发送邮件成功的概率

SELECT t1.date, 1-ROUND(c1/c2,3) P
FROM 
	(SELECT DATE,COUNT(`type`) c1
	FROM email
	WHERE `type` = "completed"
	AND 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`) t1
LEFT JOIN
	(SELECT DATE,COUNT(`type`) c2
	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`) t2
ON t1.`date` = t2.`date`