筆記-在SQL查詢中計算所佔百分比

最近遇到的小需求,要計算資料表中某一數字欄位所佔百分比,例如:

ItemId GroupId CostAmt
------ ------- --------
X1000  A       50
X1001  A       250
X1002  A       200
X1003  B       200
X1004  B       300

目標是要由以上資料算出X1000佔全部成本5%,X1001佔25%... 等百分比資訊。另外則還要再進一步分組,算出各ItemId佔該組總成本的百分比,例如: X1000佔A群組成本總和的10%,X1001佔50%,X1002佔40%、而X1003佔B群組的40%,X1004佔60%。

發現Oracle有個好用的函數ratio_to_report()可輕鬆取得佔全部及佔各組總和的百分比:

SELECT ItemId, GroupId, CostAmt,
ratio_to_report(CostAmt) over () as Ratio
FROM Cost

SELECT ItemId, GroupId, CostAmt,
ratio_to_report(CostAmt)
over (PARTITION BY GroupId) as Ratio
FROM Cost

原本想在SQL Server找到對應的簡便寫法,但並無所獲。目前想到使用T-SQL產生相同結果的寫法如下:

SELECT ItemId, GroupId, CostAmt, CostAmt/X.CostSum AS Ratio
FROM Cost, (SELECT SUM(CostAmt) As CostSum FROM Cost) X
 
SELECT ItemId, X.GroupId, CostAmt, CostAmt/Y.CostSum AS Ratio
FROM Cost X JOIN (
    SELECT GroupId, SUM(CostAmt) CostSum FROM Cost
    GROUP BY GroupId
) Y ON X.GroupId = Y.GroupId

大家如有其他點子,歡迎提供!

[2012-02-14補充]感謝網友Billy補充,原來在SQL 2005起就有SUM() OVER能做到跟ORACLE ratio_to_report()完全相同的效果,SQL Server沒讓我失望,呵!

SELECT ItemId, GroupId, CostAmt, 
CostAmt/SUM(CostAmt) OVER () AS Ratio
FROM Cost
 
SELECT ItemId, GroupId, CostAmt, 
CostAmt/SUM(CostAmt) OVER (PARTITION BY GroupId) AS Ratio
FROM Cost
歡迎推文分享:
Published 13 February 2012 10:32 PM 由 Jeffrey
Views: 25,378



意見

# NO.18 said on 13 February, 2012 09:51 AM

在下一代的SQL Server 2012有新語法可以操作

msdn.microsoft.com/.../ms189461(v=SQL.110).aspx

www.dotblogs.com.tw/.../59563.aspx

# NO.18 said on 13 February, 2012 09:52 AM

在下一代的SQL Server 2012有新語法可以操作

msdn.microsoft.com/.../ms189461(v=SQL.110).aspx

www.dotblogs.com.tw/.../59563.aspx

# Billy said on 13 February, 2012 10:15 AM

我想到的 SQL Server 解法使用的是windowing function:

http://sqlfiddle.com/#!3/0541d/5

SELECT ItemId, GroupId, CostAmt, CostAmt/Sum(CostAmt) over () AS Ratio

FROM Cost

SELECT ItemId, GroupId, CostAmt, CostAmt/Sum(CostAmt) over (partition by groupID) AS Ratio

FROM Cost

# Jeffrey said on 13 February, 2012 09:04 PM

to Billy, 哇!! 原來SQL早就有對應解決方案,汗顏居然沒找到,謝謝分享~~~

# jain said on 14 February, 2012 09:07 AM

好用,快筆記下來,

謝謝Billy !

你的看法呢?

(必要的) 
(必要的) 
(選擇性的)
(必要的) 
(提醒: 因快取機制,您的留言幾分鐘後才會顯示在網站,請耐心稍候)

5 + 3 =

搜尋

Go

<February 2012>
SunMonTueWedThuFriSat
2930311234
567891011
12131415161718
19202122232425
26272829123
45678910
 
RSS
創用 CC 授權條款
【廣告】
twMVC
最新回應

Tags 分類檢視
關於作者

一個醉心技術又酷愛分享的Coding魔人,十年的IT職場生涯,寫過系統、管過專案, 也帶過團隊,最後還是無怨無悔地選擇了技術鑽研這條路,近年來則以做一個"有為的中年人"自許。

文章典藏
其他功能

這個部落格


Syndication