select date,
ROUND(AVG(CASE WHEN type = 'no_completed' THEN 1
     ELSE 0 END),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;

这个函数直接吹爆吧。

对CASE函数使用聚合函数，真的很棒了。