方法：首先过滤掉含有非正常用户的邮件记录，并在结果中增加一个标志字段即发送成功的标识为0，失败为1；
然后按照日期分组，并计算各组标志字段的和与组长度的商，结果保留3位小数；
SELECT a.date,ROUND(SUM(a.com)*1.0/ COUNT(a.com),3) AS failed
FROM(
SELECT e.date,
CASE
WHEN e.type='no_completed' THEN 1
WHEN e.type='completed' THEN 0
END AS com
FROM email as e
WHERE e.send_id IN (
SELECT id
FROM user
WHERE is_blacklist=0
) AND e.receive_id IN (
SELECT id
FROM user
WHERE is_blacklist=0
)
) AS a
GROUP BY a.date