如何在BigQuery中计算字符串及文档名对的共同单词数？

我来帮你搞定BigQuery里相似文档的匹配问题！结合你给出的示例文档列表，咱们一步步实现你想要的结果，顺便也会讲清楚如何单独计算任意两个字符串的共同单词数。

一、生成两两文档的相似匹配结果 #

假设你的文档列表存在your_dataset.your_table表中，字段名为document_name。我们可以通过自连接+单词交集计算来生成你需要的匹配结果，完整SQL如下：

WITH processed_docs AS (
  -- 预处理文档名：去掉扩展名、转小写、拆分成单词数组
  SELECT
    document_name,
    -- 提取单词（忽略大小写、去掉非字母数字字符、排除扩展名）
    REGEXP_EXTRACT_ALL(
      LOWER(REGEXP_REPLACE(document_name, r'\.[a-z0-9]+$', '')),
      r'[a-z0-9]+'
    ) AS words
  FROM `your_dataset.your_table`
)
SELECT
  a.document_name AS `Document`,
  b.document_name AS `Matching Document`,
  -- 计算共同单词的数量
  ARRAY_LENGTH(
    ARRAY(
      SELECT DISTINCT word FROM UNNEST(a.words) word
      INTERSECT DISTINCT
      SELECT DISTINCT word FROM UNNEST(b.words) word
    )
  ) AS `Common Word Count`,
  -- 可选：列出所有共同单词
  ARRAY(
    SELECT DISTINCT word FROM UNNEST(a.words) word
    INTERSECT DISTINCT
    SELECT DISTINCT word FROM UNNEST(b.words) word
  ) AS `Common Words`
FROM processed_docs a
JOIN processed_docs b
  -- 避免重复匹配（比如A-B和B-A只保留一条），同时排除自己和自己匹配
  ON a.document_name < b.document_name
-- 可以根据共同单词数过滤，只显示相似度高的文档
WHERE ARRAY_LENGTH(
    ARRAY(
      SELECT DISTINCT word FROM UNNEST(a.words) word
      INTERSECT DISTINCT
      SELECT DISTINCT word FROM UNNEST(b.words) word
    )
  ) >= 1
ORDER BY `Common Word Count` DESC;

代码解释： #

1. 预处理阶段：
   • 先用REGEXP_REPLACE去掉文档名末尾的扩展名（比如.xlsx）
   • 转成小写后，用REGEXP_EXTRACT_ALL提取所有字母数字组成的单词，避免大小写和特殊字符干扰
2. 自连接：
   • 用a.document_name < b.document_name确保每对文档只匹配一次，不会出现重复的双向结果
3. 共同单词计算：
   • 通过INTERSECT DISTINCT取两个单词数组的交集，再用ARRAY_LENGTH得到共同单词的数量
   • 额外加了Common Words列，可以直观看到哪些单词是重复的

针对你给出的示例文档，运行后会得到类似这样的结果：

二、单独计算任意两个字符串的共同单词数 #

如果你需要随时计算任意两个字符串的共同单词数，可以封装一个自定义函数（UDF），这样调用起来更方便：

CREATE OR REPLACE FUNCTION `your_dataset.COUNT_COMMON_WORDS`(str1 STRING, str2 STRING)
RETURNS INT64
LANGUAGE js AS """
  // 定义拆分字符串为单词数组的辅助函数
  function getWords(str) {
    if (!str) return new Set();
    // 去掉扩展名、转小写、拆分单词、去重
    const cleanedStr = str.toLowerCase().replace(/\.[a-z0-9]+$/, '');
    const words = cleanedStr.match(/[a-z0-9]+/g) || [];
    return new Set(words);
  }
  
  const words1 = getWords(str1);
  const words2 = getWords(str2);
  // 计算交集的大小
  let count = 0;
  for (const word of words1) {
    if (words2.has(word)) count++;
  }
  return count;
""";

使用示例： #

-- 计算两个文档名的共同单词数
SELECT `your_dataset.COUNT_COMMON_WORDS`(
  'Spreadsheets Quality Control.xlsx',
  'Process of Quality Control.xlsx'
) AS common_word_count;

这个查询会返回2，对应共同单词quality和control。

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