在BigQuery中计算与结果关联词汇的准确率

我来帮你搞定这个需求！首先咱们得明确核心目标：从每个文档的文本里提取词汇，统计每个词汇关联的唯一文档总数，再算出这些文档里结果为True的比例（也就是你说的“准确率”）。

首先得处理文本的清洗和拆分——毕竟原始文本里可能有大小写差异、标点符号，这些都会导致同一个词被当成不同的条目（比如"This"和"this"，"document."和"document"）。咱们用BigQuery的字符串函数和UNNEST来处理：

完整查询语句 #

WITH cleaned_words AS (
  SELECT
    doc_id,
    outcome,
    -- 先转小写统一格式，去掉非字母数字/空格的标点，再拆分词汇，最后去掉多余空格
    TRIM(word) AS word
  FROM
    `你的项目ID.你的数据集ID.你的表名`,
    -- 按空格拆分清洗后的文本
    UNNEST(SPLIT(REGEXP_REPLACE(LOWER(text), r'[^a-z0-9\s]', ''), ' ')) AS word
  -- 过滤掉拆分后产生的空字符串（比如多个连续空格导致的无效条目）
  WHERE TRIM(word) != ''
),
word_metrics AS (
  SELECT
    word,
    -- 统计该词汇出现的唯一文档数量（避免同一文档内重复出现同一词导致计数虚高）
    COUNT(DISTINCT doc_id) AS docs_count,
    -- 统计该词汇所在文档中，结果为True的唯一文档数
    COUNT(DISTINCT IF(outcome = 'True', doc_id, NULL)) AS true_docs
  FROM cleaned_words
  GROUP BY word
)
SELECT
  word,
  docs_count,
  -- 计算准确率并转成百分比格式，保留两位小数
  FORMAT('%.2f%%', (true_docs / docs_count) * 100) AS accuracy
FROM word_metrics
-- 可按需排序，比如按文档数从多到少
ORDER BY docs_count DESC;

关键步骤解释 #

1. 清洗拆分词汇：cleaned_words这个公共表表达式（CTE）先把文本转成小写，用正则去掉标点，再按空格拆分成单个词汇，最后过滤掉空词，确保每个条目都是有效的词汇。
2. 统计核心指标：word_metrics里用COUNT(DISTINCT)来统计每个词汇关联的唯一文档数，以及其中结果为True的文档数——这里一定要用DISTINCT，不然同一文档里多次出现同一个词会重复计数，导致结果不准。
3. 计算并格式化准确率：最后一步用FORMAT函数把准确率转成百分比格式，看起来更直观；如果不需要百分比，直接保留小数也可以。

小提醒 #

你给出的示例结果里word列写的是doc_id，这应该是笔误吧？如果你的需求其实是按文档ID统计（而不是词汇），那逻辑会简单很多——直接按doc_id分组，统计每个文档的词汇数即可，但根据你描述的“提取每个文档的单词并计算每个单词的准确率”，上面的查询应该是符合需求的。

备注：内容来源于stack exchange，提问作者mundos