AI 分诊自动化报告的垃圾邮件标记统计

数据与报告
, ,
3

发生该错误的原因是在该时间段内，@system 没有标记任何您同意或不同意的垃圾邮件。
查询尝试除以满足条件的总标记数。在您的情况下，该数字为 0，因此会中断。
如果您希望查询仍然返回结果，可以使用 NULLIF 进行检查，这样在没有可除数时您会得到 NULL。

-- [params]
-- date :start_date = 2025-01-01
-- date :end_date = 2025-05-30

SELECT 
    COUNT(*) AS total_flags,
    COUNT(*) FILTER (WHERE r.status = 1) AS approved_flags,
    COUNT(*) FILTER (WHERE r.status = 2) AS rejected_flags,
    ROUND(100.0 * COUNT(*) FILTER (WHERE r.status = 1) / NULLIF(COUNT(*), 0), 2) AS approved_percentage,
    ROUND(100.0 * COUNT(*) FILTER (WHERE r.status = 2) / NULLIF(COUNT(*), 0), 2) AS rejected_percentage
FROM 
    reviewables r
JOIN 
    post_actions pa ON pa.post_id = r.target_id AND r.target_type = 'Post'
WHERE
    pa.post_action_type_id = 8 -- ID for spam (Flag as spam and hide post) (垃圾邮件的 ID（标记为垃圾邮件并隐藏帖子）)
    AND r.created_at BETWEEN :start_date AND :end_date
    AND r.status IN (1, 2) -- Accepted and rejected flags (已接受和已拒绝的标记)
    AND r.created_by_id = -1 -- System user ID (系统用户 ID)
2 个赞