题解 | 异常的邮件概率

select a.date,
round(count(case when a.type='no_completed' then 1 else null end)/count(a.id),3) as p

from(
    select e.id,e.send_id,e.receive_id,e.type,e.date 
    from email e 
    left join user u on e.send_id = u.id 
    where u.is_blacklist =0 
)a
join(select e.id,e.send_id,e.receive_id,e.type,e.date 
    from email e 
    left join user u on  e.receive_id = u.id
    where u.is_blacklist =0 
)as b
on a.id = b.id

group by a.date
order by a.date

关键在于写两个子查询a和b，a取出正常的发件人的信息，b取出正常的收件人的信息，然后将两个表再通过id字段相连接，再计算概率