补了零,这一天却消失了?
7月22日一单都没卖,你写了 COALESCE 补零,报表上这一天却直接消失了。很多人处理空值就一句话:用 COALESCE 补成 0。但面试官真正想听的是:0、NULL 和没有记录,分别表达什么业务含义?什么时候可以补零?补零放在哪一层?怎么区分「真的没有数据」和「数据没采到」?最危险的做法,是在 ODS 或 DWD 层,把所有 NULL 都改成 0。报表看起来完整了,但数据质量问题、分母为 0、不适用的场景,也被一起掩盖了。
一句话回答:0 表示记录存在,而且结果确定为零。NULL 表示记录存在,但值未知、缺失或者不适用。没有记录,表示这条数据行根本不存在。所以补零之前,要先补齐业务统计范围,再判断「没有发生」是不是等于 0,不能只靠 COALESCE 一把梭。
先看痛点一:只查事实表,没下单的日期直接消失。订单事实表里只有两天:7月21日 100 单,7月23日 80 单。7月22日没有订单,它在事实表里不是一行「订单数等于 0」,而是根本没有这一天的记录。直接对事实表 GROUP BY 日期,结果只会出现 21 日和 23 日。前端折线图可能把 22 日画成断点,也可能把两天直接连成一条线。正确做法是先用日期维度列出 21、22、23 三天,再 LEFT JOIN 订单聚合,22 日才能显示为 0。记住:没有记录,不等于已经存了一个 0。想展示 0,必须先有一张表告诉你:这个日期本来就在统计范围内。痛点二:把 NULL 补成 0,可能把「未知」伪装成「没有发生」。比如订单的支付数值 pay_amount 是 NULL,原因可能有好几种:源系统没有传这个字段,或者字段解析失败;订单还没支付,实付数值暂时不适用;也可能这条记录本该有值,但数据质量出了问题。这些含义,都不等于「支付了 0」。如果直接写成 0,财务看到的是一笔零元订单,真正的问题却是数据没采集成功。所以先问 NULL 的业务原因:确实没发生、而且指标定义如此,才能在指标层补 0;值未知或解析失败,保留 NULL,进入质量监控;当前不适用,保留 NULL 或标记 N/A;如果连事实记录都没有,就先确认统计范围,再决定要不要补 0。0 是一个确定的业务结论,NULL 是一个需要继续解释的信号。把信号改成结论,后面就很难追责。痛点三:LEFT JOIN 的条件写错位置,补零还没开始,空行就被过滤掉了。要统计每天的支付订单数,用日期维度左连接订单,方向是对的。但如果把「状态等于 PAID」写进 WHERE,7月22日没有订单,右表的 status 是补出来的 NULL。原稿说这个条件「结果为假」,更准确地说,NULL 和 PAID 比较的结果是 UNKNOWN。WHERE 只保留 TRUE,这一天照样被删掉,LEFT JOIN 实际退化成了 INNER JOIN 的效果。前面专题讲过:把状态条件挪进 ON,再对聚合结果做 COALESCE,22 日才能保留为 0。补零的第一步是保住本来应该出现的行;行都没了,后面再写 COALESCE 也补不回来。
进入核心论证。原因一:先区分「有记录的零」和「没有记录的零」。第一种是 0:有一行,数值就是 0,指标确定为零,比如当天退款为 0,通常不用再补。第二种是 NULL:有一行,字段为空,值未知、缺失或不适用,比如解析失败,要先判断原因。第三种是没有记录:统计表里压根没这一行,可能没发生、没覆盖或没采到,要补齐范围再判断。还有三种容易混进来:空字符串,是源系统用字符串表达缺失,比如备注为空,不要直接当 0;特殊值,比如未知年龄记成 -1 或 9999,要按字典转换;不适用,是有主体但指标没有意义,比如未支付订单没有实付数值,通常保留 NULL。判断方法很简单,连续问三个问题。第一,这条统计行应该存在吗?不应该存在,那就是没有记录,先检查统计范围。第二,这条记录的指标应该有数值吗?不适用或未知,就保留 NULL,或者标记 N/A。第三,业务是否确认没有发生?确认了才补 0;没确认,保留 NULL,排查数据质量。「没有订单」可以是 0,「没有支付数值」通常不能直接当 0。补不补,取决于指标定义,不取决于字段长什么样。
原因二:补零要先建立完整的统计范围。事实表只记录发生过的业务事件,它天然不会记录「没有发生」的组合。比如统计每天每个渠道的订单数,事实表里只有三行:21 日 APP 100 单、小程序 20 单,23 日 APP 80 单。那 22 日的小程序,到底是什么?可能当天确实没有小程序订单,也可能小程序数据没有同步成功;还可能是渠道字段解析失败后被过滤,或者统计任务只跑了部分分区。所以要先生成完整的「日期 × 渠道」网格:30 天乘 5 个渠道,就是 150 行。再拿网格左连接事实聚合,每个组合都有一行,没订单的组合按规则补 0,最后和采集分区、源端对账。只有确定任务覆盖完整、业务上确实没有发生,才把聚合结果补成 0。SQL 分三段写。第一段 order_agg,先聚合事实表,只保留真实发生过的订单。按日期和渠道分组,订单数用 COUNT DISTINCT order_id,GMV 用 SUM pay_amount。第二段 date_channel_grid:日期维度 CROSS JOIN 渠道维度,生成完整的组合。第三段拿网格 LEFT JOIN 聚合结果,日期和渠道都相等才匹配,再用 COALESCE 把空的订单数和 GMV 补成 0。这里的关键不是 COALESCE,而是 date_channel_grid,它先定义了哪些日期和渠道应该出现在结果里。补零不是凭空制造业务数据,而是把统计范围内没有发生的组合,显式表达出来。
原因三:不同聚合函数,对 NULL 的处理方式不一样。COUNT(*) 统计行数,不会因为某个字段是 NULL 而少算;COUNT 某一列,只数非空的行。SUM、AVG、MIN、MAX 都会忽略 NULL,AVG 只对非空值求平均。原稿说 SUM 全是 NULL 时「可能」返回 NULL,这在 Spark、Hive、MySQL 里都是确定行为:输入全是 NULL,或者一行都没有,SUM、AVG、MIN、MAX 都返回 NULL,而 COUNT 返回 0。举个例子,三条订单的支付数值分别是 100、NULL 和 0。COUNT(*) 等于 3,COUNT(pay_amount) 等于 2,SUM 等于 100,AVG 等于 50。如果先把 NULL 补成 0 再平均,就变成 100 除以 3,约等于 33.33。这两个平均值,回答的不是同一个问题。AVG(pay_amount),是已知数值的订单的平均值;补零之后的 AVG,是把未知数值也当成零元订单之后的平均值。后者只有在业务明确「空值就代表 0」时才成立。所以要先定义指标的分母和业务含义,再选 COUNT(*)、COUNT 列、SUM 或 COALESCE。COALESCE 是 SQL 工具,不是指标口径;先决定这个空值代表什么,再决定要不要用它。
原因四:比率指标尤其不能无脑补零。以支付转化率为例:创建订单 100 个,支付 0 个,转化率是 0%。这是确定的 0%,因为分母存在而且大于 0,只是没有订单支付。但如果创建订单数也是 0,这一天根本没有可计算的分母,转化率就不是 0%。数仓里可以保留 NULL,报表层展示成短横线、N/A,或者按产品约定处理。前面专题讲过,转化率是由两个指标算出来的复合指标,分子分母都要先有确定口径。有分母但没有结果,是 0;连分母都没有,通常是 NULL 或 N/A。转化率、通过率、留存率都一样。推荐写法:先用 CASE WHEN 判断分母大于 0,再计算比率,否则返回 NULL。不推荐的写法:用 NULLIF 把分母 0 变成 NULL,外面又套一层 COALESCE 补成 0。这样「没有流量的一天」和「有 100 个订单却一个都没支付的一天」,都会显示成 0%。补充一个方言差异:MySQL、Hive 和 Spark 3.5 默认下,除以 0 会返回 NULL;但 Spark 4.0 默认开启 ANSI 模式,直接除以 0 会报错。所以分母一定要显式判断。
那补零应该放在哪一层?ODS 尽量保存源系统原始值;DWD 负责清洗和标准化,但也不该把所有 NULL 改成 0。比如支付数值为 NULL,原因是源系统漏传,DWD 应该保留空值,再加一个质量状态,例如 data_quality_status 等于 MISSING_SOURCE_VALUE,或者把异常记录写进脏数据表。这样下游知道,这是一个待处理的数据问题,而不是一笔支付为 0 的订单。DWS 和 ADS 面向分析和报表,可以按指标定义,把统计范围内没有发生的结果展示成 0。每日订单数、每日 GMV,无订单可以补 0;支付转化率,没有创建订单,通常保留 NULL;客单价分母为 0,保留 NULL;商品平均评分,没有评价也不等于 0 分。补零越靠近指标消费层,语义越清晰:底层保留证据,上层负责表达。复杂业务里,只留一个数值字段不一定够,建议把「值」和「状态」分开。metric_value 存指标值,is_observed 标记是否观测到记录,data_quality_status 存数据质量状态。看每日渠道指标:21 日 APP 100 单,状态正常;22 日 APP 是 0,状态正常,表示确认无订单。22 日小程序是 NULL,没观测到,状态是采集失败,表示数据未采到。23 日 H5 也是 NULL,但状态是不适用,因为这个渠道当天还没开放。这样报表能把「确认无订单」显示成 0,又不会把「数据未采到」伪装成 0。报表想知道展示什么,质量系统想知道数据有没有按时到,它们关注的不是同一个问题。所以,这比把所有状态压缩成一个 0 更可靠。
把三者放在一起对比。数据行:0 和 NULL 都存在,没有记录就是不存在。明确数值:0 有,就是零;NULL 没有,是未知或不适用;没有记录,连这一行都没有。典型含义:0 是没发生、数量为零;NULL 是缺失、未知、暂不适用;没有记录是未覆盖、未发生或未采集。参与计算:0 可以直接算,NULL 通常要特殊处理,没有记录必须先补齐行。SUM 时,0 贡献 0,NULL 被忽略,没有记录不参与;COUNT(*) 时,0 和 NULL 都计数,没有记录不计数;COUNT 某一列时,只有 0 那行计数。数据质量上,0 通常正常,NULL 可能要排查,没有记录要判断原因。报表展示:0 直接展示;NULL 展示空、N/A 或约定值;没有记录要先补齐,再展示 0 或空。对比的核心不是哪种写法更好,而是这三个值背后的业务语义,不能互相替代。
再看两个常见 SQL 错误。错误一:只用 COALESCE,补不出不存在的行。直接对订单明细按日期 GROUP BY,外面套一层 COALESCE(SUM(...), 0)。事实表里没有 22 日这一行,分组结果里也没有它,COALESCE 根本没有机会执行。正确做法:先把明细按天聚合成 daily_order,再用日期维度 LEFT JOIN 它,最后对 GMV 和订单数 COALESCE 补 0;日期范围的过滤,写在日期维度这张左表上。错误二:LEFT JOIN 之后,用 COUNT(*) 判断事实表有没有数据。即使当天没有订单,日期维度那一行仍然在,补齐的右表字段全是 NULL。原稿说 COUNT(*)「可能」返回 1,其实没有订单的日期,一定会数成 1。要统计订单,应该数事实表的非空主键:COUNT(o.order_id) 只有匹配到订单才计数,没订单就是 0。
最后用一棵完整的决策树,判断这个指标到底要不要补零。第一步,先明确统计粒度:日期、渠道、商品,还是用户。统计范围内这一行不该存在,就是没有记录,不强行补零。该存在,再问数据是否按时采集:没有,就保留 NULL 或质量状态,触发告警和补数。采集正常,再问业务是否确实没有发生:值未知,就保留 NULL,标记缺失原因。确实没有发生,看指标类型:计数或可加的数值,聚合后补 0;其他指标按业务定义处理,不默认补 0。如果是比率指标,先判断分母,分母为 0 就是 NULL 或 N/A。所有分支,最后都要输出指标,并且记录口径。
面试可以这样回答。我会先区分三种语义:0 表示数据行存在、指标确定为零;NULL 表示行存在,但值未知、缺失或不适用;没有记录,表示这条统计行根本不存在。补零不能只写 COALESCE,它只能处理已有行的空值,补不出事实表里缺失的日期或渠道组合。我的做法是先用日期、渠道等维度生成完整统计范围,再左连接事实聚合;业务确认「没有发生」就等于 0,就在 DWS 或 ADS 层补零。如果是数值未采集、指标不适用,或者比率分母为 0,就保留 NULL 或展示 N/A,同时加上数据质量状态。最终看指标口径:计数和可加的数值经常可以补零,但平均值、转化率、评分不能无脑补零。
追问一:SUM 遇到全是 NULL,返回什么?Spark、Hive 和 MySQL 都一样:SUM 忽略 NULL,一个有效值都没有,就返回 NULL。如果业务确认这个范围内没有发生就该展示 0,可以在聚合结果外层写 COALESCE(SUM(...), 0)。注意,这是对指标结果做表达转换,不代表底层每条记录的数值都该改成 0。追问二:为什么 COUNT(*) 和 COUNT 某一列结果不一样?COUNT(*) 统计行数,列值是 NULL 也算;COUNT 某一列,只数这一列非空的行。所以日期维度 LEFT JOIN 事实表后,要数真实订单,就用 COUNT(order_id),并且确保 order_id 是事实表的非空主键。追问三:没有记录,就一定要补成 0 吗?不一定。要先确认这个组合在不在统计范围内,数据采集有没有成功。某渠道当天还没开通,可能是不适用;某个分区没采到,应该告警和补数;只有统计范围包含它、任务正常完成、业务确实没发生,订单数或 GMV 才适合补 0;否则,补零会掩盖数据缺失。追问四:转化率分母为 0,为什么不返回 0?分母大于 0、分子为 0,是有 100 个创建订单却一个都没支付,转化率明确是 0%;分母不存在,是当天没有创建订单,根本没有样本,返回 0% 会让人误以为发生了转化失败。所以通常数仓保留 NULL,报表层展示 N/A 或短横线。追问五:补零应该放在 DWD 层,还是 ADS 层?没有绝对答案。通常底层保留原始语义,DWD 清洗并标记缺失原因,DWS 或 ADS 按口径表达为 0 或 N/A。如果「无订单补 0」的规则被多个下游复用,可以在 DWS 统一沉淀;如果只是某张报表的展示需求,放在 ADS 更灵活。关键是,不要在底层把未知值永久改成 0。
最后总结:0 是确定的结论,NULL 是待解释的信号,没有记录是这一行根本不存在。补零的顺序是:先补齐统计范围,再判断没有发生是否等于 0,最后才轮到 COALESCE。比率先看分母;底层保留证据,上层负责表达;值和质量状态分开存。下次想一把 COALESCE 补零之前,先问一句:这个空,到底是没发生,还是没采到?