代码量最多但是最无脑的写法……

选出所有正常用户发送的邮件

select a.date,round(b.bad/a.num,3) as p
from
(select date,count(id) num from email
where send_id in (select id from user where is_blacklist=0)
and receive_id in (select id from user where is_blacklist=0)
group by date) a
join
(select date,count(id) bad from email
where send_id in (select id from user where is_blacklist=0)
and receive_id in (select id from user where is_blacklist=0)
and type = 'no_completed'
group by date) b
on a.date = b.date
order by a.date
#(select id from user where is_blacklist=0)查所有正常用户
#(select date,count(id) num from email
where send_id in (select id from user where is_blacklist=0)
and receive_id in (select id from user where is_blacklist=0)
group by date) 查所有正常用户发的邮件
#查所有正常用户发送失败的邮件
#除一下即可哈哈哈