如何在LINQ查询中获得平均分组结果[英] How to get Average of grouped results in Linq query

本文是小编为大家收集整理的关于如何在LINQ查询中获得平均分组结果的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我有一个LINQ查询,从前12个月中获得评分:

var ratingsByMonth =
    from month in Enumerable.Range(0, 12)
    let key = new { Year = DateTime.Now.AddMonths(-month).Year, Month = DateTime.Now.AddMonths(-month).Month }
    join groupedRating in ratings on key
            equals new
            {
                groupedRating.RatingDate.Year,
                groupedRating.RatingDate.Month
            } into g
    select new {
        Date = key,
        Rating = g.Select(x=>x.Rating)
    };

ratingsbymonth从我的数据集返回以下JSON:

{Date: {Year: 2016, Month: 11}, Rating: [4]}
{Date: {Year: 2016, Month: 10}, Rating: [5, 5, 4]}
{Date: {Year: 2016, Month: 9}, Rating: []}
{Date: {Year: 2016, Month: 8}, Rating: []}
{Date: {Year: 2016, Month: 7}, Rating: []}
{Date: {Year: 2016, Month: 6}, Rating: []}
{Date: {Year: 2016, Month: 5}, Rating: []}
{Date: {Year: 2016, Month: 4}, Rating: []}
{Date: {Year: 2016, Month: 3}, Rating: []}
{Date: {Year: 2016, Month: 2}, Rating: []}
{Date: {Year: 2016, Month: 1}, Rating: []}
{Date: {Year: 2015, Month: 12}, Rating: []}

但是,我试图将评分字段汇总为平均值.我已经尝试过:

return Json(new
{
    Average = ratingsByMonth.Average(x=>x.Rating),
    Dates = ratingsByMonth.Select(x => Date)
});

,但我在平均线上突出显示了以下错误:

不能隐式转换类型 'system.collections.generic.ienumerable'to'long?'

我正在寻找的输出是:

{Date: {Year: 2016, Month: 11}, Rating: 4}
{Date: {Year: 2016, Month: 10}, Rating: 3.5}
{Date: {Year: 2016, Month: 9}, Rating: 0}
{Date: {Year: 2016, Month: 8}, Rating: 0}
{Date: {Year: 2016, Month: 7}, Rating: 0}
{Date: {Year: 2016, Month: 6}, Rating: 0}
{Date: {Year: 2016, Month: 5}, Rating: 0}
{Date: {Year: 2016, Month: 4}, Rating: 0}
{Date: {Year: 2016, Month: 3}, Rating: 0}
{Date: {Year: 2016, Month: 2}, Rating: 0}
{Date: {Year: 2016, Month: 1}, Rating: 0}
{Date: {Year: 2015, Month: 12}, Rating: 0}

编辑:

尝试更改为以下内容:

return Json(new
{
    Average = ratingsByMonth.Select(x => x.Rating).SelectMany(x => x).Average(),
    Dates = ratingsByMonth.Select(x => x.Date.Month)
});

但是,这只是在整个数据集中给我一个单个"平均值"值,而不是每个日期的平均值.

编辑:

这是示例数据:

RatingID    Rating  RatingDate
5           5   2016-10-09 20:11:29.590
11          0   2016-10-09 20:14:42.503
21          5   2016-10-09 20:24:15.667
60          4   2016-10-28 11:01:21.220
64          4   2016-11-03 16:30:47.657

因此,11月的平均值应为4,十月应为3.5(5+0+5+4/4)

推荐答案

在原始查询中,您可能想这样做.

//other code removed for brevity
select new {
    Date = key,
    Rating = g.Count() > 0 ? g.Average(x => x.Rating) : 0
};

然后返回结果

return Json(ratingsByMonth);

其他推荐答案

这里的问题是属性额定值本身是一个序列,而不是可以考虑普通方法的离散值.

听起来您实际上想做类似的事情:将所有评分集合成一个序列,然后在所有结果值中取平均值.

类似:

var avg = ratingsByMonth.Select(x => x.Rating).SelectMany(x => x).Average()

