BigQuery如何拆分字符串并生成新增classID列的展开行

调整后可直接运行的查询语句 #

WITH merged_classes AS (
  SELECT 
    id,
    gpa,
    classesA,
    classesB,
    -- 合并两个课程字段的拆分结果，空字段返回空数组避免无效值
    ARRAY_CONCAT(
      IF(TRIM(classesA) != '', SPLIT(classesA, ','), []),
      IF(TRIM(classesB) != '', SPLIT(classesB, ','), [])
    ) AS all_class_arr
  FROM students
)
SELECT 
  id,
  gpa,
  classesA,
  classesB,
  -- 去除拆分后课程名的前后空格
  TRIM(class_item) AS classId
FROM merged_classes,
-- 将数组打平为独立行
UNNEST(all_class_arr) AS class_item

核心修改说明 #

• 符合要求使用WITH AS语法预先处理课程合并逻辑，避免两次扫描原表，性能优于原写法的UNION ALL方案
• 用SPLIT拆分字符串后搭配UNNEST将数组格式的课程列表打平为单行单课程的结构
• 新增TRIM函数处理拆分后课程名前后的多余空格，和预期输出格式完全匹配
• 内置空值判断逻辑，若classesA/classesB为空则自动跳过，不会生成无效空行
• 完整保留原表所有字段，仅新增classId列，符合同记录仅classId不同的拆分规则

内容的提问来源于stack exchange，提问作者Pierre-Alexandre