BigQuery SQL实现两表间近似值对最优匹配关联问题咨询

数值对近似匹配一般分为两类常用场景，以下分别给出实现逻辑：

场景1：固定容差匹配（数值偏差在可预设范围内时直接关联） #

假设两张表的数值对字段均为val1、val2，你可以自定义允许的偏差阈值做关联，SQL示例：

SELECT 
  b.val1 AS correct_val1,
  b.val2 AS correct_val2,
  t.order_id
FROM base_table b
INNER JOIN table2 t
  ON ABS(t.val1 - b.val1) < 0.001 -- 0.001为val1允许的最大偏差，可按需调整
  AND ABS(t.val2 - b.val2) < 0.001; -- val2允许的最大偏差，可按需调整

如果是整数类型的数值对，可直接把阈值调整为1或你能接受的最大整数偏差。

场景2：最近邻匹配（每个近似对仅匹配偏差最小的正确数值对） #

如果需要为table2的每一条记录找唯一的最匹配正确数值对，可通过窗口函数实现：

WITH match_candidates AS (
  SELECT 
    b.val1 AS correct_val1,
    b.val2 AS correct_val2,
    t.order_id,
    -- 计算两个数值对的欧氏距离作为匹配度，也可替换为曼哈顿距离等你需要的计算规则
    SQRT(POWER(t.val1 - b.val1, 2) + POWER(t.val2 - b.val2, 2)) AS diff_distance,
    -- 按偏差从小到大给每个order_id的匹配结果排序
    ROW_NUMBER() OVER (PARTITION BY t.order_id ORDER BY SQRT(POWER(t.val1 - b.val1, 2) + POWER(t.val2 - b.val2, 2)) ASC) AS rn
  FROM base_table b
  CROSS JOIN table2 t
)
SELECT correct_val1, correct_val2, order_id
FROM match_candidates
WHERE rn = 1; -- 仅取每个order_id的最匹配正确数值对

注意事项 #

• 若数据量较大，可先在JOIN前加粗过滤条件（比如val1 BETWEEN b.val1 - 10 AND b.val1 + 10），避免全量笛卡尔集计算耗时过长
• 如果数值对是字符串类的近似匹配（比如地址、商品名称），可把匹配逻辑换成对应字符串相似度函数（比如LEVENSHTEIN编辑距离函数）

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