如何在BigQuery中将字符串拆分为单个单词并关联对应ID？

我的BigQuery表包含一个String类型列，数据示例如下：

WITH
  food_thoughts AS (
    SELECT 1 as id, 'I want a kebab' AS thought UNION ALL
    SELECT 2, 'Pizza is delicious' UNION ALL
    SELECT 3, 'All you can eat French fries!'
  )
SELECT * FROM food_thoughts

对应的表结构：

需求：将每个句子中的每个单词拆分到独立单元格，并关联对应的id列，预期输出如下：

实现方案 #

使用BigQuery的SPLIT函数将字符串按空格拆分为单词数组，再通过UNNEST函数将数组展开为多行，同时保留对应的id列。完整SQL如下：

WITH
  food_thoughts AS (
    SELECT 1 as id, 'I want a kebab' AS thought UNION ALL
    SELECT 2, 'Pizza is delicious' UNION ALL
    SELECT 3, 'All you can eat French fries!'
  )
SELECT 
  id,
  word
FROM food_thoughts,
UNNEST(SPLIT(thought, ' ')) AS word

关键说明：

• SPLIT(thought, ' ')：将thought列的字符串按空格分割成单词数组
• UNNEST(...) AS word：将数组中的每个元素展开为单独的行，并命名为word列
• 逗号分隔food_thoughts和UNNEST是BigQuery中隐式CROSS JOIN UNNEST的写法，会将每行数据与拆分后的单词一一关联

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