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

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

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

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

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

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

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

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 !