时间戳

首页 > 解决方案 > BigQuery：计算两个字段之间的连续字符串匹配

google-bigquery - BigQuery：计算两个字段之间的连续字符串匹配

问题描述

我有两张桌子：

  1. Master_Equipment_Index（别名mei）包含列 serial_num 和 model_num
  2. 包含 account_num、serial_num 和 model_num 列的客户设备索引（别名cei ）

最初，当插入新的 serial_num 记录时，护栏没有实现要求在mei数据中输入模型属性。每当该serial_num 稍后与cei数据中的客户帐户相关联时，模型数据都会作为空值继续。

我想要做的是根据mei数据中其他类似serial_nums的最强顺序字符匹配，从mei数据中回填cei数据中缺失的模型属性。

为了进一步澄清，我无权大规模更新mei或cei数据集。我可以正式提出变更请求，但我需要构建功能以证明其价值。因此，这必须在任何大规模行动查询更新之外完成。

cei.account_num cei.serial_num cei.model mei.serial_num mei.model serial_num_str_match 行号
123123123 B4I4SXT1 708 无效的 B4I4SXT1 78A 型号_Series1 8 1
123123123 B4I4SXT 1708 无效的 B4I4SXT AS34 型号_Series2 7 2

在上面的表格示例中，row_number 1 的连续字符串匹配计数高于 row_number 2。我只想返回 row_number 1 并使用mei .model 的值填充cei .model。

cei.account_num cei.serial_num cei.model mei.serial_num mei.model serial_num_str_match 行号
123123123 B4I4SXT1 708 型号_Series1 B4I4SXT1 78A 型号_Series1 8 1

给出一个规模的概念：
mei数据包含 100 万条记录，cei数据包含 50,000 条记录。我必须为每一个cei .account_num、cei .serial_num 执行这个字符串匹配，其中cei .model 数据为空。

使用 mac 地址，前 6 个字符标识供应商，我可以在下面的示例 SQL 中查看类似的内容，以帮助减少事务量 1：发生许多查找：

/* need to define function */
create temp function string_match_function(x any type, y any type) as (
  syntax to generate consecutive string count matches between x and y
);

select * from (
select
  c.account_num,
  c.serial_num,
  m.model,
  row_number() over(partition by c.account_num, c.serial_num order by serial_num_str_match desc) seq
from (
     select 
       c.account_num, 
       c.serial_num, 
       m.model,

       needed: string_match_function(c.serial_num, m.serial_num) as serial_num_str_match

     from (
          select * from cei where model is null
          ) c
     join (
          select * from mei where model is not null
          ) m on substr(c.serial_num,1,6) = substr(m.serial_num,1,6)
) as a
) as b
where seq = 1

我查看了不同的选项，其中一些来自https://hoffa.medium.com/new-in-bigquery-persistent-udfs-c9ea4100fd83，但我没有找到我需要的。

任何见解或方向将不胜感激。

标签： google-bigquerybigquery-udf

解决方案

此 UDF 函数从开头计算每个字符串中的相等字符：

CREATE TEMP FUNCTION string_match_function(x string, y string)
RETURNS int64
LANGUAGE js
AS r"""
  var i=0;
  var max_len= Math.min(x.length,y.length);
    for(i=0;i<max_len;i++){
        if(x[i]!=y[i]) {return i;}
    }
  return i;
""";

select string_match_function("12a345","1234")

给出 2，因为两者都以12

推荐阅读