c# Linq Rank like TSQL Partition BY Multiple Columns

Question

我有一个List<T>，并试图使用Linq进行排名，就像我在TSQL中一样。

TSQL：RANK() OVER ( PARTITION BY [Time], [Filter] ORDER BY [Speed] Desc) AS speedrank

计算排名后，尝试使用计算出的排名更新List<T>中的排名列。

我可以将数据写入 sql 表并使用 tsql 进行查询，但如果可能的话，我更愿意使用我在应用程序中的强类型列表。

排名必须考虑基于分区的重复速度值，因此排序和递增行号无法按预期工作。

已更新：主窗体中的列表.cs

private List<Ranking> _rankingList = new List<Ranking>();

添加到列表。

var advancedsearchranking = new Ranking
{
Course = course,
RaceDate = racedate,
RaceTime = racetime,
RaceDayRaceNo = racenumber,
RaceDayHorseNo = horse.HNo,
Filter = "Going",
Horse = horse.HorseName,
WinPercentage = $"{winpercentage} ({wins}/{runs})",
Positions = sbpos.ToString(),
SpeedFigures = sbspeedratings.ToString(),
LastSpeedFigure = lastspeedfigure,
Average = Math.Round((double)average, 0),
BSPs = sbbsp.ToString()
};
_rankingList.Add(advancedsearchranking);

类：

public class Ranking
{
public string Course { get; set; }
public DateTime RaceDate { get; set; }
public TimeSpan RaceTime { get; set; }
public int? RaceDayRaceNo { get; set; }
public int? RaceDayHorseNo { get; set; }
public string Filter { get; set; }
public string Horse { get; set; }
public string WinPercentage { get; set; }
public string Positions { get; set; }
public string SpeedFigures { get; set; }
public int? LastSpeedFigure { get; set; }
public int LastSpeedRank { get; set; }
public double? Average { get; set; }
public virtual string BSPs { get; set; }
public virtual double[] BSPSparkLine { get; set; }
public double? MasterAverage { get; set; }
}

我尝试按过滤器属性进行分区并按 LastSpeedFigure Desc 排名，因此如果两个具有相同的值，则最高数字排名 1 或并列第 1。

问候

尼尔

Answer 1

您应该能够使用以下代码(抱歉，我还没有测试过任何代码)。

var list = _rankingList.OrderBy(r => r.Filter).ThenByDescending(r => r.LastSpeedFigure);
int rank = 1;
int rowNumber = 1;
list[0].LastSpeedRank = 1;  // set the first item
for (var i = 1; i < list.Count; i++)
{
if(list[i].Filter != list[i - 1].Filter)  // reset numbering
{
rank = 1;
rowNumber = 1;
}
else
{
rowNumber++;    // row-number always counts up
if(list[i].LastSpeedFigure != list[i - 1].LastSpeedFigure)
rank = rowNumber;    // only change rank if not tied
}
list[i].LastSpeedRank = rank;
}

您还可以实现IEnumerable扩展来执行此操作

public T WithRank<T>(
this IEnumerable<T> source,
Func<T, T, bool> partitioning,
Func<T, T, bool> ordering,
Action<T, int> setRank)
{
using var enumer = source.GetEnumerator();
if(!enumer.MoveNext())
yield break;
var previous = enumer.Current;
setRank(previous, 1);
yield return previous;
int rank = 1;
int rowNumber = 1;

while(enumer.MoveNext())
{
if(!partitioning(enumer.Current,previous))  // reset numbering
{
rank = 1;
rowNumber = 1;
}
else
{
rowNumber++;    // row-number always counts up
if(ordering(enumer.Current, previous))
rank = rowNumber;    // only change rank if not tied
}
setRank(previous, rank);
yield return enumer.Current;
}
}

像这样使用它

// the list must be pre-sorted by partitioning values then ordering values.
var list = _rankedList
.OrderBy(r => r.Filter)
.ThenByDescending(r => r.LastSpeedFigure)
.WithRanking(
(a, b) => a.Filter == b.Filter,
(a, b) => a.LastSpeedFigure == b.LastSpeedFigure,
(o, rank) => { o.LastSpeedRank = rank; })
.ToList();

您可以通过仅使用rowNumber变量并删除条件rank = rowNumber;语句来实现ROW_NUMBER。您可以通过将该行更改为rank++;并忽略rowNumber来实现DENSE_RANK。