如何在C#分批构建数据集时计算与SQL一致的DENSE_RANK()

问题根源 #

你当前分批计算DENSE_RANK()的结果和全量SQL计算不一致，核心原因是DENSE_RANK()是基于全量数据的排序分组逻辑：相同排序键（SortOrder1/SortOrder2/SortOrder3）的条目共享同一rank，不同排序键按顺序递增。分批计算时，每一批的排序键无法覆盖全局所有可能的组合，后续批次的新排序键可能插入到已有排序序列的中间，导致之前计算的rank失效。

可行解决方案 #

1. 预计算全局排序键的Rank映射（最优方案，若能提前获取所有排序键） #

如果可以提前知晓所有可能的SortOrder1/SortOrder2/SortOrder3组合，先基于全量组合预计算好对应的DENSE_RANK，再分批给数据条目赋值：

步骤1：预生成Rank字典

// 从数据源获取所有唯一的排序键组合（如果是数据库，直接查全量）
var allSortCombos = dbContext.YourEntity
    .Select(x => new { x.SortOrder1, x.SortOrder2, x.SortOrder3 })
    .Distinct()
    .OrderBy(x => x.SortOrder1)
    .ThenBy(x => x.SortOrder2)
    .ThenBy(x => x.SortOrder3)
    .ToList();

// 构建排序键到DenseRank的映射字典
var rankMap = allSortCombos
    .Select((combo, index) => new 
    { 
        Key = Tuple.Create(combo.SortOrder1, combo.SortOrder2, combo.SortOrder3),
        Rank = index + 1
    })
    .ToDictionary(item => item.Key, item => item.Rank);

步骤2：分批处理数据时直接赋值

// 遍历每一批构建好的数据
foreach (var dataBatch in yourBatches)
{
    foreach (var item in dataBatch)
    {
        var key = Tuple.Create(item.SortOrder1, item.SortOrder2, item.SortOrder3);
        // 直接从字典取预计算好的rank
        if (rankMap.TryGetValue(key, out int denseRank))
        {
            item.DenseRank = denseRank;
        }
        // 处理意外新增的排序键（若有）
        else
        {
            // 可临时标记，后续统一补充计算
            item.DenseRank = -1;
        }
    }
}

2. 增量维护全局排序序列（适用于排序键动态生成的场景） #

如果无法提前获取所有排序键组合，需要每批处理时维护全局有序的排序键集合，并实时更新Rank映射：

步骤1：定义排序键比较器与全局集合

// 定义排序键的比较逻辑，和SQL的ORDER BY规则对齐
public class SortComboComparer : IComparer<Tuple<int, int, int>>
{
    public int Compare(Tuple<int, int, int> a, Tuple<int, int, int> b)
    {
        int cmp = a.Item1.CompareTo(b.Item1);
        if (cmp != 0) return cmp;
        cmp = a.Item2.CompareTo(b.Item2);
        if (cmp != 0) return cmp;
        return a.Item3.CompareTo(b.Item3);
    }
}

// 初始化全局有序排序键集合和Rank映射
var globalSortedCombos = new SortedSet<Tuple<int, int, int>>(new SortComboComparer());
var globalRankMap = new Dictionary<Tuple<int, int, int>, int>();
// 维护所有已处理的数据条目，用于Rank更新
var processedItems = new List<YourEntityType>();

步骤2：分批处理并更新Rank

foreach (var dataBatch in yourBatches)
{
    // 提取当前批次的新排序键
    var newCombos = dataBatch
        .Select(item => Tuple.Create(item.SortOrder1, item.SortOrder2, item.SortOrder3))
        .Distinct()
        .Where(combo => !globalSortedCombos.Contains(combo))
        .ToList();

    // 将新排序键加入全局有序集合
    foreach (var combo in newCombos)
    {
        globalSortedCombos.Add(combo);
    }

    // 重新生成全局Rank映射
    globalRankMap = globalSortedCombos
        .Select((combo, index) => new 
        { 
            Combo = combo, 
            Rank = index + 1 
        })
        .ToDictionary(k => k.Combo, v => v.Rank);

    // 给当前批次条目赋值Rank
    foreach (var item in dataBatch)
    {
        var key = Tuple.Create(item.SortOrder1, item.SortOrder2, item.SortOrder3);
        item.DenseRank = globalRankMap[key];
        processedItems.Add(item);
    }

    // 注意：如果新排序键插入到了已有序列的中间，会导致后续组合的Rank递增，需要更新历史条目
    foreach (var item in processedItems)
    {
        var key = Tuple.Create(item.SortOrder1, item.SortOrder2, item.SortOrder3);
        item.DenseRank = globalRankMap[key];
    }
}

3. 延迟全量计算（备选方案） #

如果可以接受最后统一处理，等所有批次构建完成后，用你提供的Linq代码全量计算一次，结果会和SQL的DENSE_RANK() OVER (ORDER BY SortOrder1, SortOrder2, SortOrder3)完全一致：

var fullDataset = processedItems.AsQueryable();
fullDataset.GroupBy(x => new { x.SortOrder1, x.SortOrder2, x.SortOrder3 })
           .OrderBy(g => g.Key.SortOrder1)
           .ThenBy(g => g.Key.SortOrder2)
           .ThenBy(g => g.Key.SortOrder3)
           .Select((g, i) => 
           {
               int rank = i + 1;
               foreach (var item in g)
               {
                   item.DenseRank = rank;
               }
               return g;
           })
           .ToList();

注意事项 #

• 预计算方案性能最优，但依赖于提前获取所有排序键组合；
• 增量维护方案适合动态生成排序键的场景，但数据量较大时，更新历史条目的操作会影响性能；
• 所有方案的核心是保证排序规则和SQL的ORDER BY完全对齐，否则Rank结果必然不一致。

内容的提问来源于stack exchange，提问作者xchg ax ax