問題描述

我有一張如下表:

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. 每個地區和國家組的總支出
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.

這篇關于為多個層次組優化 SUM OVER PARTITION BY的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！