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

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

Comments

# by NO.18

在下一代的SQL Server 2012有新語法可以操作 http://msdn.microsoft.com/zh-tw/library/ms189461(v=SQL.110).aspx http://www.dotblogs.com.tw/ian/archive/2011/11/25/59563.aspx

# by NO.18

在下一代的SQL Server 2012有新語法可以操作 http://msdn.microsoft.com/zh-tw/library/ms189461(v=SQL.110).aspx http://www.dotblogs.com.tw/ian/archive/2011/11/25/59563.aspx

# by Billy

我想到的 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

# by Jeffrey

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

# by jain

好用,快筆記下來, 謝謝Billy !

Post a comment


96 - 26 =