google-bigquery - 两个表之间的BigQuery匹配词计数

Question

给定这两个表(documents 和 labels)，我如何从 中找到所有标签的 pattern 的匹配计数labels 表，在documents 表的document 字段中找到(计算精确匹配，使用正则表达式是可选的)

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

与this的区别问题是我需要实际匹配计数

与this不同质疑我的模式来自一个单独的表

标签表中有约 100M 文档和约 1000 行

Answer 1

考虑以下方法

select id, label, count(*) cnt
from documents, unnest(regexp_extract_all(document, r'[\w]+')) pattern
join labels
using(pattern)
group by id, label       

如果应用于您问题中的示例数据 - 输出为