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

大致思路是：

type='no_completed'的行数 除以 以日期分组之后的行数

1. 排除黑名单中的用户，关键是黑名单的用户既不能发邮件，也不能收邮件(我一开始就是只看到不能发邮件)

	select type,date from email e 
        join user u1 ON (e.send_id = u1.id and u1.is_blacklist != 1)
        join user u2 on (e.receive_id = u2.id and u2.is_blacklist != 1)

2. 排除之后，需要计算type='no_completed'的行数

这里我的想法是将type='completed'的值改为null，然后count(type)即可得到所要的行数

• 这里先将type='completed'的值改为null

	select 
        (case t.type
            when 'completed' then type = null
            else type
            end
        )as s,t.date
    from
        (select type,date from email e 
        	join user u1 ON (e.send_id = u1.id and u1.is_blacklist != 1)
        	join user u2 on (e.receive_id = u2.id and u2.is_blacklist != 1)
        ) t

3. 拿到type='no_completed'的行数剩下的就简单了

select 
    d.date,
    round(count(d.s)*1.0/count(date),3) p
from 
    (select 
        (case t.type
            when 'completed' then type = null
            else type
            end
        )as s,t.date
    from
        (select type,date from email e 
        	join user u1 ON (e.send_id = u1.id and u1.is_blacklist != 1)
        	join user u2 on (e.receive_id = u2.id and u2.is_blacklist != 1)
        ) t
    )d
group by d.date