#思路：变相用sum来统计个数
select a.date,round(sum(fail_num)*1.0/count(*),3) as p
from
(select * , 
CASE WHEN type=='no_completed' THEN 1
ELSE 0
END as fail_num
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)) as a
group by a.date