BigQuery计算两字段连续匹配度 回填CEI表缺失model属性

你需求的核心是实现两个序列号的连续匹配长度计算，以下是可直接运行的完整实现，仅返回匹配结果不会修改原表，可直接用于方案价值验证： #

1. 临时UDF定义 #

以下UDF实现最长公共前缀连续匹配长度逻辑，完全匹配你给出的示例计算规则：

CREATE TEMP FUNCTION get_prefix_match_len(str_a STRING, str_b STRING)
RETURNS INT64
LANGUAGE js AS """
  let len = 0;
  const min_length = Math.min(str_a.length, str_b.length);
  for (let i = 0; i < min_length; i++) {
    if (str_a[i] === str_b[i]) len ++;
    else break;
  }
  return len;
""";

如果你需要支持任意位置的最长连续匹配，可以改用以下UDF：

CREATE TEMP FUNCTION get_max_consecutive_match_len(str_a STRING, str_b STRING)
RETURNS INT64
LANGUAGE js AS """
  let max_len = 0;
  for (let i = 0; i < str_a.length; i++) {
    for (let j = 0; j < str_b.length; j++) {
      let current_len = 0;
      while (i + current_len < str_a.length && j + current_len < str_b.length && str_a[i + current_len] === str_b[j + current_len]) {
        current_len++;
      }
      max_len = current_len > max_len ? current_len : max_len;
    }
  }
  return max_len;
""";

2. 完整查询逻辑 #

-- 此处可以替换为上面的任意匹配规则UDF
CREATE TEMP FUNCTION get_prefix_match_len(str_a STRING, str_b STRING)
RETURNS INT64
LANGUAGE js AS """
  let len = 0;
  const min_length = Math.min(str_a.length, str_b.length);
  for (let i = 0; i < min_length; i++) {
    if (str_a[i] === str_b[i]) len ++;
    else break;
  }
  return len;
""";

SELECT 
  account_num,
  serial_num AS cei_serial_num,
  mei_model AS filled_model,
  mei_serial_num,
  mei_model,
  serial_num_str_match,
  1 AS row_number
FROM (
  SELECT
    c.account_num,
    c.serial_num,
    m.serial_num AS mei_serial_num,
    m.model AS mei_model,
    get_prefix_match_len(c.serial_num, m.serial_num) AS serial_num_str_match,
    -- 匹配度相同的情况下优先取序列号长度更接近的记录，可根据业务需求调整排序规则
    ROW_NUMBER() OVER(
      PARTITION BY c.account_num, c.serial_num 
      ORDER BY get_prefix_match_len(c.serial_num, m.serial_num) DESC, ABS(LENGTH(c.serial_num) - LENGTH(m.serial_num)) ASC
    ) AS seq
  FROM 
    (SELECT * FROM `你的cei表完整路径` WHERE model IS NULL) c
  INNER JOIN 
    (SELECT * FROM `你的mei表完整路径` WHERE model IS NOT NULL) m 
    ON SUBSTR(c.serial_num, 1, 6) = SUBSTR(m.serial_num, 1, 6)
)
WHERE seq = 1

3. 补充说明 #

• 你已经通过前6位前缀关联做了分桶过滤，100万条mei表和5万条cei表的关联量级完全在BigQuery的处理能力范围内，不会出现性能问题
• 如果匹配准确率达不到预期，可以调整前缀关联的长度（比如改成前5位/前7位），或者扩展UDF的匹配规则
• 验证通过后，你可以把该查询的结果作为正式变更申请的附件，提交后再做批量回填

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