PieCloudDB查询正常用户间邮件失败概率遇除零错误求助

问题核心 #

统计正常用户互发邮件的失败概率时，若某日期无符合条件的发送记录（总发送数为0），直接执行除法会触发“division by zero”错误。以下是修正后的SQL方案：

修正后的SQL查询 #

SELECT
    DATE(e.send_time) AS send_date,
    ROUND(
        SUM(CASE WHEN e.status = 'failed' THEN 1 ELSE 0 END)::NUMERIC 
        / NULLIF(COUNT(*), 0),
        3
    ) AS fail_rate
FROM
    email e
JOIN
    email_user sender ON e.sender_id = sender.user_id AND sender.is_blacklist = 0
JOIN
    email_user receiver ON e.receiver_id = receiver.user_id AND receiver.is_blacklist = 0
GROUP BY
    DATE(e.send_time)
HAVING
    COUNT(*) > 0
ORDER BY
    send_date ASC;

关键处理点 #

1. 避免除以零：使用NULLIF(COUNT(*), 0)，当总发送数为0时返回NULL，除法结果也为NULL，不会触发报错；再通过HAVING COUNT(*) > 0直接过滤无有效数据的日期，符合需求。
2. 筛选正常用户：通过两次JOIN关联email_user表，分别确保发件人和收件人都是is_blacklist=0的正常用户。
3. 统计失败邮件数：用SUM(CASE WHEN e.status = 'failed' THEN 1 ELSE 0 END)统计失败邮件数量，若你的失败状态标识不是'failed'，请替换为实际值。
4. 格式处理：用ROUND(..., 3)将结果保留3位小数，按日期升序排序。

备选方案（CASE判断） #

也可以用CASE语句显式判断分母是否为0，逻辑一致：

SELECT
    DATE(e.send_time) AS send_date,
    CASE
        WHEN COUNT(*) = 0 THEN NULL
        ELSE ROUND(SUM(CASE WHEN e.status = 'failed' THEN 1 ELSE 0 END)::NUMERIC / COUNT(*), 3)
    END AS fail_rate
FROM
    email e
JOIN
    email_user sender ON e.sender_id = sender.user_id AND sender.is_blacklist = 0
JOIN
    email_user receiver ON e.receiver_id = receiver.user_id AND receiver.is_blacklist = 0
GROUP BY
    DATE(e.send_time)
HAVING
    COUNT(*) > 0
ORDER BY
    send_date ASC;

内容的提问来源于stack exchange，提问作者heihei Li