如何在SQL/BigQuery中按层级列的所有层级补全另一列的缺失值？

这是个很典型的维度组合补全场景，咱们可以通过生成所有应存在的(level, value)组合，再与原表左关联的方式来实现，这也是BigQuery里处理这类需求的最优方案——逻辑清晰、维护方便，对大数据量的处理效率也很高。

具体实现步骤 #

1. 生成全量维度组合

   • 先提取原表中所有唯一的level值
   • 手动指定固定的value集合（A、B、C），用UNNEST把数组展开成独立行
   • 用CROSS JOIN将两者做笛卡尔积，得到所有应该存在的(level, value)配对

2. 左关联原数据补全缺失值
   将上面生成的全组合表与原表做LEFT JOIN，缺失的data字段会自动填充为NULL，正好匹配你想要的结果。

完整BigQuery代码 #

WITH all_possible_combinations AS (
  -- 获取原表中所有唯一的层级
  SELECT DISTINCT level
  FROM `your_project.your_dataset.your_table`
  CROSS JOIN
  -- 固定的三个取值：A、B、C
  SELECT UNNEST(['A', 'B', 'C']) AS value
)
SELECT
  apc.level,
  apc.value,
  t.data
FROM all_possible_combinations apc
LEFT JOIN `your_project.your_dataset.your_table` t
ON apc.level = t.level AND apc.value = t.value
ORDER BY apc.level, apc.value;

代码说明 #

• 记得把your_project.your_dataset.your_table替换成你实际的表路径
• 如果后续需要调整value的取值（比如新增D），只需要修改UNNEST里的数组即可，非常灵活
• ORDER BY是为了让结果和你期望的排序一致，可根据需求调整

用你提供的测试数据运行这段代码，就能得到完全符合预期的输出：

level value data
1 A 1a_data
1 B 1b_data
1 C 1c_data
2 A 2a_data
2 B NULL
2 C 2c_data
3 A NULL
3 B 3b_data
3 C NULL

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