本文介紹了為多個層次組優化 SUM OVER PARTITION BY的處理方法,對大家解決問題具有一定的參考價值,需要的朋友們下面隨著小編來一起學習吧!
問題描述
限時送ChatGPT賬號..
我有一張如下表:
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:
- 整個表中所有行的總支出
- 每個區域 的總支出
- 每個地區和國家組的總支出
- 每個地區、國家/地區和廣告客戶組的總支出
- Total Spend over all the rows in the whole table
- Total Spend for each Region
- Total Spend for each Region and Country group
- 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模板網!
【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題,如果有圖片或者內容侵犯了您的權益,請聯系我們刪除處理,感謝您的支持!