LINQ to Entities分组数据时如何计算businessEmployeeCount列的中位数

解决方案 #

LINQ to Entities 未内置中位数聚合方法，无法直接转换为对应SQL语句执行，你可以根据数据规模选择以下两种实现方案：

方案1：内存端计算（适合数据量较小的场景） #

先将分组所需的原始数据拉取到内存，再对每个分组的目标列排序后计算中位数，修改后的代码如下：

private void button2_Click(object sender, EventArgs e)
{
    // 先从数据库查询所需字段，避免加载冗余数据
    var rawData = _database.jon_export
        .Select(t => new { t.county, t.businessEmployeeCount, t.businessRevenue, t.businessTurnover, t.booiqEconomicWellBeing })
        .ToList();

    var query = from t in rawData
                orderby t.businessEmployeeCount
                group t by t.county.ToString() into g
                where g.Count() > 0
                let orderedCounts = g.Select(x => x.businessEmployeeCount).OrderBy(v => v).ToList()
                let count = orderedCounts.Count
                select new
                {
                    County = g.Key,
                    CountValue = count,
                    BusinessEmployeeCount = count,
                    BusinessEmployeeAverageValue = g.Average(x => x.businessEmployeeCount),
                    // 计算中位数，偶数个元素时取中间两个的平均值，如需取中间位直接取orderedCounts[count/2]即可
                    BusinessEmployeeMedianValue = count % 2 == 1 
                        ? orderedCounts[count / 2] 
                        : (orderedCounts[count / 2 - 1] + orderedCounts[count / 2]) / 2.0,
                    BusinessRevenueAverageValue = g.Average(x => x.businessRevenue),  
                    BusinessTurnover = g.Average(x => x.businessTurnover),
                    BooiqEconomicWellBeing = g.Average(x => x.booiqEconomicWellBeing)
                };
    this.dataGridView1.DataSource = query.ToList();
}

注：如果目标列是整数类型且需要保留小数结果，注意提前做类型转换避免整数截断

方案2：数据库端计算（EF Core 6+ 适用，适合大数据量场景） #

可以直接调用EF Core内置的SQL百分位函数映射，PercentileCont(0.5) 对应连续型中位数，PercentileDisc(0.5) 对应离散型中位数，写法如下：

private void button2_Click(object sender, EventArgs e)
{
    var query = from t in _database.jon_export
                orderby t.businessEmployeeCount
                group t by t.county.ToString() into g
                where g.Count() > 0
                select new
                {
                    County = g.Key,
                    CountValue = g.Count(),
                    BusinessEmployeeCount = g.Count(),
                    BusinessEmployeeAverageValue = g.Average(x => x.businessEmployeeCount),
                    // 数据库端计算中位数
                    BusinessEmployeeMedianValue = g.PercentileCont(0.5).Within(x => x.businessEmployeeCount),
                    BusinessRevenueAverageValue = g.Average(x => x.businessRevenue),  
                    BusinessTurnover=g.Average(x => x.businessTurnover),
                    BooiqEconomicWellBeing=g.Average(x=>x.booiqEconomicWellBeing)
                };
    this.dataGridView1.DataSource = query.ToList();
}

注：该方法依赖数据库对百分位函数的支持，主流关系型数据库如SQL Server、PostgreSQL、MySQL 8.0+均支持该语法

内容的提问来源于stack exchange，提问作者Munir Hadrovic