BigQuery中双列多条件left join匹配订单佣金比例方案问询

实现思路 #

针对大数据量场景下避免交叉连接的性能问题，我们可以通过优先级排序+取Top1的方案实现匹配，仅需要一次关联加窗口函数即可完成，时间复杂度可控：

• 第一步：按vendor_code关联订单表与合同表，仅保留合同生效日期早等于订单日期的记录，先过滤掉大量无效候选合同
• 第二步：为每个订单的候选合同设置匹配优先级：
  • 最高优先级：满足order_type = contract_type且订单日期落在合同有效期内，命中该规则直接优先选用
  • 次优先级：剩余候选合同优先选类型为OD的，同类型下按合同生效日期倒序，取最新的一份
• 第三步：每个订单仅保留优先级最高的一条合同记录即可

完整SQL代码 #

with contracts as (
    select date('2021-01-01') as contract_start_date, date('2021-02-28') as contract_end_date, 'OD' as contract_type, 20 as commission_percentage, 1 as vendor_code
    union all
    select '2021-01-01', '2021-02-28', 'VD', 25, 1
    union all
    select '2021-03-01', '2021-04-30', 'OD', 30, 1
    union all 
    select '2021-06-01', current_date(), 'OD', 35, 1
),
orders as (
    select 1 as code, 'OD' as order_type, date('2021-02-01') as order_date, 1 as vendor_code
    union all 
    select 2, 'OD', '2021-05-03', 1
    union all
    select 3, 'VD', '2021-02-04', 1
    union all
    select 4, 'VD', '2021-07-01', 1
),
ranked_contracts AS (
    SELECT 
        o.*,
        c.contract_type,
        c.commission_percentage,
        -- 按匹配规则设置排序优先级，rn=1即为最优匹配
        ROW_NUMBER() OVER (
            PARTITION BY o.code 
            ORDER BY 
                -- 第一优先级：完全匹配类型+日期范围
                CASE WHEN o.order_type = c.contract_type AND o.order_date <= c.contract_end_date THEN 1 ELSE 2 END ASC,
                -- 第二优先级：优先选OD类型
                CASE WHEN c.contract_type = 'OD' THEN 1 ELSE 2 END ASC,
                -- 第三优先级：合同生效日期越新越好
                c.contract_start_date DESC
        ) AS rn
    FROM orders o
    -- 仅关联同供应商、且合同生效日早等于订单日的候选合同，避免无效关联
    LEFT JOIN contracts c 
        ON o.vendor_code = c.vendor_code 
        AND c.contract_start_date <= o.order_date
)
SELECT 
    code, order_type, order_date, vendor_code, contract_type, commission_percentage
FROM ranked_contracts 
WHERE rn = 1;

输出验证 #

运行上述代码可得到和期望完全一致的结果：

性能优化建议 #

针对百万级订单场景，可通过以下方式进一步提升执行效率：

• 给合同表的vendor_code、contract_start_date字段加联合索引
• 如果订单数据量极大，可按订单日期分区后分批执行

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