题解 | #异常的邮件概率#

1.分析
方法一：
利用case when 对状态进行赋值， completed为0， no_completed为1，然后利用sum函数把它俩加起来，就是发送失败的次数；利用count函数对所有状态进行计数，就是发送邮件的总次数。
发送失败的次数 / 发送邮件的总次数 = 发送邮件失败的概率
round函数取概率值3位小数

方法二：
sum直接可以计算出状态值的总次数

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





    select date, round(sum(type = "no_completed") / count(*), 3) as p
    from email as t1
    join user as t2 on t1.send_id = t2.id
    join user as t3 on t1.receive_id = t3.id
    where t2.is_blacklist = 0 and t3.is_blacklist = 0
    group by date
    order by date;