最直接的思考方式

select

t1.date,

round(t1.col1/t2.col2,3) as p

from(

select date,count(1) col1

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)

and type = 'no_completed'

group by date) t1

join

(select date,count(1) as col2 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) t2

on

t1.date = t2.date;

使用case when else end 来做 先过滤掉黑名单

select

date,

round(sum(case type when 'no_completed' then 1 else 0 end) /count(1) ,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;

应该还可以使用if

select
date,
round(sum(if(type = 'no_completed' ,1,0)) /count(1) ,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;