SQL Server中PARTITION BY复杂数据分区的优化实现问询

针对你的业务需求，可以通过动态定义分区范围的方式，用单轮计算替代多CTE+UNION的复杂逻辑，提升代码可读性与执行效率：

DECLARE @Dinky TABLE
(
    [Id] INT PRIMARY KEY NOT NULL,
    [Name] nvarchar(32) NOT NULL,
    [Score] float NOT NULL,
    [Type] nvarchar(4) NOT NULL,
    [Pool] int NOT NULL
);

INSERT INTO @Dinky ([Id], [Name], [Score], [Type], [Pool]) VALUES (1, 'Bob',   1.4, 'A', 1);
INSERT INTO @Dinky ([Id], [Name], [Score], [Type], [Pool]) VALUES (2, 'Mary',  2.6, 'A', 2);
INSERT INTO @Dinky ([Id], [Name], [Score], [Type], [Pool]) VALUES (3, 'Kim',   7.8, 'A', 3);
INSERT INTO @Dinky ([Id], [Name], [Score], [Type], [Pool]) VALUES (4, 'Chris', 3.3, 'A', 1);
INSERT INTO @Dinky ([Id], [Name], [Score], [Type], [Pool]) VALUES (5, 'Linda', 4.5, 'A', 1);
INSERT INTO @Dinky ([Id], [Name], [Score], [Type], [Pool]) VALUES (6, 'Frank', 2.1, 'A', 2);
INSERT INTO @Dinky ([Id], [Name], [Score], [Type], [Pool]) VALUES (7, 'Julie', 1.0, 'A', 3);
INSERT INTO @Dinky ([Id], [Name], [Score], [Type], [Pool]) VALUES (8, 'Greg',  6.7, 'A', 3);
INSERT INTO @Dinky ([Id], [Name], [Score], [Type], [Pool]) VALUES (9, 'Tom',   8.3, 'A', 2);

SELECT 
    [Id],
    [Name],
    [Score],
    [Type],
    [Pool],
    PERCENT_RANK() OVER (
        PARTITION BY 
            [Type],
            -- 根据当前行的Pool值，动态指定参与排名的分组范围
            CASE 
                WHEN [Pool] = 1 THEN 'All_Pools'     -- Pool1与所有Pool（1/2/3）共同排名
                WHEN [Pool] = 2 THEN 'Pool2_3'      -- Pool2与Pool3共同排名
                WHEN [Pool] = 3 THEN 'Pool3_Only'   -- Pool3仅内部排名
            END
        ORDER BY [Score] DESC
    ) AS [Rank]
FROM @Dinky;

实现逻辑说明 #

• 核心思路是在PARTITION BY子句中用CASE语句，为不同Pool的行分配对应的分组标识：
  • Pool=1的行统一归到All_Pools分组，所有Pool=1/2/3的行都会进入该分组参与排名
  • Pool=2的行归到Pool2_3分组，仅Pool=2/3的行参与该分组排名
  • Pool=3的行归到Pool3_Only分组，仅同Pool的行参与排名
• 只需一次表扫描和排名计算，就能得到每行对应的正确结果，避免了原方案中多次重复计算、子查询与UNION去重的额外开销

结果验证 #

执行上述代码后，会得到与你原有方案完全一致的排名结果，但代码结构更简洁，执行效率更高。

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