这使用选择来产生一系列额定阵列.然后,SelectMany然后将列表弄平,然后您可以平均.

本文地址:https://www.itbaoku.cn/post/1556779.html

问题描述

I have a linq query that gets ratings from the previous 12 months:

var ratingsByMonth =
    from month in Enumerable.Range(0, 12)
    let key = new { Year = DateTime.Now.AddMonths(-month).Year, Month = DateTime.Now.AddMonths(-month).Month }
    join groupedRating in ratings on key
            equals new
            {
                groupedRating.RatingDate.Year,
                groupedRating.RatingDate.Month
            } into g
    select new {
        Date = key,
        Rating = g.Select(x=>x.Rating)
    };

ratingsByMonth returns the following JSON from my data set:

{Date: {Year: 2016, Month: 11}, Rating: [4]}
{Date: {Year: 2016, Month: 10}, Rating: [5, 5, 4]}
{Date: {Year: 2016, Month: 9}, Rating: []}
{Date: {Year: 2016, Month: 8}, Rating: []}
{Date: {Year: 2016, Month: 7}, Rating: []}
{Date: {Year: 2016, Month: 6}, Rating: []}
{Date: {Year: 2016, Month: 5}, Rating: []}
{Date: {Year: 2016, Month: 4}, Rating: []}
{Date: {Year: 2016, Month: 3}, Rating: []}
{Date: {Year: 2016, Month: 2}, Rating: []}
{Date: {Year: 2016, Month: 1}, Rating: []}
{Date: {Year: 2015, Month: 12}, Rating: []}

However I'm trying to aggregate the Rating field into an Average. I've tried this:

return Json(new
{
    Average = ratingsByMonth.Average(x=>x.Rating),
    Dates = ratingsByMonth.Select(x => Date)
});

But I get the following error highlighted on the Average - line:

Cannot implicitly convert type 'System.Collections.Generic.IEnumerable' to 'long?'

The output that I am looking for is:

{Date: {Year: 2016, Month: 11}, Rating: 4}
{Date: {Year: 2016, Month: 10}, Rating: 3.5}
{Date: {Year: 2016, Month: 9}, Rating: 0}
{Date: {Year: 2016, Month: 8}, Rating: 0}
{Date: {Year: 2016, Month: 7}, Rating: 0}
{Date: {Year: 2016, Month: 6}, Rating: 0}
{Date: {Year: 2016, Month: 5}, Rating: 0}
{Date: {Year: 2016, Month: 4}, Rating: 0}
{Date: {Year: 2016, Month: 3}, Rating: 0}
{Date: {Year: 2016, Month: 2}, Rating: 0}
{Date: {Year: 2016, Month: 1}, Rating: 0}
{Date: {Year: 2015, Month: 12}, Rating: 0}

Edit:

Have tried changing to the following:

return Json(new
{
    Average = ratingsByMonth.Select(x => x.Rating).SelectMany(x => x).Average(),
    Dates = ratingsByMonth.Select(x => x.Date.Month)
});

However, this just gives me a single 'Average' value across the whole data set, rather than an average per date.

Edit:

Here is sample data:

RatingID    Rating  RatingDate
5           5   2016-10-09 20:11:29.590
11          0   2016-10-09 20:14:42.503
21          5   2016-10-09 20:24:15.667
60          4   2016-10-28 11:01:21.220
64          4   2016-11-03 16:30:47.657

So for November the average should be 4, and October should be 3.5 (5+0+5+4 / 4)

推荐答案

In the original query you probably want to do this.

//other code removed for brevity
select new {
    Date = key,
    Rating = g.Count() > 0 ? g.Average(x => x.Rating) : 0
};

And then return the result

return Json(ratingsByMonth);

其他推荐答案

The issue here is that the property Rating is itself a sequence, not a discrete value that you can ask the Average method to consider.

It sounds like you actually want to do something like: flatten all of the Rating collections into a single sequence and then take the average across all of the resulting values.

Something like:

var avg = ratingsByMonth.Select(x => x.Rating).SelectMany(x => x).Average()

This uses Select to produce a sequence of Rating arrays. SelectMany then flattens the list, after which you can take the average.