BigQuery中跨表统计标签模式在文档中的匹配次数

需求说明 #

现有两张BigQuery表：documents（存储文档内容）和labels（存储标签与对应匹配模式），需统计每个label下所有pattern在document字段中的精确匹配总次数，最终按文档ID和标签维度输出统计结果。

数据规模参考：documents表约1亿条记录，labels表约1000行。

示例表结构 #

WITH documents AS (
  SELECT 1 AS id, "foo bar, foo baz" AS document UNION ALL
  SELECT 2, "foo bar bar qux" UNION ALL
  SELECT 3, "etc blah blah"
),

labels AS (
  SELECT 'FOO_LABEL' AS label, 'foo' AS pattern UNION ALL
  SELECT 'FOO_LABEL', 'qux' UNION ALL
  SELECT 'BAR_LABEL', 'bar' UNION ALL
  SELECT 'ETC_LABEL', 'etc'
)

预期输出 #

id, label, cnt
1,  FOO_LABEL, 2
1,  BAR_LABEL, 1
2,  FOO_LABEL, 2
2,  BAR_LABEL, 2
3,  ETC_LABEL, 1

解决方案 #

针对数据规模，以下提供两种高效的实现方案：

方案1：正则聚合匹配（支持含特殊字符的pattern） #

先将同一标签下的所有pattern合并为正则表达式，利用REGEXP_EXTRACT_ALL统计匹配次数，避免全量笛卡尔积的性能损耗：

WITH processed_labels AS (
  -- 按标签聚合，将pattern转为带单词边界的正则（确保精确匹配）
  SELECT 
    label,
    CONCAT(r'\b(', STRING_AGG(REGEXP_ESCAPE(pattern), '|'), r')\b') AS regex_pattern
  FROM labels
  GROUP BY label
)

SELECT
  d.id,
  pl.label,
  ARRAY_LENGTH(REGEXP_EXTRACT_ALL(d.document, pl.regex_pattern)) AS cnt
FROM documents d
CROSS JOIN processed_labels pl
-- 过滤无匹配的结果
WHERE ARRAY_LENGTH(REGEXP_EXTRACT_ALL(d.document, pl.regex_pattern)) > 0
ORDER BY d.id, pl.label;

• 优势：支持包含特殊字符的pattern，通过REGEXP_ESCAPE避免正则语法冲突
• 注意：若pattern不需要单词边界（比如匹配子串而非独立单词），可移除\b

方案2：拆分单词后JOIN统计（仅适用于单词类pattern） #

将文档拆分为独立单词，与labels表做JOIN后聚合统计，性能更优：

WITH document_words AS (
  -- 拆分文档为独立单词（自动过滤标点）
  SELECT
    id,
    REGEXP_EXTRACT_ALL(document, r'\b\w+\b') AS words
  FROM documents
),
unnested_words AS (
  -- 展开单词数组
  SELECT
    id,
    word
  FROM document_words
  CROSS JOIN UNNEST(words) word
)

SELECT
  uw.id,
  l.label,
  COUNT(*) AS cnt
FROM unnested_words uw
-- 精确匹配单词与pattern
JOIN labels l ON uw.word = l.pattern
GROUP BY uw.id, l.label
ORDER BY uw.id, l.label;

• 优势：利用BigQuery的哈希JOIN优化，处理1亿级文档时性能更稳定
• 限制：仅适用于pattern为纯单词的场景，若pattern含特殊字符（如标点、符号）则不适用

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