問題描述
我正在嘗試計算表格中某些數據的加權平均成熟度.
I'm trying to calculate the weighted average maturity of some data in my table.
SaleEventID LID(PK) CurrentUPB Interest Rate RemainingMonths
1 1 $100,000.00 6.100% 11.00
1 2 $67,000.00 6.200% 360.00
1 3 $1,400,000.00 6.300% 240.00
1 4 $500,000.00 7.000% 100.00
2 5 $1,400,000.00 7.100% 240.00
2 6 $500,000.00 7.000% 100.00
所以我想要完成的公式是 (WAM):
1) 乘以 CurrentUPB * RemainingMonths for LID=1
2) 對匹配 WHERE SaleEventID=1
的每一行執行此操作3) 對以上計算求和 = $411,220,000.00 = A
4) SUM 所有 CurrentUPB WHERE SaleEventID=1 等于 $2,067,000.00 =B
5) 然后除以 A/B = $198.95 這是我的 WAM
So the formula i'm trying to accomplish is (WAM):
1) Multiply CurrentUPB * RemainingMonths for LID=1
2) Do that for each row that matches WHERE SaleEventID=1
3) SUM the above calculation = $411,220,000.00 = A
4) SUM all the CurrentUPB WHERE SaleEventID=1 which equals $2,067,000.00 =B
5) Then Divide A/B = $198.95 which is my WAM
我需要考慮的是，在我的表中，我將有許多貸款，并且每個貸款都不會被賦予相同的 SaleEventID 值(不是主鍵)
I need to consider that in my table I will have many Loans and that each will not be attributed the same SaleEventID value (Which is not the Primary Key)
到目前為止我的查詢:
SELECT l.*, A / B FROM AS WAM
FROM ( SELECT LSX_DC_Loans l
(SELECT CurrentUPB * RemainingMonths FROM l WHERE LID = 1
) AS A
(SELECT SUM (CurrentUPB) CurrentUPB FROM LSX_DC_Loans
WHERE SaleEventID = 1
) AS B
FROM l
) l
我無法弄清楚如何執行第 2 步和;4. 任何幫助，示例高度贊賞.
I'm having trouble figuring out how to do steps 2 & 4. Any help, examples highly appreciated.
推薦答案
計算 WAM 和 WAIR 非常簡單.想想 Excel 中的 sumproduct()
To calculate WAM and WAIR is pretty simple. Think sumproduct() in Excel
Declare @YourTable table (SaleEventID int,LID int,CurrentUPB money,[Interest Rate] money,RemainingMonths money)
Insert Into @YourTable values
(1,1,100000.00,6.100, 11.00),
(1,2,67000.00,6.200, 360.00),
(1,3,1400000.00,6.300, 240.00),
(1,4,500000.00,7.000, 100.00),
(2,5,1400000.00,7.100, 240.00),
(2,6,500000.00,7.000,100.00)
Select SaleEventID
,UPB = sum(CurrentUPB)
,WAM = sum(CurrentUPB*RemainingMonths)/sum(CurrentUPB)
,WAIR = sum(CurrentUPB*[Interest Rate] )/sum(CurrentUPB)
From @YourTable
Where SaleEventID = @Event
Group By SaleEventID
退貨
SaleEventID UPB WAM WAIR
1 2067000.00 198.9453 6.4564
2 1900000.00 203.1578 7.0736
這篇關于SQL Server 2012:加權平均計算的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！