NULL / 默认值语义怎么约定

难度与前置

难度：进阶
前置：做过贴源 ODS 与清洗 DWD；遇到过「空串算不算缺」的口径扯皮。
读完能做什么：在团队内统一 NULL / 空串 / 哨兵默认值语义，落到 DDL、写入与质量规则，并用 SQL 验收。

适用场景

多源接入后同一字段有时 NULL、有时 ''、有时 -1 / unknown
指标对「未知用户」「未知渠道」要单独出桶，不能 silently 并进某类
Join、去重、对账因空值语义不一致导致行数漂

不适用：

纯日志原文归档、明确「原样保留、不做语义」的 ODS 冷存
业务库内 OLTP 约束已强制 NOT NULL 且仓内只读副本、无需再解释
与分区策略无关的纯存储格式选型

真实场景（一条）

广告明细 ODS 里 channel 有 NULL、''、unknown、0 四种「没渠道」。DWD 有人用 nvl(channel,'other')，有人用 ''，ADS 渠道 UV 与财务对账差 3%。约定：ODS 保留原文；DWD 统一映射为 NULL 表示「源未给」；业务默认桶用独立码 __UNKNOWN__；禁止用 0 当未知。上线后口径对齐，周对账 diff 归零。

怎么做（步骤清单）

1. 先立三条语义（写进建模规范）

符号	含义	建议落点
`NULL`	源未提供 / 不可得	DWD 及以后优先
`''`（空串）	源明确给了空字符串	仅 ODS 保留；DWD 一般转 NULL 或业务码
哨兵值（`-1`/`0`/`unknown`）	历史兼容或外部枚举	登记白名单，禁止新增扩散

text

原则：
1. 「未知」是一等公民，要有统一业务码或统一 NULL，二选一写死
2. ODS 尽量原样；语义归一放在 DWD
3. 禁止用有业务含义的数字（如渠道 0）兼做「未知」

2. 分层约定表

层	NULL	空串	默认值 / 哨兵
ODS	保留源 NULL	保留	不擅自填
DWD	归一后的「缺失」	转 NULL 或 `__EMPTY__`	仅登记过的映射
DWS/ADS	聚合时明确是否计入	一般不应再出现空串维度键	未知桶显式维度

3. DDL 与注释写清

sql

-- DWD 示意：注释即契约
CREATE TABLE IF NOT EXISTS dwd_ad_click_di (
  click_id      STRING COMMENT '点击唯一键，非空',
  user_id       STRING COMMENT '用户 ID；未知时为 NULL，禁止空串',
  channel_code  STRING COMMENT '渠道码；未知统一 __UNKNOWN__，禁止 NULL 与空串混用',
  price         DECIMAL(18,4) COMMENT '成交价；源缺失为 NULL，禁止填 0 伪装',
  event_ts      TIMESTAMP COMMENT '事件时间，非空'
)
PARTITIONED BY (dt STRING COMMENT '业务日 yyyy-MM-dd')
STORED AS ORC
TBLPROPERTIES ('comment'='DWD 点击明细；空值语义见字段 COMMENT');

4. 写入映射（可照抄模板）

sql

INSERT OVERWRITE TABLE dwd_ad_click_di PARTITION (dt='${biz_date}')
SELECT
  click_id,
  NULLIF(TRIM(user_id), '') AS user_id,
  CASE
    WHEN channel IS NULL OR TRIM(channel) = '' THEN '__UNKNOWN__'
    WHEN LOWER(TRIM(channel)) IN ('unknown','null','none','0','-1') THEN '__UNKNOWN__'
    ELSE TRIM(channel)
  END AS channel_code,
  CASE WHEN price IS NULL OR price < 0 THEN NULL ELSE price END AS price,
  event_ts
FROM ods_ad_click_di
WHERE dt = '${biz_date}'
  AND click_id IS NOT NULL
  AND TRIM(click_id) <> '';

5. Join / 去重纪律

sql

-- 错误：NULL 键 Join 直接丢「未知」侧
-- 正确：先归一再 Join，或显式保留未知桶
SELECT a.*, b.channel_name
FROM dwd_ad_click_di a
LEFT JOIN dim_channel_df b
  ON a.channel_code = b.channel_code
 AND b.dt = '${biz_date}';
-- dim 必须有 channel_code='__UNKNOWN__' 行，否则未知全变 NULL 名称

6. 质量规则挂钩

