筆記-在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