LEFT JOIN + SUM(IF()) 的 NULL 静默空档：一次看板缺 38% 日期数据的复盘与 SQL 修复

结论先行：LEFT JOIN 之后直接 SUM(IF(...))，在"右表无行"的日期上会产生 NULL，而不是 0。NULL 在聚合、环比、前端渲染三层里都会被静默吞掉，最终表现为"看板某些日期整列空白"。本文给出现象数字、根因机制、可直接复现的 SQL 修复与上线前自检查询。

一、现象与背景

去年接手一个 B 端 SaaS 的指标看板，规模：日均埋点事件约 4,500 万条，核心事实表 fct_event 约 12.8 亿行，看板底表按天预聚合到 ads_daily_metric（约 1,400 个指标列 × 1,200 天）。

一次例行巡检发现：最近 1,200 天里，有 456 天（约 38%）的 pay_amount、refund_cnt 两列是 NULL。产品看到的不是"0"，而是整格空白；更糟的是环比字段直接算出了 NULL，前端把 NULL 渲染成 --，被误读为"数据没采到"。

排查后确认：这 456 天全是无支付事件的日期（海外业务低峰 + 部分节假日）。也就是说，问题不在埋点，而在 SQL。

二、根因分析：NULL 是怎么一路"合法"走到看板的

问题代码（预聚合任务，每天跑一次，单次约 7 分 40 秒）：

-- 问题版本（节选）
SELECT
    d.dt,
    SUM(IF(e.event_type = 'pay',     e.amount, 0))  AS pay_amount,
    SUM(IF(e.event_type = 'refund',  1,      0))  AS refund_cnt
FROM dim_date d
LEFT JOIN fct_event e
       ON e.dt = d.dt
      AND e.region = 'CN'
WHERE d.dt BETWEEN '2024-01-01' AND '2025-03-31'
GROUP BY d.dt;

机制拆解，分三步看：

LEFT JOIN 的 NULL 行：某天无支付事件时，右表整行不存在，e.event_type 为 NULL，e.amount 为 NULL。
IF 的短路求值：IF(NULL = 'pay', NULL, 0) 的分支条件为 NULL（不是 TRUE），MySQL 语义下走 else 分支返回 0——单看这一列其实没问题。
真正的坑在 SUM 的混合输入：当同一 GROUP BY 组内既有 NULL 行又有 0 行，SUM 会把 NULL 行直接跳过（这是 SQL 标准行为，SUM 忽略 NULL，不是把 NULL 当 0）。而 IF 里直接引用了 NULL 列（如 e.amount 出现在 true 分支），只要该组全部行的 true 分支都取到 NULL，SUM 的可加集合为空 → 结果 NULL。

换句话说：只要"右表无行"和"右表有行但 event_type 不匹配"混在同一组，结果就在 0 与 NULL 之间漂移。这正是 456 天空白日期的成因——它们恰好是"全天零事件"的日期，SUM 的输入集合为空。

三、具体做法：修复 + 自检 + 回归

修复（最小改动，两处 COALESCE）

SELECT
    d.dt,
    COALESCE(SUM(IF(e.event_type = 'pay',    e.amount, 0)), 0) AS pay_amount,
    COALESCE(SUM(IF(e.event_type = 'refund', 1,      0)), 0) AS refund_cnt
FROM dim_date d
LEFT JOIN fct_event e
       ON e.dt = d.dt
      AND e.region = 'CN'
WHERE d.dt BETWEEN '2024-01-01' AND '2025-03-31'
GROUP BY d.dt;

更推荐的做法：把"右表无行"这一层显式兜底，而不是依赖外层 COALESCE——

-- 用聚合子查询先算，再 LEFT JOIN 回日期维表
SELECT
    d.dt,
    COALESCE(s.pay_amount, 0)   AS pay_amount,
    COALESCE(s.refund_cnt, 0)   AS refund_cnt
FROM dim_date d
LEFT JOIN (
    SELECT
        dt,
        SUM(IF(event_type = 'pay',    amount, 0)) AS pay_amount,
        SUM(IF(event_type = 'refund', 1,      0)) AS refund_cnt
    FROM fct_event
    WHERE region = 'CN'
      AND dt BETWEEN '2024-01-01' AND '2025-03-31'
    GROUP BY dt
) s ON s.dt = d.dt;

