优化多个分层组的 SUM OVER PARTITION BY

我有一个如下表：

Region    Country    Manufacturer    Brand    Period    Spend
R1        C1         M1              B1       2016      5
R1        C1         M1              B1       2017      10
R1        C1         M1              B1       2017      20
R1        C1         M1              B2       2016      15
R1        C1         M1              B3       2017      20
R1        C2         M1              B1       2017      5
R1        C2         M2              B4       2017      25
R1        C2         M2              B5       2017      30
R2        C3         M1              B1       2017      35
R2        C3         M2              B4       2017      40
R2        C3         M2              B5       2017      45

我需要找到SUM([Spend]不同组的情况如下：

1. 整个表中所有行的总支出
2. 每人总支出Region
3. 每人总支出地区和国家 group
4. 每人总支出地区、国家和广告商 group

所以我在下面写了这个查询：

SELECT 
    [Period]
    ,[Region]
    ,[Country]
    ,[Manufacturer]
    ,[Brand]
    ,SUM([Spend]) OVER (PARTITION BY [Period]) AS [SumOfSpendWorld]
    ,SUM([Spend]) OVER (PARTITION BY [Period], [Region]) AS [SumOfSpendRegion]
    ,SUM([Spend]) OVER (PARTITION BY [Period], [Region], [Country]) AS [SumOfSpendCountry]
    ,SUM([Spend]) OVER (PARTITION BY [Period], [Region], [Country], [Manufacturer]) AS [SumOfSpendManufacturer]
FROM myTable

但对于只有 45 万行的表，该查询需要 15 分钟以上。我想知道是否有任何方法可以优化此性能。预先感谢您的回答/建议！

您对问题的描述表明grouping sets to me:

SELECT YEAR([Period]) AS [Period], [Region], [Country], [Manufacturer], 
       SUM([Spend])
GROUP BY GROUPING SETS ( (YEAR([Period]),
                         (YEAR([Period]), [Region]),
                         (YEAR([Period]), [Region], [Country]), 
                         (YEAR([Period]), [Region], [Country], [Manufacturer])
                        );

我不知道这是否会更快，但它肯定看起来更符合你的问题。