select a.date,round(sum(case a.type when'completed' then 0 else 1 end )*1.0/count(a.type),3)as p
from (select e.send_id,e.receive_id,e.type,e.date,u1.is_blacklist as b1,u2.is_blacklist as b2 from email as e
join user as u1
on e.send_id=u1.id and u1.is_blacklist=0
join user as u2
on e.receive_id=u2.id and u2.is_blacklist=0) as a
group by a.date order by a.date

join 和 left_join 的的区别（因为一开始用left_join报错了）：https://blog.csdn.net/xxx_13/article/details/97631254#:~:text=SQL%E4%B8%ADJoin%E4%B8%8ELeft%20Join%20%E7%9A%84%E5%8C%BA%E5%88%AB.%20%E7%89%88%E6%9D%83%E5%A3%B0%E6%98%8E%EF%BC%9A%E6%9C%AC%E6%96%87%E4%B8%BA%E5%8D%9A%E4%B8%BB%E5%8E%9F%E5%88%9B%E6%96%87%E7%AB%A0%EF%BC%8C%E9%81%B5%E5%BE%AA%20CC%204.0%20BY-SA%20%E7%89%88%E6%9D%83%E5%8D%8F%E8%AE%AE%EF%BC%8C%E8%BD%AC%E8%BD%BD%E8%AF%B7%E9%99%84%E4%B8%8A%E5%8E%9F%E6%96%87%E5%87%BA%E5%A4%84%E9%93%BE%E6%8E%A5%E5%92%8C%E6%9C%AC%E5%A3%B0%E6%98%8E%E3%80%82.,JOIN%20%EF%BC%89%20%20%20SELECT%20%2AFROM%20T1%20

如果说我用leftjoin 会出现null 用这边的算***给当成是一个no_completed 出现问题