我正在嘗試在單行中實作加權平均資料。這是我當前資料的樣子
| 名稱 | 位置 | ID | 得分1 | 得分2 | 串列 | 姓 |
|---|---|---|---|---|---|---|
| 美國廣播公司 | POS1 | 12345 | 10 | 20 | 2 | 小唯 |
| 美國廣播公司 | POS2 | 12345 | 20 | 10 | 5 | 小唯 |
| 美國廣播公司 | POS3 | 12345 | 20 | 30 | 7 | 小唯 |
| 美國廣播公司 | POS4 | 12345 | 10 | 50 | 2 | 小唯 |
| XYZ | POS1 | 67890 | 5 | 10 | 5 | OPR |
| XYZ | POS2 | 67890 | 30 | 20 | 3 | OPR |
| XYZ | POS3 | 67890 | 40 | 40 | 1 | OPR |
| XYZ | POS4 | 67890 | 20 | 10 | 2 | OPR |
期望輸出應如下所示
| 名稱 | 位置 | ID | 得分1 | 得分2 | 串列 | 姓 |
|---|---|---|---|---|---|---|
| 美國廣播公司 | POS1、POS2、PO3、POS4 | 12345 | 17.5 | 25 | 16 | 小唯 |
| XYZ | POS1、POS2、PO3、POS4 | 67890 | 17.72 | 15.45 | 11 | OPR |
score1 根據 id 計算為 sum(score1 * list/ sum(list))
對于 ABC (ID 12345) 得分 1= 10*(2/16) 20*(5/16) 20*(7/16) 10*(2/16) = 17.5
分數2= 20*(2/16) 10*(5/16) 30*(7/16) 50*(2/16) = 25
串列 = 2 5 7 2 = 16
對于 XYZ(ID 67890) 得分 1= 5*(5/11) 30*(3/11) 40*(1/11) 20*(2/11) = 17.72
分數2= 10*(5/11) 20*(3/11) 40*(1/11) 10*(2/11) = 15.45
串列 = 5 3 1 2 = 11
位置資料是不同位置的字串聚合。位置資料并不總是相同的，一個可能是 4 行，另一個可能是 2 行。
任何幫助都會非常棒，謝謝！
uj5u.com熱心網友回復：
在 SQL Server 2017 或更高版本上：
SELECT
Name,
Position = STRING_AGG(Position, ', '),
Id,
Score1 = CONVERT(DECIMAL(18,2), SUM(Score1 * 1.0 * list) / SUM(list)),
Score2 = CONVERT(DECIMAL(18,2), SUM(Score2 * 1.0 * list) / SUM(list)),
list = SUM(list),
lastName
FROM dbo.Scores
GROUP BY Name, Id, lastName;
輸出：
| 名稱 | 位置 | ID | 分數1 | 分數2 | 串列 | 姓 |
|---|---|---|---|---|---|---|
| 美國廣播公司 | POS1、POS2、POS3、POS4 | 12345 | 17.50 | 25.00 | 16 | 小唯 |
| XYZ | POS1、POS2、POS3、POS4 | 67890 | 17.73 | 15.45 | 11 | OPR |
在舊版本上，結果相同，只是更丑陋且效率更低：
SELECT
Name,
Position = STUFF((SELECT ', ' Position
FROM dbo.Scores
WHERE Id = s.Id
FOR XML PATH(''), TYPE).value(N'./text()[1]',
N'varchar(max)'),1,1,''),
Id,
Score1 = CONVERT(DECIMAL(18,2), SUM(Score1 * 1.0 * list) / SUM(list)),
Score2 = CONVERT(DECIMAL(18,2), SUM(Score2 * 1.0 * list) / SUM(list)),
list = SUM(list),
lastName
FROM dbo.Scores AS s
GROUP BY Name, Id, lastName;
- 示例db<>fiddle
uj5u.com熱心網友回復：
您可以將連接與子查詢一起用于總和串列
select a.name, STRING_AGG(a.Position, ','), a.id, sum(a.score1*a.list)/t.tot_x_list, sum(a.score2*(a.list/t.tot_x_list))
from my_table a
inner join (
select id, sum(list) tot_x_list
from my_table
group by id
) t on t.id = a.id
group by a.id
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/411044.html
標籤：
上一篇：不同但不是真的