如何在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();

Answer 2

运行此查询(来自jdweng的答案)

Exchanges.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()

在LINQPad中导致

+----------+----------+-----------+
| Currency | Exchange | Frequency |
+----------+----------+-----------+
| USD      | New York |         3 |
| CAD      | Montreal |         2 |
+----------+----------+-----------+

这就是生成的SQL

SELECT [t4].[test], [t1].[Currency], [t4].[Exchange], [t4].[value] AS [Frequency]
FROM (
    SELECT [t0].[Currency]
    FROM [Exchanges] AS [t0]
    GROUP BY [t0].[Currency]
    ) AS [t1]
OUTER APPLY (
    SELECT TOP (1) 1 AS [test], [t3].[Exchange], [t3].[value]
    FROM (
        SELECT COUNT(*) AS [value], [t2].[Exchange]
        FROM [Exchanges] AS [t2]
        WHERE (([t1].[Currency] IS NULL) AND ([t2].[Currency] IS NULL)) OR (([t1].[Currency] IS NOT NULL) AND ([t2].[Currency] IS NOT NULL) AND ([t1].[Currency] = [t2].[Currency]))
        GROUP BY [t2].[Exchange]
        ) AS [t3]
    ORDER BY [t3].[value] DESC
    ) AS [t4]
ORDER BY [t4].[value] DESC