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

• 1. 筛选出黑名单用户 /不是黑名单用户 做区分
• 2. 然后使用 WHERE 限制 筛选中不包含黑名单用户;

• 3.计算　失败率；分天－－＞　必定GROUP BY，　然后累计的时候是　部分判定／整体count（），因此　用avg() 嵌套：if()/case when

SELECT date,ROUND(avg(if(type="completed",0,1)),3) as p 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