在BigQuery的SQL数据库中识别法律实体名称模式的方法求助

针对千万级法律实体名称的模式检测需求，这里提供几个实用的BigQuery实现思路，重点聚焦提取XYZ这类高频重复的字符序列：

1. 直接提取字符级子串并统计频率 #

这是最直接的方法：遍历每个实体名称的所有可能子串，限定长度范围后统计出现频率，过滤出高频序列，适合寻找短字符重复（如XYZ）。

WITH entity_names AS (
  SELECT name FROM `your-project.your-dataset.target-table`
),
candidate_substrings AS (
  SELECT
    SUBSTR(name, pos, substr_len) AS target_substr,
    COUNT(*) AS occurrence_count
  FROM entity_names,
    -- 生成子串起始位置
    GENERATE_ARRAY(1, LENGTH(name)) AS pos,
    -- 限定子串长度（3-8个字符，可根据需求调整）
    GENERATE_ARRAY(3, 8) AS substr_len
  -- 确保子串不超出原字符串长度
  WHERE pos + substr_len - 1 <= LENGTH(name)
    -- 只保留字母数字组成的子串，过滤空格、符号等无效内容
    AND REGEXP_CONTAINS(SUBSTR(name, pos, substr_len), r'^[A-Za-z0-9]+$')
  GROUP BY target_substr
  -- 过滤低频子串，阈值根据数据量调整
  HAVING occurrence_count > 100
)
SELECT target_substr, occurrence_count
FROM candidate_substrings
ORDER BY occurrence_count DESC;

注意事项：

• 子串长度范围要合理，过短会出现大量无意义高频（如"St"），过长会导致计算量激增
• 可以提前过滤常见后缀（LLC/Inc/Group等），减少无效子串的生成

2. 先清理名称再提取核心子串 #

先移除法律实体常见的后缀、地址词汇，再提取子串，能有效减少干扰，聚焦真正的实体核心标识。

WITH cleaned_entity_names AS (
  SELECT
    -- 移除常见后缀和地址关键词，可根据业务扩展词汇表
    REGEXP_REPLACE(
      name,
      r'\b(LLC|Inc|Corp|Group|St|Street|Avenue|Road|Owner)\b',
      ''
    ) AS cleaned_name
  FROM `your-project.your-dataset.target-table`
),
core_substrings AS (
  SELECT
    SUBSTR(TRIM(cleaned_name), pos, substr_len) AS core_substr,
    COUNT(*) AS occurrence_count
  FROM cleaned_entity_names,
    GENERATE_ARRAY(1, LENGTH(TRIM(cleaned_name))) AS pos,
    GENERATE_ARRAY(3, 8) AS substr_len
  WHERE pos + substr_len - 1 <= LENGTH(TRIM(cleaned_name))
    AND REGEXP_CONTAINS(SUBSTR(TRIM(cleaned_name), pos, substr_len), r'^[A-Za-z]+$')
  GROUP BY core_substr
  HAVING occurrence_count > 100
)
SELECT core_substr, occurrence_count
FROM core_substrings
ORDER BY occurrence_count DESC;

优势：避免把"LLC""St"这类通用词汇误判为高频实体标识，结果更聚焦业务核心。

3. 结合聚类模型自动分组并提取簇内共性 #

如果需要将实体按共性分组后再审查，可以用BigQuery ML的聚类模型，先把相似实体归为一簇，再提取每个簇的高频子串。

-- 第一步：创建聚类模型（根据数据量调整簇数量）
CREATE OR REPLACE MODEL `your-project.your-dataset.entity_cluster_model`
OPTIONS(model_type='kmeans', num_clusters=150)
AS
SELECT
  name,
  -- 将名称分词作为聚类特征
  ML.TOKENIZE(REGEXP_REPLACE(name, r'[^A-Za-z]', ' ')) AS name_tokens
FROM `your-project.your-dataset.target-table`;

-- 第二步：预测聚类结果并提取簇内高频子串
WITH clustered_entities AS (
  SELECT
    name,
    CENTROID_ID AS cluster_id
  FROM ML.PREDICT(
    MODEL `your-project.your-dataset.entity_cluster_model`,
    SELECT name FROM `your-project.your-dataset.target-table`
  )
),
cluster_common_substrings AS (
  SELECT
    cluster_id,
    SUBSTR(name, pos, substr_len) AS common_substr,
    COUNT(*) AS occurrence_count,
    -- 计算子串在簇内的占比
    COUNT(*) / (SELECT COUNT(*) FROM clustered_entities WHERE cluster_id = c.cluster_id) AS cluster_ratio
  FROM clustered_entities c,
    GENERATE_ARRAY(1, LENGTH(name)) AS pos,
    GENERATE_ARRAY(3, 8) AS substr_len
  WHERE pos + substr_len - 1 <= LENGTH(name)
    AND REGEXP_CONTAINS(SUBSTR(name, pos, substr_len), r'^[A-Za-z]+$')
  GROUP BY cluster_id, common_substr
  -- 只保留簇内超过50%实体包含的子串
  HAVING cluster_ratio >= 0.5
)
SELECT cluster_id, common_substr, occurrence_count, cluster_ratio
FROM cluster_common_substrings
ORDER BY cluster_id, occurrence_count DESC;

适用场景：需要批量分组审查相似实体时，能自动关联同一实体的不同表述（如XYZ LLC/XYZ Group）。

性能优化建议 #

• 千万级数据优先使用分区表，通过分区过滤减少扫描量
• 先取10%数据做测试，调整子串长度、频率阈值等参数后再全量运行
• 对字符串长度做过滤，跳过过短（<5字符）的名称，减少无效计算

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