基于多列特定/默认值匹配的raw_data与components成本校验问题

核心思路 #

针对raw_data和components表的关联匹配需求，核心是给每条raw_data记录找到components中优先级最高的匹配项，再对比两者的cost值。匹配优先级：优先匹配name+目标字段（如account_id）完全一致的行；无匹配时，取name一致且account_id=-1的默认行。

具体SQL实现 #

WITH ranked_matches AS (
    SELECT 
        rd.name,
        rd.account_id,
        rd.cost AS raw_cost,
        c.cost AS component_cost,
        -- 按匹配优先级排序：完全匹配排第1，默认行排第2
        ROW_NUMBER() OVER (
            PARTITION BY rd.name, rd.account_id
            ORDER BY 
                CASE WHEN c.account_id = rd.account_id THEN 1 ELSE 2 END
        ) AS match_priority
    FROM raw_data rd
    LEFT JOIN components c 
        ON rd.name = c.name
        -- 限定只保留有效匹配项：完全匹配 或 默认行
        AND (c.account_id = rd.account_id OR c.account_id = -1)
)
SELECT 
    name,
    account_id,
    raw_cost,
    component_cost,
    -- 输出校验结果
    CASE WHEN raw_cost = component_cost THEN '一致' ELSE '不一致' END AS cost_check_status
FROM ranked_matches
WHERE match_priority = 1; -- 仅取优先级最高的匹配项

逻辑说明 #

1. ranked_matches CTE：通过LEFT JOIN关联两张表，用ROW_NUMBER()窗口函数给每个raw_data记录的匹配项排序，完全匹配的行优先级为1，默认行优先级为2。
2. 主查询：筛选出优先级最高的匹配项，直接对比raw_cost和component_cost，输出校验状态。

扩展到多匹配字段 #

如果需要增加其他优先匹配字段（如region），只需调整ORDER BY中的排序逻辑，示例如下：

ORDER BY 
    CASE 
        WHEN c.account_id = rd.account_id AND c.region = rd.region THEN 1
        WHEN c.account_id = rd.account_id THEN 2
        WHEN c.account_id = -1 THEN 3
        ELSE 4 
    END

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