問(wèn)題描述

我有一張如下表:

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. 整個(gè)表中所有行的總支出
2. 每個(gè)區(qū)域
的總支出
3. 每個(gè)地區(qū)和國(guó)家組的總支出
4. 每個(gè)地區(qū)、國(guó)家/地區(qū)和廣告客戶(hù)組的總支出

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

所以我在下面寫(xiě)了這個(gè)查詢(xún):

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

但是對(duì)于只有 450K 行的表，該查詢(xún)需要 15 分鐘以上的時(shí)間.我想知道是否有任何方法可以?xún)?yōu)化此性能.預(yù)先感謝您的回答/建議！

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!

推薦答案

你對(duì)問(wèn)題的描述向我暗示了分組集:

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

我不知道這是否會(huì)更快，但它似乎更符合您的問(wèn)題.

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

這篇關(guān)于為多個(gè)層次組優(yōu)化 SUM OVER PARTITION BY的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！