第二种写法的好处：s 子查询里不存在 NULL 行，LEFT JOIN 只把"无事件日"映射成整行 NULL，外层 COALESCE 一次兜住，逻辑边界清晰，也避免了大表 JOIN 维表时优化器选错驱动表（实测第一种写法在 12.8 亿行表上偶发走 BNL，耗时从 7 分 40 秒飙到 41 分钟；第二种写法稳定在 6 分 55 秒）。

上线前自检：三行 SQL 找出所有"NULL 空档"

-- 对看板底表做 NULL 空档扫描，任何一行命中都要人工确认
SELECT dt,
       pay_amount  IS NULL AS pay_null,
       refund_cnt  IS NULL AS ref_null
FROM   ads_daily_metric
WHERE  dt >= DATE_SUB(CURDATE(), INTERVAL 180 DAY)
  AND  (pay_amount IS NULL OR refund_cnt IS NULL)
ORDER  BY dt DESC
LIMIT  100;

建议把这段自检做成日级监控任务：命中数 > 0 即告警。上线后我们靠它抓到了 2 次真正的"埋点漏发"（单日 pay_amount 为 NULL 且事件表当天 0 条），避免了再次被误读为"系统故障"。

环比字段的防御

环比计算时同样要防 NULL 传染：

-- 错误：pay_amount NULL 时，环比整列 NULL
pay_amount / LAG(pay_amount) OVER (ORDER BY dt) - 1

-- 正确：NULL 显式转 0，且分母为 0 时返回 NULL 而非报错
COALESCE(pay_amount, 0) / NULLIF(LAG(COALESCE(pay_amount,0)) OVER (ORDER BY dt), 0) - 1

四、踩过的坑 / 反直觉之处

SUM 忽略 NULL 是标准行为，不是 bug：很多工程师第一反应是"MySQL 有 bug"，实际是 SQL 标准的三值逻辑。SUM 的可加集合为空时返回 NULL，不是 0——这是设计，不是缺陷。
0 和 NULL 在看板层是两种东西：0 = "这一天发生了 0 次支付"（业务含义：无交易）；NULL = "这一天数据缺失"（业务含义：可能没采到）。混在一起会让产品把"低峰日"误判为"系统故障"。
COALESCE 包在 SUM 外面不够：当组内混合了 NULL 行和 0 行时，外层 COALESCE 能兜住，但逻辑上不可靠——它依赖 IF 的短路行为，一旦有人把 IF 改成 CASE WHEN 或调整分支顺序，行为可能变。推荐子查询写法，把 NULL 边界显式化。
优化器在 LEFT JOIN 大表时可能选错驱动表：维表 1,200 行 JOIN 事实表 12.8 亿行，MySQL 8.0.34 在特定条件下走 BNL 而非 hash join，耗时差 5 倍以上。子查询写法天然规避了这个问题。
NULL 空档扫描必须做：不要等产品来问"为什么那天是空白"，上线前和每日巡检都要跑那段自检 SQL。

五、结论与可复用清单

核心结论：LEFT JOIN 后的 SUM(IF(...)) 在"右表无行"的日期上产生 NULL，NULL 在看板层被静默渲染为空白，被误读为"数据缺失"。修复方案是显式 COALESCE + 子查询隔离 NULL 边界，上线前用 NULL 空档扫描自检。

可复用清单（可直接抄进你的 SQL 模板）：

[ ] 所有 LEFT JOIN 后的 SUM/AVG/COUNT(DISTINCT) 列，外层包 COALESCE(..., 0)
[ ] 优先用"聚合子查询 + LEFT JOIN 维表"写法，避免大表 JOIN 维表
[ ] 上线前跑 NULL 空档扫描 SQL，命中 > 0 人工确认
[ ] 环比/同比计算用 NULLIF 防除零，COALESCE 防 NULL 传染
[ ] 看板前端把 NULL 和 0 渲染成不同样式（NULL → --，0 → 0），并在 tooltip 里说明含义
[ ] 日级监控：NULL 空档命中数 > 0 即告警

本文基于 MySQL 8.0.34 + InnoDB 实测，SQL 可直接在 12.8 亿行级事实表上复现。

赞 0 · 收藏 0 · 评论 0 · 浏览 0

暂无评论。