c# Linq Rank like TSQL分区BY多列

1szpjjfi 于 2022-12-06 发布在 C#

关注(0)|答案(1)|浏览(130)

我有一个List<T>，并试图使用Linq进行排名，因为我可以使用TSQL。
测试SQL：RANK() OVER ( PARTITION BY [Time], [Filter] ORDER BY [Speed] Desc) AS speedrank
计算排名后，尝试使用计算的排名更新List<T>中的排名列。
我可以将数据写入sql表并使用tsql进行查询，但如果可能的话，我更喜欢使用应用程序中的强类型列表。
Rank必须考虑基于分区的重复Speed值，因此排序和增加行号不能按预期工作。

更新日期：在MainForm.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; }

}

我尝试按Filter属性进行分区，并按LastSpeedFigure Desc进行排名，因此如果两个值相同，则最高数字排名为1或联合第一。
此致！
尼尔

linq

来源：https://stackoverflow.com/questions/73270288/c-sharp-linq-rank-like-tsql-partition-by-multiple-columns

1条答案

按热度按时间

lyr7nygr1#

您应该能够使用下面的代码（抱歉，我还没有测试过这些代码）。

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。

赞(0）回复(0）举报 2022-12-06

我来回答

c# Linq Rank like TSQL分区BY多列

1条答案

相关问题

热门标签

最新问答