题解 | 异常的邮件概率

with
    type1 as (
        select
            e.*,
            u1.is_blacklist as send_type,
            u2.is_blacklist as receive_type
        from
            email as e
            inner join user as u1 on e.send_id = u1.id
            inner join user as u2 on e.receive_id = u2.id
    ),
    all_com as (
        select
            date,
            count(*) as all_num
        from
            type1
        where
            send_type = 0
            and receive_type = 0
        group by
            date
    ),
    not_com as (
        select
            date,
            count(*) as not_num
        from
            type1
        where
            send_type = 0
            and receive_type = 0
            and type='no_completed'
        group by
            date      
    )

select a.date as date,round(coalesce(not_num,0)*1.0/nullif(all_num,0),3) as p
from all_com a
left join not_com n on a.date=n.date;

1. COALESCE(n.not_num, 0) 作用： 处理可能为NULL的分子值

工作原理：COALESCE 函数返回参数列表中的第一个非NULL值

当 n.not_num 为NULL时（LEFT JOIN后没有匹配记录的情况），返回0

确保分子永远不会是NULL

您的场景：当某天没有失败邮件时（如2020-01-12），not_com CTE中没有该日期的记录

LEFT JOIN后 n.not_num 会是NULL

COALESCE 将其转换为0

1. 1.0 作用： 强制进行浮点数运算而非整数除法

工作原理：SQLite中，整数除以整数会进行整数除法（如1/2=0）

乘以1.0将运算转换为浮点数运算（1.0/2=0.5）

您的场景：确保像"1次失败/2次总数"能正确计算为0.5而不是0

1. NULLIF(a.all_num, 0) 作用： 安全防护，避免除零错误

工作原理：当 a.all_num 等于0时返回NULL，否则返回 a.all_num

任何数除以NULL结果为NULL（不会报错）

与外部COALESCE配合可提供默认值（但您当前查询中不需要，因为WHERE条件已保证all_num>0）

您的场景：防御性编程，虽然您的业务逻辑中 all_num 不会为0（因为WHERE过滤了）

防止意外数据导致的除零错误