为多个层次组优化 SUM OVER PARTITION BY

2021-09-25 00:00:00 sql window-functions sql-server 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] 如下:

I need to find SUM([Spend] over different groups as follow:

  1. 整个表中所有行的总支出
  2. 每个区域
    3. 的总支出
  3. 每个地区和国家组的总支出
  4. 每个地区、国家/地区和广告客户组的总支出
  1. Total Spend over all the rows in the whole table
  2. Total Spend for each Region
  3. Total Spend for each Region and Country group
  4. Total Spend for each Region, Country and Advertiser group

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

So I wrote this query below:

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 分钟以上的时间.我想知道是否有任何方法可以优化此性能.预先感谢您的回答/建议！

But that query takes >15 minutes for a table of just 450K rows. I'd like to know if there is any way to optimize this performance. Thank you in advanced for your answers/suggestions!

推荐答案

你对问题的描述向我暗示了分组集:

Your description of the problem suggests 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])
                        );

我不知道这是否会更快，但它似乎更符合您的问题.

I don't know if this will be faster, but it certainly seems more aligned with your question.

相关文章