基于CASE WHEN的优先级排序问题（SAS Proc SQL实现）

需求 #

按TABLE_ID分区，为ACCOUNT_ID生成Priority列，规则如下：

• 默认优先级顺序：PREMIUM(1) > PERFORMANCE(2) > VALUE(3) > PLUS(4)
• 若分区内无更高优先级的账户，后续优先级自动递补（例如分区无PREMIUM时，PERFORMANCE优先级变为1，VALUE变为2，以此类推）

源表数据 #

预期结果 #

现有代码问题 #

你当前使用的ROW_NUMBER()窗口函数会为每一行生成唯一序号，若同一分区内存在多个同类型账户，它们的Priority会被分配不同数值；若存在未定义的ACCOUNT_NAME，还可能出现超出预期范围的序号值。代码如下：

SELECT 
    *, 
    ROW_NUMBER() OVER (
        PARTITION BY TABLE_ID
        ORDER BY 
            CASE 
                WHEN ACCOUNT_NAME = 'PREMIUM' THEN 1 
                WHEN ACCOUNT_NAME  = 'PERFORMANCE' THEN 2
                WHEN ACCOUNT_NAME  = 'VALUE' THEN 3
                WHEN ACCOUNT_NAME  = 'PLUS' THEN 4
                ELSE 5
            END
    ) AS Priority
FROM 
   TABLE_A as a;

解决方案 #

方案1：同类型账户优先级相同（简洁版） #

使用DENSE_RANK()替代ROW_NUMBER()，它会为相同优先级的账户分配一致的Priority数值，并自动根据分区内存在的类型递补序号：

PROC SQL;
SELECT 
    *,
    DENSE_RANK() OVER (
        PARTITION BY TABLE_ID
        ORDER BY 
            CASE ACCOUNT_NAME
                WHEN 'PREMIUM' THEN 1
                WHEN 'PERFORMANCE' THEN 2
                WHEN 'VALUE' THEN 3
                WHEN 'PLUS' THEN 4
                ELSE 5
            END
    ) AS Priority
FROM TABLE_A
ORDER BY TABLE_ID, Priority;
QUIT;

方案2：严格递补指定类型（严谨版） #

若需要排除未定义ACCOUNT_NAME的干扰，仅针对指定的4种类型进行递补排序，可通过子查询先确认分区内存在的类型，再生成动态优先级：

PROC SQL;
-- 定义基础优先级映射表
CREATE TABLE REF_PRIORITY AS
SELECT 'PREMIUM' AS ACCOUNT_NAME, 1 AS BASE_PRIORITY FROM UNION ALL
SELECT 'PERFORMANCE' AS ACCOUNT_NAME, 2 AS BASE_PRIORITY FROM UNION ALL
SELECT 'VALUE' AS ACCOUNT_NAME, 3 AS BASE_PRIORITY FROM UNION ALL
SELECT 'PLUS' AS ACCOUNT_NAME, 4 AS BASE_PRIORITY FROM ;

-- 生成最终结果
CREATE TABLE FINAL_RESULT AS
SELECT 
    a.TABLE_ID,
    a.ACCOUNT_ID,
    a.ACCOUNT_NAME,
    t.DYNAMIC_PRIORITY AS Priority
FROM TABLE_A a
LEFT JOIN REF_PRIORITY ref ON a.ACCOUNT_NAME = ref.ACCOUNT_NAME
INNER JOIN (
    SELECT 
        TABLE_ID,
        ACCOUNT_NAME,
        ROW_NUMBER() OVER(PARTITION BY TABLE_ID ORDER BY BASE_PRIORITY) AS DYNAMIC_PRIORITY
    FROM (
        SELECT DISTINCT 
            TABLE_ID,
            ACCOUNT_NAME,
            ref.BASE_PRIORITY
        FROM TABLE_A
        LEFT JOIN REF_PRIORITY ref ON TABLE_A.ACCOUNT_NAME = ref.ACCOUNT_NAME
        WHERE ref.BASE_PRIORITY IS NOT NULL
    ) tmp
) t ON a.TABLE_ID = t.TABLE_ID AND a.ACCOUNT_NAME = t.ACCOUNT_NAME
ORDER BY a.TABLE_ID, Priority;
QUIT;

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