SQL医疗患者数据1:1匹配方案优化咨询

核心思路 #

先收集所有符合1:1匹配规则的候选结果，按匹配优先级（MRN > MBI > ...）排序，再为每个源患者ID选取最高优先级的有效匹配，未找到有效匹配的直接排除。

WITH base AS (
    SELECT
        ID,
        ENTITYID,
        PSNMRN,
        MBI,
        PROGRAM
    FROM tblATB_DataLoad 
    WHERE CREATEDATE > GETDATE() - 1
),
-- 生成所有符合1:1规则的匹配候选，标记优先级
match_candidates AS (
    -- MRN匹配：优先级1（最高）
    SELECT
        b.ID,
        p.PSNNMBR,
        1 AS match_priority
    FROM base b
    JOIN tblMain_Patient_Master p 
        ON b.ENTITYID = p.ENTITYID 
        AND LOWER(b.PSNMRN) = LOWER(p.PSNMRN)
    -- 验证该源ID通过MRN仅匹配到1条主表记录
    WHERE EXISTS (
        SELECT 1
        FROM tblMain_Patient_Master p2
        WHERE b.ENTITYID = p2.ENTITYID 
          AND LOWER(b.PSNMRN) = LOWER(p2.PSNMRN)
        GROUP BY b.ID
        HAVING COUNT(*) = 1
    )
    UNION ALL
    -- MBI匹配：优先级2（仅当MRN无有效匹配时生效）
    SELECT
        b.ID,
        p.PSNNMBR,
        2 AS match_priority
    FROM base b
    JOIN tblMain_Patient_Master p 
        ON b.ENTITYID = p.ENTITYID 
        AND LOWER(b.MBI) = LOWER(p.CLM1ID)
    -- 验证该源ID通过MBI仅匹配到1条主表记录，且未通过MRN匹配到结果
    WHERE EXISTS (
        SELECT 1
        FROM tblMain_Patient_Master p2
        WHERE b.ENTITYID = p2.ENTITYID 
          AND LOWER(b.MBI) = LOWER(p2.CLM1ID)
        GROUP BY b.ID
        HAVING COUNT(*) = 1
    )
    AND NOT EXISTS (
        SELECT 1
        FROM tblMain_Patient_Master p3
        WHERE b.ENTITYID = p3.ENTITYID 
          AND LOWER(b.PSNMRN) = LOWER(p3.PSNMRN)
        GROUP BY b.ID
        HAVING COUNT(*) = 1
    )
),
-- 为每个源ID选取最高优先级的匹配
ranked_matches AS (
    SELECT
        ID,
        PSNNMBR,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY match_priority ASC) AS rn
    FROM match_candidates
)
-- 关联基础数据输出最终结果，仅保留找到唯一匹配的患者
SELECT DISTINCT
    b.ENTITYID,
    rm.PSNNMBR,
    b.PROGRAM
FROM base b
JOIN ranked_matches rm ON b.ID = rm.ID
WHERE rm.rn = 1;

方案优势 #

1. 优先级严格生效：通过match_priority标记规则优先级，高优先级匹配规则优先生效，低优先级规则仅在高优先级无有效匹配时触发，符合需求中的匹配顺序要求。
2. 确保1:1匹配：每个匹配规则都通过EXISTS子句验证源ID在当前规则下仅匹配到1条主表记录，彻底避免一对多、多对多的错误匹配。
3. 扩展性强：后续新增匹配规则（如姓名+出生日期等），只需在match_candidates中新增UNION ALL块，调整优先级数值即可，无需大幅修改整体逻辑。

原代码问题说明 #

原代码中matchMRN和matchMBI的分组逻辑存在错误：

• GROUP BY bas.ID, pat.PSNNMBR会将同一个源ID匹配到的不同主表患者分开统计，导致即使一个源ID匹配到多个主表记录，只要每个主表记录只被匹配一次，就会被错误保留。
• 正确逻辑应为按源ID单独分组，验证该ID在当前匹配规则下的总匹配数为1，而非按源ID+主表患者ID组合分组。

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