如何在BigQuery SQL中将字符串列拆分为单字与相邻词对多行？

嘿，我刚好处理过类似的BigQuery数据转换需求，这就帮你实现把IndataTable转换成目标OutdataTable的逻辑！

核心思路 #

我们需要分两步处理再合并结果：

1. 把每个WordString拆分成单个单词，保留对应ID
2. 提取每个单词和它的下一个相邻单词，组成有序词对，同样保留ID
   最后用UNION ALL把两部分结果合并，就能得到你想要的输出。

完整BigQuery SQL代码 #

WITH split_words AS (
  SELECT
    ID,
    word,
    pos
  FROM
    IndataTable,
    UNNEST(SPLIT(REGEXP_REPLACE(WordString, r'\s+', ' '), ' ')) AS word WITH OFFSET pos
)
-- 单个单词的结果
SELECT ID, word AS result
FROM split_words
UNION ALL
-- 相邻有序词对的结果
SELECT
  ID,
  CONCAT(word, ' ', next_word) AS result
FROM (
  SELECT
    ID,
    word,
    LEAD(word) OVER (PARTITION BY ID ORDER BY pos) AS next_word
  FROM split_words
)
WHERE next_word IS NOT NULL
-- 可选：按ID和结果排序，让输出更规整
ORDER BY ID, result;

代码细节解释 #

• REGEXP_REPLACE(WordString, r'\s+', ' ')：先处理字符串里的多空格问题，把连续空格替换成单个空格，避免拆分出空字符串
• UNNEST(...) WITH OFFSET pos：拆分字符串数组的同时，记录每个单词在原字符串中的位置，用来确定相邻关系
• LEAD(word) OVER (PARTITION BY ID ORDER BY pos)：窗口函数，针对每个ID，按单词位置顺序获取下一个单词，这样就能和当前单词组成相邻词对
• WHERE next_word IS NOT NULL：过滤掉最后一个单词（它没有下一个单词，无法组成词对）
• UNION ALL：合并单个单词和词对的结果集，不会去重（完全匹配你的需求）

测试示例数据 #

用你给出的示例数据测试：

执行上述SQL后，输出会和你期望的OutdataTable完全一致：

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