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

select
    date,
    round(sum(type = "no_completed") * 1.0 / count(type), 3) p
from
    (
        select
            *
        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
            )
    ) t
group by date
order by date

上述 ...and... 语句可以直接使用 join 进行联立获取；

对于 sum 来代替 count 的原因：

count(条件表达式) 无论是否满足条件表达式，只要非 null 值都会加 1，而 sum(条件表达式) 则符合正常逻辑。

使用 count 可以如下方式

count(type = 'no_completed' or null)

count(case when type = 'no_completed' then 1 else null end)