使用PowerShell从CSV生成分组平均值的代码优化及实现问题

数据与需求 #

给定以下CSV数据（Dates列仅作参考，无实际作用）：

Name,Dates,Sortino,RoMDD,PctWins
AAL,2017-06-01 - 2018-06-01,-0.47,-0.26,66.67%
AAL,2017-09-01 - 2018-06-01,0.03,0.02,66.67%
AAL,2017-12-01 - 2018-06-01,-1.05,-0.69,50.00%
AAL,2018-03-01 - 2018-06-01,-2.99,-2.32,33.33%
AAPL,2017-06-01 - 2018-06-01,2.01,1.79,66.67%
AAPL,2017-09-01 - 2018-06-01,1.89,1.68,66.67%
AAPL,2017-12-01 - 2018-06-01,1.66,1.64,66.67%
AAPL,2018-03-01 - 2018-06-01,3.1,3.86,66.67%
ADBE,2017-06-01 - 2018-06-01,3.88,6.09,75.00%
ADBE,2017-09-01 - 2018-06-01,4.16,7.03,77.78%
ADBE,2017-12-01 - 2018-06-01,3.92,8.34,83.33%
ADBE,2018-03-01 - 2018-06-01,4.58,10.29,100.00%

核心需求：

• 按Name字段分组，计算Sortino、RoMDD、PctWins的平均值，效果等同于Excel数据透视表的行分组+值平均值设置
• 最终将结果写入包含平均值列的CSV文件
• 后续需逐步过滤：先取Sortino排名前20的条目，再在该结果中取RoMDD排名前20的条目，最后取PctWins排名前20的条目（此部分另行处理）

现有代码问题 #

你编写的代码存在重复输出问题：每次调用Add-Member并使用-PassThru会输出一次对象，导致最终结果中每个Name对应多条重复记录，而非单条包含所有平均值的记录。代码如下：

$csv = import-csv -path C:\Temp\Example.CSV | Select-Object -Property Name,Sortino,RoMDD,PctWins | ForEach {$_.PctWins=$_.PctWins.Replace("%","").ToDouble([cultureinfo]::InvariantCulture);$_}
$collection = $csv | group-object -property Name
$avg = $collection | Foreach {
    $avg = $_.group|measure -Property Sortino -Average | select -expand average
    add-member -inputobject $_.group[0] -notepropertyname AvgSortino -notepropertyvalue $avg -PassThru
    $avg = $_.group|measure -Property RoMDD -Average | select -expand average
    add-member -inputobject $_.group[0] -notepropertyname AvgRoMDD -notepropertyvalue $avg -PassThru
    $avg = $_.group|measure -Property PctWins -Average | select -expand average
    add-member -inputobject $_.group[0] -notepropertyname AvgPctWins -notepropertyvalue $avg -PassThru
} | Select Name,AvgSortino,AvgRoMDD,AvgPctWins

优化方案：使用PSCustomObject #

使用PSCustomObject是更简洁高效的方案，可直接为每个分组构造包含所有平均值的单一对象，彻底解决重复输出问题：

# 导入并预处理CSV：将PctWins转换为数值类型
$csv = Import-Csv -Path C:\Temp\Example.CSV | 
    Select-Object Name, Sortino, RoMDD, PctWins |
    ForEach-Object {
        # 移除百分号并转换为双精度数值
        $_.PctWins = $_.PctWins.Replace("%", "") -as [double]
        $_
    }

# 按Name分组，计算各字段平均值并构造新对象
$groupedAverages = $csv | Group-Object -Property Name |
    ForEach-Object {
        $group = $_.Group
        [PSCustomObject]@{
            Name         = $_.Name
            AvgSortino   = ($group | Measure-Object -Property Sortino -Average).Average
            AvgRoMDD     = ($group | Measure-Object -Property RoMDD -Average).Average
            AvgPctWins   = ($group | Measure-Object -Property PctWins -Average).Average
        }
    }

# 将结果写入新CSV文件
$groupedAverages | Export-Csv -Path C:\Temp\GroupedAverages.csv -NoTypeInformation

代码说明 #

1. 预处理阶段：导入CSV后，移除PctWins字段的百分号并转换为数值类型，确保平均值计算的准确性。
2. 分组计算：通过Group-Object按Name分组后，对每个分组的三个字段分别用Measure-Object计算平均值，直接嵌入到PSCustomObject的属性中，每个分组仅生成一个对象。
3. 导出结果：使用Export-Csv将最终的平均值结果写入新文件，-NoTypeInformation参数避免添加PowerShell类型信息头。

备选方案：SQL风格查询（适合熟悉SQL的场景） #

如果你熟悉SQL，可以将CSV转换为DataTable后，用SQL语句实现分组计算，示例如下：

# 导入CSV并转换为DataTable
$csv = Import-Csv -Path C:\Temp\Example.CSV | Select-Object Name, Sortino, RoMDD, PctWins
$dt = New-Object System.Data.DataTable
$csv[0].PSObject.Properties.Name | ForEach-Object { $dt.Columns.Add($_) }
$csv | ForEach-Object { $dt.Rows.Add($_.PSObject.Properties.Value) }

# 转换PctWins为数值类型
$dt.Columns["PctWins"].DataType = [double]
foreach ($row in $dt.Rows) {
    $row["PctWins"] = $row["PctWins"].Replace("%", "") -as [double]
}

# 创建内存数据库并导入数据
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;Integrated Security=True;AttachDbFileName=C:\Temp\TempDB.mdf")
$conn.Open()
$dt.AcceptChanges()
$bulkCopy = New-Object System.Data.SqlClient.SqlBulkCopy($conn)
$bulkCopy.DestinationTableName = "StockData"
$bulkCopy.WriteToServer($dt)

# 执行分组查询
$query = @"
SELECT Name, 
       AVG(CAST(Sortino AS float)) AS AvgSortino,
       AVG(CAST(RoMDD AS float)) AS AvgRoMDD,
       AVG(PctWins) AS AvgPctWins
FROM StockData
GROUP BY Name
"@
$result = Invoke-SqlCmd -Connection $conn -Query $query

# 关闭连接并导出结果
$conn.Close()
$result | Export-Csv -Path C:\Temp\SQLGroupedAverages.csv -NoTypeInformation

不过该方案依赖LocalDB，步骤繁琐，对于简单的分组计算，PSCustomObject的方式更轻量高效。

内容的提问来源于stack exchange，提问作者Scott