c# - 使用 c# LINQ 计算分组字段的平均值

Question

对于以下数据，有人可以帮助我使用 c#Linq 计算平均值：

Year    city1   city2   value  
2016    CIT01   CIT01   578
2016    CIT01   CIT02   1067
2016    CIT01   CIT03   17
2016    CIT02   CIT01   1105
2016    CIT02   CIT02   233
2016    CIT02   CIT03   0
2016    CIT03   CIT01   1425
2016    CIT03   CIT02   231
2016    CIT03   CIT03   1398
2017    CIT01   CIT01   185
2017    CIT01   CIT02   69
2017    CIT01   CIT03   2225
2017    CIT02   CIT01   0
2017    CIT02   CIT02   104
2017    CIT02   CIT03   258
2017    CIT03   CIT01   10
2017    CIT03   CIT02   342
2017    CIT03   CIT03   643
2018    CIT01   CIT01   59
2018    CIT01   CIT02   163
2018    CIT01   CIT03   284
2018    CIT02   CIT01   19
2018    CIT02   CIT02   379
2018    CIT02   CIT03   462
2018    CIT03   CIT01   46
2018    CIT03   CIT02   0
2018    CIT03   CIT03   154

例如，我想得到平均值：
2016 CIT01 CIT01 578
2017 CIT01 CIT01 185
2018 CIT01 CIT01 59

输出：

Year   city1   city2   value
-----------------------------
Prev    CIT01   CIT01   274
Prev    CIT01   CIT02   433
Prev    CIT01   CIT03   842
Prev    CIT02   CIT01   375
Prev    CIT02   CIT02   239
Prev    CIT02   CIT03   240
Prev    CIT03   CIT01   494
Prev    CIT03   CIT02   191
Prev    CIT03   CIT03   732

标签： c#linqaverage

Answer 1

一种方法是按city1和city2值分组，然后选择一个新项目，其中年份为"Prev"，城市保持不变（与组中一样），值为group.Average(g => g.value)。我们可以使用该Math.Ceiling方法强制四舍五入（如您在示例中所做的那样）。

例如，给定以下类来保存数据：

public class Data
{
    public string Year { get; set; }
    public string City1 { get; set; }
    public string City2 { get; set; }
    public int Value { get; set; }

    public Data(string year, string city1, string city2, int value)
    {
        Year = year;
        City1 = city1;
        City2 = city2;
        Value = value;
    }

    public override string ToString()
    {
        return $"{Year} {City1} {City2} {Value}";
    }
}

以下内容List<Data>用于保存您问题的初始值：

var allData = new List<Data>
{
    new Data("2016", "CIT01", "CIT01", 578),
    new Data("2016", "CIT01", "CIT02", 1067),
    new Data("2016", "CIT01", "CIT03", 17),
    new Data("2016", "CIT02", "CIT01", 1105),
    new Data("2016", "CIT02", "CIT02", 233),
    new Data("2016", "CIT02", "CIT03", 0),
    new Data("2016", "CIT03", "CIT01", 1425),
    new Data("2016", "CIT03", "CIT02", 231),
    new Data("2016", "CIT03", "CIT03", 1398),
    new Data("2017", "CIT01", "CIT01", 185),
    new Data("2017", "CIT01", "CIT02", 69),
    new Data("2017", "CIT01", "CIT03", 2225),
    new Data("2017", "CIT02", "CIT01", 0),
    new Data("2017", "CIT02", "CIT02", 104),
    new Data("2017", "CIT02", "CIT03", 258),
    new Data("2017", "CIT03", "CIT01", 10),
    new Data("2017", "CIT03", "CIT02", 342),
    new Data("2017", "CIT03", "CIT03", 643),
    new Data("2018", "CIT01", "CIT01", 59),
    new Data("2018", "CIT01", "CIT02", 163),
    new Data("2018", "CIT01", "CIT03", 284),
    new Data("2018", "CIT02", "CIT01", 19),
    new Data("2018", "CIT02", "CIT02", 379),
    new Data("2018", "CIT02", "CIT03", 462),
    new Data("2018", "CIT03", "CIT01", 46),
    new Data("2018", "CIT03", "CIT02", 0),
    new Data("2018", "CIT03", "CIT03", 154),
};

我们可以使用以下System.Linq代码对数据进行分组并选择新项目：

var results = allData
    .GroupBy(d => new {d.City1, d.City2})
    .Select(group => new Data("Prev", group.Key.City1, group.Key.City2,
        (int) Math.Ceiling(group.Average(g => g.Value))))
    .OrderBy(d => d.City1)
    .ThenBy(d => d.City2);

foreach (var result in results)
{
    Console.WriteLine(result);
}

输出