如何在BigQuery中正确聚合拆分后首字母大写的单词

你的代码核心问题是错误地使用了UNNEST后的OFFSET作为分组键：OFFSET是每个拆分单词在其所在行数组中的位置，比如第一行的"corki"位置是0，第二行的"dog"位置是0、"corki"位置是1，第三行的"golden"位置是0、"retriever"位置是1。GROUP BY pos会把所有行中同位置的单词聚合到一起，导致结果不符合预期。

下面提供两种可行的解决方案：

方案一：保留原行标识修正分组逻辑 #

给每一行添加唯一标识，拆分后按原行标识分组，确保同一行的单词被正确聚合：

WITH array_table AS (
  SELECT 1 AS row_id, "corki" AS dog UNION ALL
  SELECT 2, "dog corki" UNION ALL
  SELECT 3, "golden retriever"
),
split_table AS (
  SELECT row_id, SPLIT(dog, " ") AS split_word
  FROM array_table
)
SELECT 
  STRING_AGG(CONCAT(UPPER(SUBSTR(unnest_split_word, 1, 1)), LOWER(SUBSTR(unnest_split_word, 2))), ' ') AS capitalized_dog
FROM 
  split_table,
  UNNEST(split_table.split_word) AS unnest_split_word
GROUP BY row_id
ORDER BY row_id

方案二：用正则表达式简化实现 #

不需要拆分再聚合，直接通过正则匹配每个单词的首字母并转大写，代码更简洁高效：

WITH array_table AS (
  SELECT "corki" AS dog UNION ALL
  SELECT "dog corki" UNION ALL
  SELECT "golden retriever"
)
SELECT 
  REGEXP_REPLACE(dog, r'\b(\w)', UPPER(r'\1')) AS capitalized_dog
FROM array_table

两种方案都会返回预期结果：

Corki
Dog Corki
Golden Retriever

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