如何基于A1/A2/A3分组生成R1ID与组内行号R2ID？

现有如下测试表结构及数据：

CREATE TABLE TEST
(UID int, A1 VARCHAR(10), A2 VARCHAR(10), A3 VARCHAR(10), A4 VARCHAR(10));

INSERT INTO TEST VALUES
(1, 'A1' , 'B1', 'C1', NULL),
(2, 'A1' , 'B1', 'C1', 'D1'),
(3, 'A2' , 'B2', 'C2', 'D1'),
(4, 'A1' , 'B1', 'C1', 'D2'),
(5, 'A2' , 'B2', 'C2', 'D4'),
(6, 'A2' , 'B2', 'C2', 'D6'),
(7, NULL, NULL, 'C3' , NULL),
(8,  NULL, NULL, 'C3' , 'd7');

需求是生成两个ID列：

• R1ID：基于A1、A2、A3的组合分配唯一分组ID，同一组合的行拥有相同R1ID
• R2ID：每个分组内的行号，按A4排序（NULL优先）

已实现R2ID的生成，需要同时完成R1ID的生成，解决方案如下：

SELECT 
    UID, A1, A2, A3, A4,
    DENSE_RANK() OVER (ORDER BY A1, A2, A3) AS R1ID,
    ROW_NUMBER() OVER (PARTITION BY A1, A2, A3 ORDER BY A4) AS R2ID
FROM TEST
ORDER BY R1ID, R2ID;

关键说明： #

• DENSE_RANK() OVER (ORDER BY A1, A2, A3)：对A1、A2、A3的每个唯一组合进行连续排名，生成无跳号的分组ID（R1ID），NULL值会被视为同一分组
• ROW_NUMBER() OVER (PARTITION BY A1, A2, A3 ORDER BY A4)：在每个分组内按A4排序生成行号（R2ID），NULL默认排在分组最前，符合预期
• 末尾的ORDER BY R1ID, R2ID确保结果按分组顺序+组内行号排序，与预期输出完全匹配

内容的提问来源于stack exchange，提问作者Abhiram Reddy Kotu