Oracle SQL实现金字塔式匹配查询的高效方案咨询

需要编写Oracle SQL脚本实现交易表与金字塔配置表的匹配查询，要求从配置表获取VAL字段值，必须返回所有交易记录的匹配结果，允许非精确匹配。

匹配优先级规则（从高到低）：

• 优先匹配LITM字段：若配置表的LITM非空，则交易表的LITM必须与配置表一致
• 若配置表LITM为空，则匹配PRODM：配置表PRODM非空时，交易表PRODM需与配置表一致
• 以此类推，优先级顺序为：LITM > PRODM > PRODF > AN8 > MPF > MCU

核心逻辑：找到金字塔表中所有非空高优先级字段都与交易表匹配的记录，选择其中匹配优先级最高（即匹配的高优先级字段数量最多）的记录的VAL值。

示例数据 #

金字塔配置表 #

MCU|MPF|AN8|PRODF|PRODM|LITM|VAL
112| |0| | | |Value006
112|431|0| | | |Value005
112|431|9999| | | |Value004
112|431|9999|VAL001| | |Value003
112|431|9999|VAL001|VAL002| |Value002
112|431|9999|VAL001|VAL002|TEST-MR-001|Value001

交易表 #

DOCO|MCU|MPF|AN8|PRODF|PRODM|LITM|FETCH_RESULT
10001|112|431|9999|VAL001|VAL002|TEST-MR-001
10002|112|431|9999|VAL001|VAL003|TEST-MR-098
10003|112|431|9999|VAL014|VAL055|TEST-MR-005
10004|112|431|9999|VAL012|VAL050|TEST-MR-023
10005|112|345|1293|STK001|STK067|TEST-MR-004

期望输出 #

DOCO|MCU|MPF|AN8|PRODF|PRODM|LITM|FETCH_RESULT
10001|112|431|9999|VAL001|VAL002|TEST-MR-001|Value001
10002|112|431|9999|VAL001|VAL003|TEST-MR-098|Value003
10003|112|431|9999|VAL014|VAL055|TEST-MR-005|Value004
10004|112|431|9999|VAL012|VAL050|TEST-MR-023|Value004
10005|112|345|1293|STK001|STK067|TEST-MR-004|Value006

要求：避免多次JOIN，寻求更高效的实现方式。

使用一次LEFT JOIN + 窗口函数实现，通过权重打分筛选最佳匹配记录，无需多次关联：

WITH PYRAMID_TABLE AS (
    SELECT '112' MCU, '431' MPF, 9999 AN8, 'VAL001' PRODF, 'VAL002' PRODM, 'TEST-MR-001' LITM, 'Value001' VAL FROM DUAL UNION
    SELECT '112' MCU, '431' MPF, 9999 AN8, 'VAL001' PRODF, 'VAL002' PRODM, ' ' LITM, 'Value002' VAL FROM DUAL UNION
    SELECT '112' MCU, '431' MPF, 9999 AN8, 'VAL001' PRODF, ' ' PRODM, ' ' LITM, 'Value003' VAL FROM DUAL UNION
    SELECT '112' MCU, '431' MPF, 9999 AN8, ' ' PRODF, ' ' PRODM, ' ' LITM, 'Value004' VAL FROM DUAL UNION
    SELECT '112' MCU, '431' MPF, 0 AN8, ' ' PRODF, ' ' PRODM, ' ' LITM, 'Value005' VAL FROM DUAL UNION
    SELECT '112' MCU, ' ' MPF, 0 AN8, ' ' PRODF, ' ' PRODM, ' ' LITM, 'Value006' VAL FROM DUAL),
TRANSACTION_TABLE AS (
    SELECT 10001 DOCO, '112' MCU, '431' MPF, 9999 AN8, 'VAL001' PRODF, 'VAL002' PRODM, 'TEST-MR-001' LITM FROM DUAL UNION
    SELECT 10002 DOCO, '112' MCU, '431' MPF, 9999 AN8, 'VAL001' PRODF, 'VAL003' PRODM, 'TEST-MR-098' LITM FROM DUAL UNION
    SELECT 10003 DOCO, '112' MCU, '431' MPF, 9999 AN8, 'VAL014' PRODF, 'VAL055' PRODM, 'TEST-MR-005' LITM FROM DUAL UNION
    SELECT 10004 DOCO, '112' MCU, '431' MPF, 9999 AN8, 'VAL012' PRODF, 'VAL050' PRODM, 'TEST-MR-023' LITM FROM DUAL UNION
    SELECT 10005 DOCO, '112' MCU, '345' MPF, 1293 AN8, 'STK001' PRODF, 'STK067' PRODM, 'TEST-MR-004' LITM FROM DUAL)
SELECT 
    t.DOCO,
    t.MCU,
    t.MPF,
    t.AN8,
    t.PRODF,
    t.PRODM,
    t.LITM,
    p.VAL AS FETCH_RESULT
FROM (
    SELECT 
        t.*,
        p.VAL,
        -- 按优先级给非空字段打分，分值越高匹配优先级越高
        ROW_NUMBER() OVER (
            PARTITION BY t.DOCO 
            ORDER BY 
                CASE WHEN TRIM(p.LITM) <> ' ' THEN 6 ELSE 0 END +
                CASE WHEN TRIM(p.PRODM) <> ' ' THEN 5 ELSE 0 END +
                CASE WHEN TRIM(p.PRODF) <> ' ' THEN 4 ELSE 0 END +
                CASE WHEN p.AN8 IS NOT NULL THEN 3 ELSE 0 END +
                CASE WHEN TRIM(p.MPF) <> ' ' THEN 2 ELSE 0 END +
                CASE WHEN TRIM(p.MCU) <> ' ' THEN 1 ELSE 0 END DESC
        ) AS rn
    FROM TRANSACTION_TABLE t
    LEFT JOIN PYRAMID_TABLE p ON 
        -- 配置表字段非空时必须与交易表匹配，空字段自动满足条件
        (TRIM(p.LITM) = ' ' OR TRIM(p.LITM) = TRIM(t.LITM))
        AND (TRIM(p.PRODM) = ' ' OR TRIM(p.PRODM) = TRIM(t.PRODM))
        AND (TRIM(p.PRODF) = ' ' OR TRIM(p.PRODF) = TRIM(t.PRODF))
        AND (p.AN8 IS NULL OR p.AN8 = t.AN8)
        AND (TRIM(p.MPF) = ' ' OR TRIM(p.MPF) = TRIM(t.MPF))
        AND (TRIM(p.MCU) = ' ' OR TRIM(p.MCU) = TRIM(t.MCU))
) t
WHERE rn = 1;

1. 关联条件：使用LEFT JOIN确保所有交易记录都被返回，每个字段的匹配规则为：配置表字段非空（处理了空格情况）则必须等于交易表对应字段，空字段自动通过匹配校验。
2. 权重打分：通过CASE语句给优先级高的字段赋予更高分值，非空且匹配的高优先级字段越多，总分越高，匹配优先级也就越高。
3. 窗口函数筛选：ROW_NUMBER()按交易记录分组，按权重降序排序，取每组第一条记录（rn=1），即为该交易的最佳匹配结果。

补充说明 #

• 若实际数据中空值为NULL而非空格，将TRIM(p.XXX) = ' '替换为p.XXX IS NULL即可。
• 该方案仅需一次JOIN，性能远优于多次关联，适合大数据量场景。
• 确保金字塔表存在兜底默认记录（如所有字段为空的配置），避免出现无匹配结果的情况。

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