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

SELECT
    date,
    ROUND((nc / cnt), 3)
FROM
    (
        SELECT
            date,
            COUNT(CASE WHEN type = 'no_completed' THEN 1 END) nc,
            COUNT(*) cnt
        FROM
            email
        WHERE
            send_id <> 2
            AND receive_id <> 2
        GROUP BY
            date
    ) subquery




/*
select
    date,
        round(
        (
            select
                count(type) nc
            from
                email
            where
                send_id <> 2
                and receive_id <> 2
                and type = 'no_completed'
            group by
                date
        ) / (
            select
                count(type)
            from
                email
            where
                send_id <> 2
                and receive_id <> 2
            group by
                date
        ),3
        )#不加round保留的小数位数不对，加了round整段代码就有问题
from
    email
group by
    date
*/




    /*
    select count(type)
    from email
    where send_id<>2 and received_id<>2
    group by date
    
    
    
    select (type) nc
    from email
    where send_id<>2 and received_id<>2 and type='no_completed'
    group by date
    */