规则	示例阈值
主键非空	`click_id` NULL 率 = 0
禁止空串主键	trim 后空串 = 0
未知码白名单	`channel_code` 只能是维表码 ∪ `__UNKNOWN__`
金额 NULL vs 0	「缺失」与「零元成交」分计

与质量闭环、对账共用同一套语义表。

7. 指标层口径声明

text

UV：user_id IS NOT NULL 才计入；未知用户进「匿名桶」另列
渠道 GMV：channel_code='__UNKNOWN__' 单独一行，禁止摊进 Top1 渠道
客单价：分母排除 price IS NULL；零元单单独标签，不与 NULL 合并

常见坑（至少牢记这些）

nvl(x,0) / coalesce 一把梭
把「没传」变成「真是 0」，财务与算法两边炸。
ODS 空串、DWD NULL、ADS 又填 unknown
三层三语义，对账永远对不齐。
用有业务含义的枚举兼未知
如 channel=0 既是「自然量」又是「未知」，历史不可逆。
Join 条件两侧空值不对称
左表 __UNKNOWN__、右表 NULL，维度名全丢。
COUNT(col) 当行数
NULL 不计入，误判丢数；行数用 COUNT(*)，非空用 COUNT(col)。
Spark / Hive 空串与 NULL 比较行为混用
col = '' 与 col IS NULL 漏一侧，清洗不彻底。

如何验收（可量化）

[ ] 建模规范文档有 NULL/空串/哨兵三节，评审签字或 Wiki 版本号可溯
[ ] DWD 字段 COMMENT 写明未知策略
[ ] 未知渠道/用户有独立桶，ADS 可查到 __UNKNOWN__
[ ] 质量规则：主键空串率=0；哨兵值仅出现在白名单
[ ] 与源系统抽样对账：NULL 语义一致，不把 0 当缺失

sql

-- 1) 空串与 NULL 混用巡检
SELECT
  SUM(CASE WHEN user_id IS NULL THEN 1 ELSE 0 END) AS user_null_cnt,
  SUM(CASE WHEN user_id = '' THEN 1 ELSE 0 END) AS user_empty_cnt,
  SUM(CASE WHEN channel_code IS NULL THEN 1 ELSE 0 END) AS ch_null_cnt,
  SUM(CASE WHEN channel_code = '' THEN 1 ELSE 0 END) AS ch_empty_cnt,
  SUM(CASE WHEN channel_code = '__UNKNOWN__' THEN 1 ELSE 0 END) AS ch_unknown_cnt
FROM dwd_ad_click_di
WHERE dt = '${biz_date}';
-- 期望：user_empty_cnt=0, ch_null_cnt=0, ch_empty_cnt=0；unknown 可 >0

sql

-- 2) 禁止哨兵污染
SELECT channel_code, COUNT(*) AS cnt
FROM dwd_ad_click_di
WHERE dt = '${biz_date}'
  AND channel_code IN ('0','-1','unknown','null','none','NULL')
GROUP BY channel_code;
-- 期望：0 行（已映射为 __UNKNOWN__）

sql

-- 3) 指标分母声明验收：NULL 价不进客单价
SELECT
  COUNT(*) AS order_cnt,
  COUNT(price) AS priced_cnt,
  SUM(CASE WHEN price = 0 THEN 1 ELSE 0 END) AS zero_price_cnt,
  AVG(price) AS avg_price_wrong,          -- 含 NULL 行为因引擎而异，勿直接用
  AVG(CASE WHEN price IS NOT NULL THEN price END) AS avg_price_ok
FROM dwd_ad_click_di
WHERE dt = '${biz_date}';

bash

# 规范与代码门禁示意
grep -R "nvl(.*price.*,.*0)" jobs/dwd/ && echo "FAIL: price 禁止 nvl 成 0"
grep -R "__UNKNOWN__" jobs/dwd/dim_channel || echo "FAIL: 维表缺未知码"

NULL / 默认值语义怎么约定 ​

适用场景 ​

真实场景（一条） ​

怎么做（步骤清单） ​

1. 先立三条语义（写进建模规范） ​

2. 分层约定表 ​

3. DDL 与注释写清 ​

4. 写入映射（可照抄模板） ​

5. Join / 去重纪律 ​

6. 质量规则挂钩 ​

7. 指标层口径声明 ​

常见坑（至少牢记这些） ​

如何验收（可量化） ​

相关面试题 ​