BigQuery中高效匹配新SKU与数值最接近的现有SKU的方法

核心思路 #

全量交叉连接会生成百万级×数万级的巨量数据，完全不可行。核心优化方向是缩小每个table_a SKU的匹配范围，再在小范围内筛选最接近的记录，同时依赖索引大幅提升查询速度。

具体实现方案 #

1. 单维度优先匹配（以重量为例） #

如果业务上重量是影响运费的核心因素，可以优先用重量做匹配维度：

• 先给table_b的重量字段建索引，加速范围查询：

CREATE INDEX idx_b_wght ON table_b(item_wght);

• 用范围过滤缩小匹配池，再通过窗口函数选出最接近的记录：

SELECT 
    a.*,
    b.item_ship_est
FROM (
    SELECT 
        table_a.*,
        table_b.item_ship_est,
        ABS(table_a.item_wght - table_b.item_wght) AS weight_diff,
        ROW_NUMBER() OVER (PARTITION BY table_a.sku_id ORDER BY weight_diff) AS rn
    FROM table_a
    -- 只匹配重量在±10%范围内的现有SKU，阈值可根据业务调整
    JOIN table_b 
        ON table_b.item_wght BETWEEN table_a.item_wght * 0.9 AND table_a.item_wght * 1.1
) a
WHERE rn = 1;

2. 多维度综合匹配（重量+尺寸） #

如果需要同时参考长宽高，就用综合差异值筛选：

• 给table_b建复合索引，覆盖所有匹配维度：

CREATE INDEX idx_b_dimensions ON table_b(item_wght, item_len, item_wid, item_hgt);

• 计算加权综合差异，同样先做范围过滤再筛选最接近的记录：

SELECT 
    a.*,
    b.item_ship_est
FROM (
    SELECT 
        table_a.*,
        table_b.item_ship_est,
        -- 给重量更高权重，权重比例可根据业务规则调整
        ABS(table_a.item_wght - table_b.item_wght) * 0.6 +
        ABS(table_a.item_len - table_b.item_len) * 0.15 +
        ABS(table_a.item_wid - table_b.item_wid) * 0.15 +
        ABS(table_a.item_hgt - table_b.item_hgt) * 0.1 AS total_diff,
        ROW_NUMBER() OVER (PARTITION BY table_a.sku_id ORDER BY total_diff) AS rn
    FROM table_a
    -- 多维度范围过滤，进一步压缩匹配池大小
    JOIN table_b 
        ON table_b.item_wght BETWEEN table_a.item_wght * 0.85 AND table_a.item_wght * 1.15
        AND table_b.item_len BETWEEN table_a.item_len * 0.9 AND table_a.item_len * 1.1
        AND table_b.item_wid BETWEEN table_a.item_wid * 0.9 AND table_a.item_wid * 1.1
        AND table_b.item_hgt BETWEEN table_a.item_hgt * 0.9 AND table_a.item_hgt * 1.1
) a
WHERE rn = 1;

3. 极致速度优化：预分组近似匹配 #

如果table_b数据量超大，对匹配精度要求不高的话，可以先给现有SKU按维度分组预计算运费：

• 先生成分组表，按固定区间聚合运费（比如重量每1kg一组，尺寸每5cm一组）：

CREATE TABLE table_b_groups AS
SELECT 
    FLOOR(item_wght) AS wght_group,
    FLOOR(item_len/5)*5 AS len_group,
    FLOOR(item_wid/5)*5 AS wid_group,
    FLOOR(item_hgt/5)*5 AS hgt_group,
    AVG(item_ship_est) AS avg_ship_est,
    -- 也可以取分组内最常用的运费值
    MODE() WITHIN GROUP (ORDER BY item_ship_est) AS mode_ship_est
FROM table_b
GROUP BY wght_group, len_group, wid_group, hgt_group;

-- 给分组表建索引
CREATE INDEX idx_b_groups ON table_b_groups(wght_group, len_group, wid_group, hgt_group);

• 新SKU直接匹配对应分组，快速获取运费：

SELECT 
    a.*,
    g.avg_ship_est AS item_ship_est
FROM table_a a
JOIN table_b_groups g
    ON FLOOR(a.item_wght) = g.wght_group
    AND FLOOR(a.item_len/5)*5 = g.len_group
    AND FLOOR(a.item_wid/5)*5 = g.wid_group
    AND FLOOR(a.item_hgt/5)*5 = g.hgt_group;

关键注意点 #

• 索引是提速核心：所有用于连接、排序的字段必须建索引，否则范围过滤的优势会大打折扣。
• 范围阈值要合理：阈值太小可能出现无匹配的情况，太大则失去优化效果，建议根据业务数据分布测试调整。
• 处理无匹配场景：可以把JOIN改成LEFT JOIN，给无匹配的SKU设置默认运费或者标记状态。

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