在BigQuery中按字符拆分Score列并转为多列的技术问询

我明白你的需求——把用>分隔的Score字段拆分成对应数量的列，并且以全表最大的分段数作为列数，不足的补0或者NA。你之前用SPLIT得到行而不是列，确实需要额外的步骤来转成宽表格式，下面给你两种可行的方案：

方案1：使用数组索引+静态列（适合已知最大分段数的情况） #

如果已经明确全表的最大分段数（比如你例子里的3），可以直接通过数组索引提取每个位置的分数，用IFNULL给不足的位置补0，或者保留NULL（对应需求里的NA）：

WITH split_scores AS (
  SELECT
    Name,
    -- 把Score拆转为整数数组
    ARRAY(SELECT CAST(score AS INT64) FROM UNNEST(SPLIT(Score, ' > ')) score) AS score_array
  FROM
    `你的项目名.你的数据集名.你的表名`
)
SELECT
  Name,
  IFNULL(score_array[OFFSET(0)], 0) AS Score_1,
  IFNULL(score_array[OFFSET(1)], 0) AS Score_2,
  IFNULL(score_array[OFFSET(2)], 0) AS Score_3
FROM split_scores;

如果想要用NA（即BigQuery中的NULL），直接去掉IFNULL的第二个参数即可，比如score_array[OFFSET(0)] AS Score_1。

方案2：动态生成列（适应任意最大分段数） #

如果不确定全表的最大分段数，需要先自动计算这个数值，再动态生成对应列。这种情况要用到BigQuery的EXECUTE IMMEDIATE执行动态SQL：

DECLARE max_segments INT64;
-- 第一步：计算全表的最大分段数
SET max_segments = (
  SELECT MAX(ARRAY_LENGTH(SPLIT(Score, ' > ')))
  FROM `你的项目名.你的数据集名.你的表名`
);

-- 第二步：动态生成SQL并执行
EXECUTE IMMEDIATE FORMAT("""
WITH split_scores AS (
  SELECT
    Name,
    ARRAY(SELECT CAST(score AS INT64) FROM UNNEST(SPLIT(Score, ' > ')) score) AS score_array
  FROM `你的项目名.你的数据集名.你的表名`
)
SELECT
  Name,
  %s
FROM split_scores
""", 
-- 自动生成每个Score_N的字段表达式
STRING_AGG(
  FORMAT("IFNULL(score_array[OFFSET(%d)], 0) AS Score_%d", i, i+1),
  ', '
)
FROM UNNEST(GENERATE_ARRAY(0, max_segments-1)) i
);

这段代码会自动根据全表的最大分段数生成对应列，比如如果最大分段数是4，就会生成Score_1到Score_4，不足的位置补0。同样，要换成NULL的话，把IFNULL(..., 0)改成score_array[OFFSET(%d)]即可。

为什么直接用SPLIT会得到行？ #

因为SPLIT返回的是数组，配合UNNEST会把数组的每个元素拆成单独的行，这是长表格式，而你需要的是宽表格式，所以需要通过数组索引或者透视转换，上面的两种方案就是解决这个格式转换的核心逻辑。

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