针对多个层次结构组优化“ SUM OVER PARTITION BY”

Question

我有一个如下表：

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. 每个 地区的* 总支出 *
3. 每个 地区和国家/地区 组的总支出
4. 每个 地区，国家/地区和广告客户 组的总支出

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

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

但是对于仅450K行的表，该查询要花费超过15分钟的时间。我想知道是否有任何方法可以优化此性能。在此先感谢您的回答/建议！

Answer 1

您对问题的描述grouping sets向我建议：

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])
                        );

我不知道这样会不会更快，但是这似乎与您的问题更加吻合。