c# - 如何在 Linq C# 中使用分区？

Question

我有以下 SQL 表，我需要为每种货币选择一个顶级交易所

Currency  Exchange Date
USD       NewYork  01/12/20
USD       NewYork  01/11/20
USD       NewYork  01/10/20
USD       Montreal 01/10/20
CAD       Montreal 01/07/20
CAD       Montreal 01/06/20
CAD       Beijing  01/06/20

我正在使用这个使用分区的查询

select distinct currency,
       first_value(exchange) over (partition by currency order by count(*) desc) exchange,
       max(count(*)) over (partition by currency) frequency
from tablename
group by currency, exchange

结果

Currency Exchange Frequency 
USD      NewYork  3
CAD      Montreal 2 

我需要在 Linq 中实现它

Answer 1

尝试以下操作:

DataTable dt = new DataTable();
dt.Columns.Add("Currency", typeof(string));
dt.Columns.Add("Exchange", typeof(string));
dt.Columns.Add("Date", typeof(DateTime));

dt.Rows.Add(new object[] {"USD", "NewYork", DateTime.Parse("01/12/20")});
dt.Rows.Add(new object[] {"USD", "NewYork", DateTime.Parse("01/11/20")});
dt.Rows.Add(new object[] {"USD", "NewYork", DateTime.Parse("01/10/20")});
dt.Rows.Add(new object[] {"USD", "Montreal", DateTime.Parse("01/10/20")});
dt.Rows.Add(new object[] {"CAD", "Montreal", DateTime.Parse("01/07/20")});
dt.Rows.Add(new object[] {"CAD", "Montreal", DateTime.Parse("01/06/20")});
dt.Rows.Add(new object[] { "CAD", "Beijing", DateTime.Parse("01/06/20") });

var results = dt.AsEnumerable()
    .GroupBy(x => x.Field<string>("Currency"))
    .Select(x =>
        x.GroupBy(y => y.Field<string>("Exchange"))
            .Select(z =>
                new
                {
                    Currency = x.Key,
                    Exchange = z.Key,
                    Frequency = z.Count()
                })
            .OrderByDescending(z => z.Frequency)
            .First()
    ).ToList();