如何在BigQuery中折叠同一行变量，提取非空值并保留前3个？

原始数据 #

WITH GradeIN AS (
  SELECT 1 AS Student, NULL AS Grade1, 'A+' AS Grade2, NULL AS Grade3, 'C' AS Grade4, 'A' AS Grade5
   UNION ALL
  SELECT 2 AS Student, 'C' AS Grade1, 'A' AS Grade2, 'B' AS Grade3, 'B' AS Grade4, 'A' AS Grade5
   UNION ALL
  SELECT 3 AS Student, 'F' AS Grade1, 'F' AS Grade2, 'A' AS Grade3, 'B' AS Grade4, 'C' AS Grade5
   UNION ALL
  SELECT 4 AS Student, NULL AS Grade1, NULL AS Grade2, NULL AS Grade3, NULL AS Grade4, 'C' AS Grade5
   UNION ALL
  SELECT 5 AS Student, 'A' AS Grade1, 'A' AS Grade2, NULL AS Grade3, NULL AS Grade4, NULL AS Grade5
)
SELECT * FROM GradeIN

需求说明 #

提取每个学生Grade1到Grade5列中的非空值，按原列顺序左移，生成最多3个新列New_Grade1、New_Grade2、New_Grade3，不足3个的位置用NULL填充。

解决方案SQL #

WITH GradeIN AS (
  SELECT 1 AS Student, NULL AS Grade1, 'A+' AS Grade2, NULL AS Grade3, 'C' AS Grade4, 'A' AS Grade5
   UNION ALL
  SELECT 2 AS Student, 'C' AS Grade1, 'A' AS Grade2, 'B' AS Grade3, 'B' AS Grade4, 'A' AS Grade5
   UNION ALL
  SELECT 3 AS Student, 'F' AS Grade1, 'F' AS Grade2, 'A' AS Grade3, 'B' AS Grade4, 'C' AS Grade5
   UNION ALL
  SELECT 4 AS Student, NULL AS Grade1, NULL AS Grade2, NULL AS Grade3, NULL AS Grade4, 'C' AS Grade5
   UNION ALL
  SELECT 5 AS Student, 'A' AS Grade1, 'A' AS Grade2, NULL AS Grade3, NULL AS Grade4, NULL AS Grade5
),
unpivoted AS (
  SELECT 
    Student,
    Grade,
    -- 提取成绩列的数字序号，保证按原始列顺序排序
    CAST(REGEXP_EXTRACT(grade_col, r'\d+') AS INT64) AS col_order
  FROM GradeIN
  UNPIVOT (
    Grade FOR grade_col IN (Grade1, Grade2, Grade3, Grade4, Grade5)
  )
  WHERE Grade IS NOT NULL
),
ranked AS (
  SELECT
    Student,
    Grade,
    ROW_NUMBER() OVER(PARTITION BY Student ORDER BY col_order) AS rn
  FROM unpivoted
),
pivoted AS (
  SELECT
    Student,
    MAX(IF(rn=1, Grade, NULL)) AS New_Grade1,
    MAX(IF(rn=2, Grade, NULL)) AS New_Grade2,
    MAX(IF(rn=3, Grade, NULL)) AS New_Grade3
  FROM ranked
  GROUP BY Student
)
-- 关联原表确保所有学生都被保留
SELECT 
  g.Student,
  p.New_Grade1,
  p.New_Grade2,
  p.New_Grade3
FROM GradeIN g
LEFT JOIN pivoted p ON g.Student = p.Student
ORDER BY g.Student

逻辑说明 #

1. UNPIVOT转成行：把多列成绩转为行数据，同时提取列的数字序号，保证后续排序符合原始列的顺序。
2. 过滤空值：直接剔除Grade为NULL的行，只保留有效成绩。
3. 排序编号：按学生分组，对每个有效成绩按原始列顺序标记序号（1、2、3...）。
4. PIVOT转回列：将序号前3的成绩转为新列，不足3个的位置自动填充NULL。
5. 关联原表：通过LEFT JOIN确保所有学生都出现在结果中，避免丢失只有少量有效成绩的学生。

执行结果 #

Student | New_Grade1 | New_Grade2 | New_Grade3
   1    |    A+      |    C       |     A
   2    |    C       |    A       |     B
   3    |    F       |    F       |     A
   4    |    C       |   null     |    null
   5    |    A       |    A       |    null

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