按ID与Category分组并按分类执行不同计算的SQL优化咨询

数据表结构与测试数据 #

CREATE TABLE Test(
ID int,
Category NVARCHAR(MAX),
Value1 float,
Value2 float
)
GO

INSERT INTO Test
VALUES(1,'Category 1',1,4)
,(1,'Category 1',2,3)
,(1,'Category 2',3,5)
,(1,'Category 3',4,2)
,(2,'Category 2',5,1)
,(2,'Category 2',6,3)
,(2,'Category 2',7,6)
,(2,'Category 3',8,3)
,(2,'Category 1',1,2)
,(3,'Category 1',2,4)
,(3,'Category 1',3,5)
,(3,'Category 1',4,7)
,(3,'Category 2',5,8)
,(3,'Category 3',6,9)

需求说明 #

• 按ID和Category分组，先对Value1、Value2做分组求和
• 根据Category的不同执行差异化计算：
  • 当Category = 'Category 1'时：(Category1的Value1总和 + Category2的Value1总和) - Category3的Value1总和，Value2逻辑同理
  • 当Category = 'Category 2'时：(Category1的Value1总和 - Category2的Value1总和) * Category3的Value1总和，Value2逻辑同理
  • 当Category = 'Category 3'时：直接取当前分类的Value1/Value2总和
• 最终结果需保留ID和Category字段

现有问题 #

之前尝试的GROUP BY语句因分组限制，无法在当前分类组内获取同ID下其他分类的汇总值，导致计算逻辑失效：

SELECT
  ID,
  Category,
  CASE
    WHEN Category = 'Category 1' THEN (SUM(CASE WHEN Category = 'Category 1' THEN Value1 ELSE 0 END) + SUM(CASE WHEN Category = 'Category 2' THEN Value1 ELSE 0 END)) - SUM(CASE WHEN Category = 'Category 3' THEN Value1 ELSE 0 END)
    WHEN Category = 'Category 2' THEN (SUM(CASE WHEN Category = 'Category 1' THEN Value1 ELSE 0 END) - SUM(CASE WHEN Category = 'Category 2' THEN Value1 ELSE 0 END)) * SUM(CASE WHEN Category = 'Category 3' THEN Value1 ELSE 0 END)
    WHEN Category = 'Category 3' THEN SUM(CASE WHEN Category = 'Category 3' THEN Value1 ELSE 0 END)
    ELSE 0
  END AS Value1,
    CASE
    WHEN Category = 'Category 1' THEN (SUM(CASE WHEN Category = 'Category 1' THEN Value2 ELSE 0 END) + SUM(CASE WHEN Category = 'Category 2' THEN Value2 ELSE 0 END)) - SUM(CASE WHEN Category = 'Category 3' THEN Value2 ELSE 0 END)
    WHEN Category = 'Category 2' THEN (SUM(CASE WHEN Category = 'Category 1' THEN Value2 ELSE 0 END) - SUM(CASE WHEN Category = 'Category 2' THEN Value2 ELSE 0 END)) * SUM(CASE WHEN Category = 'Category 3' THEN Value2 ELSE 0 END)
    WHEN Category = 'Category 3' THEN SUM(CASE WHEN Category = 'Category 3' THEN Value2 ELSE 0 END)
    ELSE 0
  END AS Value2
FROM dbo.Test
GROUP BY ID, Category
ORDER BY ID, Category

采用UNION ALL多次查询主表的方式虽能实现需求，但主表数据量达50万+，该方案执行效率极低。

高效解决方案 #

通过窗口函数在同ID范围内预计算各分类的汇总值，只需扫描一次表即可完成所有计算，性能大幅提升：

WITH CategorySums AS (
    SELECT
        ID,
        Category,
        -- 当前分类的Value1/Value2总和
        SUM(Value1) OVER (PARTITION BY ID, Category) AS CurrentCatValue1Sum,
        SUM(Value2) OVER (PARTITION BY ID, Category) AS CurrentCatValue2Sum,
        -- 同ID下各分类的Value1总和
        SUM(CASE WHEN Category = 'Category 1' THEN Value1 ELSE 0 END) OVER (PARTITION BY ID) AS Cat1Value1Sum,
        SUM(CASE WHEN Category = 'Category 2' THEN Value1 ELSE 0 END) OVER (PARTITION BY ID) AS Cat2Value1Sum,
        SUM(CASE WHEN Category = 'Category 3' THEN Value1 ELSE 0 END) OVER (PARTITION BY ID) AS Cat3Value1Sum,
        -- 同ID下各分类的Value2总和
        SUM(CASE WHEN Category = 'Category 1' THEN Value2 ELSE 0 END) OVER (PARTITION BY ID) AS Cat1Value2Sum,
        SUM(CASE WHEN Category = 'Category 2' THEN Value2 ELSE 0 END) OVER (PARTITION BY ID) AS Cat2Value2Sum,
        SUM(CASE WHEN Category = 'Category 3' THEN Value2 ELSE 0 END) OVER (PARTITION BY ID) AS Cat3Value2Sum
    FROM dbo.Test
)
SELECT DISTINCT
    ID,
    Category,
    CASE
        WHEN Category = 'Category 1' THEN (Cat1Value1Sum + Cat2Value1Sum) - Cat3Value1Sum
        WHEN Category = 'Category 2' THEN (Cat1Value1Sum - Cat2Value1Sum) * Cat3Value1Sum
        WHEN Category = 'Category 3' THEN CurrentCatValue1Sum
        ELSE 0
    END AS Value1,
    CASE
        WHEN Category = 'Category 1' THEN (Cat1Value2Sum + Cat2Value2Sum) - Cat3Value2Sum
        WHEN Category = 'Category 2' THEN (Cat1Value2Sum - Cat2Value2Sum) * Cat3Value2Sum
        WHEN Category = 'Category 3' THEN CurrentCatValue2Sum
        ELSE 0
    END AS Value2
FROM CategorySums
ORDER BY ID, Category;

方案说明 #

1. CTE预计算阶段：利用窗口函数PARTITION BY ID一次性算出每个ID下所有分类的Value1/Value2汇总值，同时计算当前分类的自身总和，仅需扫描一次主表。
2. 最终查询阶段：基于预计算好的汇总值，根据当前Category执行对应的差异化计算，用DISTINCT去重得到按ID+Category分组的结果。

该方案既解决了原GROUP BY无法跨分类取数的问题，又避免了多次扫描大表，在大数量级场景下性能远优于UNION ALL方案。

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