BigQuery如何无需传统Join实现跨表变长前缀匹配补全字段

你要实现手机号对应可变长度运营商号段匹配、不想写高复杂度传统Join的需求，直接用下面的可落地方案即可，不需要固定截取手机号字段长度。

方案1：标量子查询实现（通用度最高，适配任意规模数据集） #

不需要写显式JOIN关键字，通过逐行匹配前缀的逻辑实现关联，同时自动优先匹配最长、精准度最高的号段（避免短号段误覆盖长号段的问题），代码如下：

SELECT
  phone,
  (
    SELECT service_name
    FROM `你的项目名.你的数据集名.service_provider` sp
    WHERE STARTS_WITH(p.phone, sp.service_code)
    ORDER BY LENGTH(sp.service_code) DESC
    LIMIT 1
  ) AS service_name
FROM `你的项目名.你的数据集名.phones` p

逻辑说明：

• 用STARTS_WITH(手机号, 号段前缀)函数直接判断前缀匹配关系，完全适配4-10位长度不固定的号段规则
• 子查询内按号段长度倒序排序、取第一条结果，自动命中匹配度最高的号段，不需要额外写分组、窗口函数去重的逻辑
• 整体代码量比传统不等值Join+结果去重的写法少60%以上，逻辑直观不容易写错

方案2：预排序维度表优化（适合大表关联场景） #

如果phones表数据量在千万级以上，可以提前把运营商号段表按长度倒序排序后再参与匹配，减少子查询的遍历开销，进一步提升查询速度，代码如下：

WITH sorted_service_provider AS (
  SELECT service_code, service_name
  FROM `你的项目名.你的数据集名.service_provider`
  ORDER BY LENGTH(service_code) DESC
)
SELECT
  phone,
  (
    SELECT service_name
    FROM sorted_service_provider
    WHERE STARTS_WITH(p.phone, service_code)
    LIMIT 1
  ) AS service_name
FROM `你的项目名.你的数据集名.phones` p

注意：使用前建议校验运营商表是否存在同长度重叠号段，如果两个相同长度的号段同时匹配同一个手机号，上述查询会返回第一个命中的结果，可能出现匹配错误。

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