AWS Athena查询过滤NULL和空数组'[]'条件不生效问题排查

AI 小北

2026-10-7

问题原因

过滤条件逻辑运算符使用错误
你统计opp_w_changed时使用的条件(oldcategory IS NOT NULL OR oldcategory != '[]')完全不符合业务预期：

当oldcategory值为'[]'时，oldcategory IS NOT NULL判断结果为真，OR逻辑下整行条件会被判定为真，导致本应视为空值的行被错误计入“有变更”的分组
该条件本质上只会排除oldcategory为NULL的行，和你要统计“oldcategory既不是NULL也不是'[]'”的需求完全不符

唯一值计数逻辑冲突
你使用COUNT(DISTINCT salesforce_opportunity_id)按行过滤统计时，只要同一个商机ID下存在任意一行满足过滤条件就会被计数。如果同一个商机同时存在oldcategory为空、不为空的行，会同时被计入opp_w_changed和opp_without_changed两个分组，导致两个分组的计数加总远大于总商机数，你遇到的opp_without_changed等于总商机数的情况，就是因为所有商机都至少有一行oldcategory符合空值判断条件。

解决方案

首先修正有效值判断的逻辑：

判定oldcategory为有效值的正确条件：oldcategory IS NOT NULL AND oldcategory != '[]'
判定oldcategory为空值的正确条件：oldcategory IS NULL OR oldcategory = '[]'

其次调整统计逻辑，使用条件聚合一次性计算所有互斥的商机统计指标，避免多子查询的逻辑冲突，优化后SQL如下：

SELECT 
    COUNT(*) AS total_rows,
    COUNT(DISTINCT sfattachmentid) AS total_attachments,
    COUNT(DISTINCT salesforce_opportunity_id) AS total_opps,
    -- 统计至少有一行oldcategory为有效值的商机数
    COUNT(DISTINCT CASE WHEN oldcategory IS NOT NULL AND oldcategory != '[]' THEN salesforce_opportunity_id END) AS opp_w_changed,
    -- 统计无任何有效值的商机数 = 总商机数 - 有有效值的商机数
    COUNT(DISTINCT salesforce_opportunity_id) - COUNT(DISTINCT CASE WHEN oldcategory IS NOT NULL AND oldcategory != '[]' THEN salesforce_opportunity_id END) AS opp_without_changed,
    SUM(CASE WHEN oldcategory != '' THEN 1 ELSE 0 END) AS oldCategory_changed,
    SUM(CASE WHEN oldcategory IS NULL THEN 1 ELSE 0 END) AS oldCategory_blank
FROM "athena_decisionengine"."transactions"

如果你的业务逻辑要求“商机下所有行oldcategory都为空才计入opp_without_changed”，以上逻辑可以直接满足你的计算预期，两个商机统计项的加总将和总商机数一致。

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