基于ranking_1与多维度字段生成expected_ranking_2的SQL实现问题

计算规则 #

基于已有的ranking_1、ct_1、ct_2、st_1、st_2、co_1、co_2字段生成新字段expected_ranking_2，规则如下：

• 满足ct_1 == ct_2 AND st_1 == st_2 AND co_1 == co_2时，初始优先级为1
• 满足(ct_1 == ct_2 AND st_1 == st_2) OR (st_1 == st_2 AND co_1 == co_2) OR (ct_1 == ct_2 AND co_1 == co_2)时，初始优先级为2
• 满足ct_1 == ct_2 OR st_1 == st_2 OR co_1 == co_2时，初始优先级为3
• 其余场景初始优先级直接取ranking_1原值
• 补充规则：若初始优先级重复，需结合ranking_1的值调整，最终expected_ranking_2无重复值

原有代码问题 #

1. 存在语法错误：CASE判断中p.co _1多了空格，会直接执行报错
2. 重复值处理逻辑错误：通过LAG计算优先级差值的方式仅能处理相邻重复的场景，无法覆盖所有重复情况，导致结果不符合预期
3. 逻辑冗余：4层CTE嵌套无必要，执行效率低且难以维护

优化后实现 #

WITH base_calc AS (
    SELECT 
        p.*,
        CASE
            WHEN ct_1 = ct_2 AND st_1 = st_2 AND co_1 = co_2 THEN 1
            WHEN (ct_1 = ct_2 AND st_1 = st_2) 
                 OR (st_1 = st_2 AND co_1 = co_2) 
                 OR (ct_1 = ct_2 AND co_1 = co_2) THEN 2
            WHEN ct_1 = ct_2 OR st_1 = st_2 OR co_1 = co_2 THEN 3
            ELSE ranking_1
        END AS init_priority
    FROM Table_x p
)
SELECT 
    *,
    -- 若需按id分组计算每个分组内的排名，添加PARTITION BY id即可：ROW_NUMBER() OVER(PARTITION BY id ORDER BY init_priority ASC, ranking_1 ASC)
    ROW_NUMBER() OVER(ORDER BY init_priority ASC, ranking_1 ASC) AS expected_ranking_2
FROM base_calc

优化说明 #

• 执行效率提升：仅保留1层计算初始优先级的CTE，去掉了冗余的lag、差值计算逻辑，执行效率大幅提升
• 完全符合规则要求：相同初始优先级的记录会按照原有ranking_1的顺序依次排列，生成的expected_ranking_2无重复值
• 扩展性强：如果业务需要调整为分组排名、允许并列排名，仅需要修改窗口函数的参数即可，修改成本极低

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