BigQuery：统计标题列单词出现次数时CTE报错求助

你的报错本质是语法错误引发的误导性提示，原代码存在三个关键问题，以下是修复方案：

问题点分析 #

• 递归CTE未声明RECURSIVE：BigQuery要求递归公共表表达式必须显式添加RECURSIVE关键字，否则会将CTE识别为普通表，导致"需要数据集限定"的错误提示。
• 列名未定义：初始查询中的1和INSTR结果未指定列名，后续引用start_pos、end_pos属于非法引用，这是核心语法错误。
• Oracle函数适配问题：原代码参考Oracle语法，BigQuery中递归拆分字符串的写法需要调整，同时可以用更高效的原生函数替代。

修复后的递归CTE方案 #

WITH RECURSIVE word_count AS (
  SELECT 
    Title,
    1 AS start_pos,
    INSTR(Title, ' ', 1) AS end_pos
  FROM `circulation2023.top_food`
UNION ALL
  SELECT 
    Title,
    end_pos + 1 AS start_pos,
    INSTR(Title, ' ', end_pos + 1) AS end_pos
  FROM word_count
  WHERE end_pos > 0
),
words AS (
  SELECT 
    CASE 
      WHEN end_pos = 0 THEN SUBSTR(Title, start_pos)
      ELSE SUBSTR(Title, start_pos, end_pos - start_pos)
    END AS word
  FROM word_count
)
SELECT 
  word,
  COUNT(*) AS frequency
FROM words
GROUP BY word
ORDER BY frequency DESC, word;

更高效的BigQuery原生函数方案 #

BigQuery提供REGEXP_EXTRACT_ALL可以直接拆分字符串为单词数组，无需递归，代码更简洁高效：

SELECT
  word,
  COUNT(*) AS frequency
FROM `circulation2023.top_food`,
UNNEST(REGEXP_EXTRACT_ALL(Title, r'\w+')) AS word
GROUP BY word
ORDER BY frequency DESC, word;

注：r'\w+'会匹配字母数字下划线组成的单词，如果需要支持带特殊字符的单词，可以调整正则表达式，比如r'[^ ]+'匹配非空格的连续字符。

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