T-SQL计算各FundType加权绩效遇聚合函数嵌套报错求解决

表结构与数据 #

SQL Server中存在FundAllocation表，结构及数据如下：

需求 #

为每个FundType计算加权绩效（WeightedPerformance），公式为：

WeightedPerformance = SUM( (Allocation / SUM(Allocation Per FundType)) * Performance )

以Credit类型为例：

• 该类型总分配额：0.10+0.20+0.30+0.40+0.50 = 1.50
• 加权绩效计算：((0.1/1.5)*0.15) + ((0.2/1.5)*0.25) + ((0.3/1.5)*0.35) + ((0.4/1.5)*0.45) + ((0.5/1.5)*0.55) = 0.416666666666667

错误的T-SQL语句及报错 #

当前编写的查询语句：

SELECT 
        FundType, 
        Sum((Allocation/Sum(Allocation))*Performance) as WeightedPerformance 
FROM
       FundAllocation
GROUP BY
       FundType

执行时报错：

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

错误原因 #

SQL Server不允许在同一个聚合层级中嵌套使用聚合函数（比如外层SUM里嵌套内层SUM(Allocation)），分组聚合时内层聚合无法直接获取当前分组的总分配额。

解决方案 #

方法1：使用窗口函数计算分组总分配额 #

通过窗口函数SUM(Allocation) OVER (PARTITION BY FundType)先获取每个FundType的总分配额，再计算加权值后聚合：

SELECT 
    FundType,
    SUM((Allocation / TotalAllocation) * Performance) AS WeightedPerformance
FROM (
    SELECT 
        FundType,
        Allocation,
        Performance,
        SUM(Allocation) OVER (PARTITION BY FundType) AS TotalAllocation
    FROM FundAllocation
) AS SubQuery
GROUP BY FundType;

方法2：使用子查询预计算分组总分配额 #

先通过子查询统计每个FundType的总分配额，再关联原表计算加权绩效：

SELECT 
    fa.FundType,
    SUM((fa.Allocation / ft.TotalAllocation) * fa.Performance) AS WeightedPerformance
FROM FundAllocation fa
JOIN (
    SELECT FundType, SUM(Allocation) AS TotalAllocation
    FROM FundAllocation
    GROUP BY FundType
) ft ON fa.FundType = ft.FundType
GROUP BY fa.FundType;

验证结果 #

执行上述任一正确语句，将得到如下结果：

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