时间戳

首页 > 解决方案 > 如何在没有 GROUP BY 子句的情况下使用 PARTITION BY

sql - 如何在没有 GROUP BY 子句的情况下使用 PARTITION BY

问题描述

我有一个简单的查询，我需要总结每个保单编号的所有保费。它给了我一个错误，因为我需要将它分组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,
-- this gives an error
PremiumPerPolicy = SUM(Premium) OVER (Partition by PolicyNumber)
from @Test
group by PolicyNumber, Coverage

结果应该是这样的

PolicyNumber    Coverage    Premium  PremiumPerPolicy
 Pol1          Coverage1     100       1300
 Pol1          Coverage2     225       1300
 Pol1          Coverage3     500       1300
 Pol1          Coverage4     325       1300
 Pol1          Coverage5     150       1300

标签： sqlsql-servertsqlgroup-bypartition-by

解决方案

您可以正常聚合您的值，然后使用窗口函数再次聚合。

SUM(Premium) OVER (Partition by PolicyNumber)

变成

SUM(SUM(Premium)) OVER (Partition by PolicyNumber)

推荐阅读