SELECT date,
       ROUND(AVG(CASE 
                     WHEN type = 'no_completed' THEN 1
                     ELSE 0 
                 END),3) AS p
FROM email e
JOIN user u1 ON (e.send_id = u1.id AND u1.is_blacklist = 0)
JOIN user u2 ON (e.receive_id = u2.id AND u2.is_blacklist = 0)
GROUP BY date
ORDER BY date;

先将不是正常用户的往来信息都给排除掉，然后将不成功的邮件设成1，成功为0，计算平均值