如何不使用GROUP BY子句结合PARTITION BY实现按保单号汇总保费

报错原因 #

该报错由SQL执行顺序导致：GROUP BY 的执行优先级高于窗口函数和SELECT子句，原查询中窗口函数里引用的是原始表的Premium字段，该字段既没有出现在GROUP BY分组条件中，也没有被聚合函数包裹，因此触发校验报错。

解决方案 #

有两种常用实现方式，都不需要把Premium加入分组条件：

方案1：嵌套聚合函数（最简单改法） #

直接在窗口函数的聚合参数里套入已计算好的按分组求和的SUM(Premium)即可，代码如下：

declare @Test table (PolicyNumber varchar(50), Coverage varchar(50), Premium money)
insert into @Test 
select 'Pol1' as PolicyNumber, 'Coverage1' as Coverage, 100 as Premium
UNION ALL select 'Pol1', 'Coverage2', 200 
UNION ALL select 'Pol1', 'Coverage2', 25 
UNION ALL select 'Pol1', 'Coverage3', 500 
UNION ALL select 'Pol1', 'Coverage4', 300 
UNION ALL select 'Pol1', 'Coverage4', 25 
UNION ALL select 'Pol1', 'Coverage5', 150 

select 
PolicyNumber,
Coverage,
SUM(Premium) as Premium,
-- 嵌套两层SUM：内层是分组的保费和，外层窗口函数把所有分组的和累加得到整单总保费
PremiumPerPolicy = SUM(SUM(Premium)) OVER (Partition by PolicyNumber)
from @Test
group by PolicyNumber, Coverage

方案2：CTE先算分组结果再计算窗口函数 #

如果觉得嵌套聚合可读性差，可以先用CTE计算每个保单+保障维度的保费和，再在外层计算整单总保费：

WITH GroupedData AS (
    select 
    PolicyNumber,
    Coverage,
    SUM(Premium) as Premium
    from @Test
    group by PolicyNumber, Coverage
)
SELECT 
    PolicyNumber,
    Coverage,
    Premium,
    SUM(Premium) OVER (Partition by PolicyNumber) AS PremiumPerPolicy
FROM GroupedData

两种方案运行后都会得到预期结果：

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