如何在SQL中应用CMS HCC层级/优先级覆盖逻辑？

问题背景 #

我花了大量时间查找相关实现都没结果，自己解决了问题，现在想找更优雅简洁的优化方案，也希望能帮到其他人！CMS发布的风险调整模型会把疾病分成不同层级，只有疾病的最严重形式才会计入患者的风险调整评分（Risk Adjustment Score）。CMS只提供了这个模型的SAS版本，没有SQL版本。除了下面要应用的层级/优先级覆盖逻辑，模型其他部分都很容易实现。

Members表（存储会员/患者ID及其层级疾病分类（HCC）） #

Hierarchy表（定义需保留最严重HCC的规则） #

逻辑说明 #

若会员同时拥有17、18、19，仅保留17；若仅拥有19则保留19——17是包含18、19的疾病分类中最严重的形式，评分仅需统计17。

预期结果 #

现有实现代码 #

;with members as (
select 123456 as memberID, 17 as hcc
UNION
select 123456 as memberID, 18 as hcc
UNION
select 123456 as memberID, 19 as hcc
UNION
select 2222222 as memberID, 19 as hcc
UNION
select 9999999 as memberID, 18 as hcc
UNION
select 9999999 as memberID, 19 as hcc
)
, Hierarchy as
(
Select 17 as hcc, 18 as dropHCC, 'diabetes1' as hccCategory
UNION
Select 17 as hcc, 19 as dropHCC, 'diabetes1' as hccCategory
UNION
Select 18 as hcc, 19 as dropHCC, 'diabetes2' as hccCategory
)


select m.*--, h2.dropHCC as hccRemovedBy
from members m
left join(
    select m.*, r.drophcc
    from members m
    inner join ( select memberid, m.hcc, h.drophcc
            from members m
            inner join hierarchy h on h.hcc = m.hcc) r on 
        r.memberid = m.memberid 
        and r.dropHCC = m.hcc) h2
        on h2.memberID = m.memberID
        and h2.hcc = m.hcc
where h2.dropHCC is null --remove this criteria in the event you want to see what dropped

优化方案 #

方案1：用NOT EXISTS直接过滤被剔除的HCC #

这个写法更直观，核心逻辑是：找出会员的所有HCC，且该HCC不存在对应的上级HCC（即Hierarchy表中dropHCC等于当前HCC，且该会员同时拥有对应的上级HCC）。代码结构更简洁，避免了多层嵌套JOIN，执行效率更高。

WITH members AS (
    SELECT 123456 AS memberID, 17 AS hcc UNION
    SELECT 123456 AS memberID, 18 AS hcc UNION
    SELECT 123456 AS memberID, 19 AS hcc UNION
    SELECT 2222222 AS memberID, 19 AS hcc UNION
    SELECT 9999999 AS memberID, 18 AS hcc UNION
    SELECT 9999999 AS memberID, 19 AS hcc
),
Hierarchy AS (
    SELECT 17 AS hcc, 18 AS dropHCC, 'diabetes1' AS hccCategory UNION
    SELECT 17 AS hcc, 19 AS dropHCC, 'diabetes1' AS hccCategory UNION
    SELECT 18 AS hcc, 19 AS dropHCC, 'diabetes2' AS hccCategory
)
SELECT m.memberID, m.hcc
FROM members m
WHERE NOT EXISTS (
    SELECT 1
    FROM Hierarchy h
    JOIN members m2 ON m2.memberID = m.memberID AND m2.hcc = h.hcc
    WHERE h.dropHCC = m.hcc
);

方案2：基于优先级标记保留项 #

如果Hierarchy的规则可以转化为HCC的优先级（比如17>18>19），可以先给每个HCC分配优先级，再按会员分组取优先级最高的HCC。这种方式扩展性更强，后续层级规则变化时，只需要维护优先级表即可，无需修改核心代码。

WITH members AS (
    SELECT 123456 AS memberID, 17 AS hcc UNION
    SELECT 123456 AS memberID, 18 AS hcc UNION
    SELECT 123456 AS memberID, 19 AS hcc UNION
    SELECT 2222222 AS memberID, 19 AS hcc UNION
    SELECT 9999999 AS memberID, 18 AS hcc UNION
    SELECT 9999999 AS memberID, 19 AS hcc
),
hcc_priority AS (
    -- 定义HCC优先级：数值越小，代表疾病越严重，优先级越高
    SELECT 17 AS hcc, 1 AS priority UNION
    SELECT 18 AS hcc, 2 AS priority UNION
    SELECT 19 AS hcc, 3 AS priority
)
SELECT memberID, hcc
FROM (
    SELECT 
        m.memberID, 
        m.hcc,
        ROW_NUMBER() OVER (PARTITION BY m.memberID ORDER BY hp.priority ASC) AS rn
    FROM members m
    JOIN hcc_priority hp ON m.hcc = hp.hcc
) t
WHERE rn = 1;

方案对比 #

• 方案1贴合原Hierarchy表规则，无需额外维护优先级，适合规则复杂、无法用单一优先级排序的场景；
• 方案2扩展性更强，规则变化时只需更新优先级表，代码逻辑不用动，适合规则清晰、可量化优先级的场景；
• 两种方案都比原代码更简洁，执行效率更优，尤其是数据量较大时，NOT EXISTS和窗口函数的性能表现优于多层嵌套JOIN。

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