LINQ如何在GroupBy中显示所有结果的平均值 [英] LINQ How to Show Average of all results in GroupBy

问题描述

我正在尝试编写一个查询，该查询返回在票务系统中花在工作上的平均时间. 每个作业中都有多个时间日志，因此我需要按SO编号进行分组，然后以某种方式获得所有结果的平均值.

I am trying to write a query which returns the average time spent on a job in a ticketing system. There are multiple time logs in each job so I need to group by the SO Number and then somehow get an average of all of the results.

当前查询返回每个服务订单的列表以及花费在该工作上的总分钟数.

The current query returns a list of every service order and the total minutes spent to the job.

如何让我知道每份工作的平均时间?

How do I make this show me the average minutes spent on each job?

from so in TblServiceOrders
from sologs in TblSOLogs.Where(x => x.SONumber == so.SONumber).DefaultIfEmpty()


where so.DateClosed >= new DateTime(2013,07,01) 
where so.DateClosed <= new DateTime(2013,07,02)
where sologs.ElapsedHours != 0 || sologs.ElapsedMinutes != 0

group new { sologs.ElapsedHours, sologs.ElapsedMinutes } by so into g
select new { 
g.Key.SONumber,
elapsed = g.Average (x => (x.ElapsedHours == null ? 0 : x.ElapsedHours * 60) + (x.ElapsedMinutes == null ? 0 : x.ElapsedMinutes))
}

== EDIT ==

==EDIT==

这看起来越来越近了，但是它给了我每个时间日志的平均值，而不是每个SO中总时间日志的平均值.

This looks like it is getting close but it is giving me an average of every time log and not an average of the total time logs in each SO.

请帮助?

from so in TblServiceOrders
join sologs in TblSOLogs on so.SONumber equals sologs.SONumber

where so.DateClosed >= new DateTime(2013,07,01) 
where so.DateClosed <= new DateTime(2013,07,03)
where sologs.ElapsedHours != 0 || sologs.ElapsedMinutes != 0

group sologs.SONumber by sologs into g
group new {g.Key.ElapsedHours, g.Key.ElapsedMinutes} by "Total" into t

select t.Average (x => (x.ElapsedHours == null ? 0 : x.ElapsedHours * 60) + (x.ElapsedMinutes == null ? 0 : x.ElapsedMinutes))

推荐答案

我已经完成了大部分工作，希望这可以对其他人有所帮助.

I've worked it out for the most part, Hopefully this can help others.

我的问题的第一部分是将所有结果归入同一组，而对Group By没有明显的价值.这是通过在Group By中声明一个字符串(例如"Total")来实现的.

The first part of my question was getting all the results into the same group without a distinct value to Group By. This was achieved by declaring a string in the Group By such as "Total".

例如:

group tblName.FieldName by "Example" into group1

对于第二部分，我需要在另一个Group By的Sum上执行一个Group By.下面是声明从Group By的Sum生成的两个新值的示例:

For the second part I needed to perform a Group By on the Sum of another Group By. Below is an example of declaring two new values generated from the Sum of a Group By:

group new { tblName.FieldName1, tblName.FieldName2} by tblName into group1
group new { SumField1 = g.Sum (x => x.FieldName1), SumField2 = g.Sum (x => x.FieldName2) } by "Totals" into newgroup2

这是我最终编写的代码的完整示例，在LINQPad中效果很好，但我仍在努力实现Visual Studio C# DataGridView.

Here is a full example of the code I ended up writing, It works well in LINQPad but I am still working on implementation into a Visual Studio C# DataGridView.

from so in TblServiceOrders
join sologs in TblSOLogs on so.SONumber equals sologs.SONumber

where so.DateClosed >= new DateTime(2014,01,17) 
where so.DateClosed <= new DateTime(2014,01,17)
where sologs.ElapsedHours != 0 || sologs.ElapsedMinutes != 0

group new {sologs.ElapsedHours, sologs.ElapsedMinutes} by sologs.SONumber into g
group new {hours = g.Sum (x => x.ElapsedHours), mins = g.Sum (x => x.ElapsedMinutes)} by "Totals" into t

select new {
Average = t.Average (x => (x.hours * 60) + x.mins),
Count = t.Count ()
}

这篇关于LINQ如何在GroupBy中显示所有结果的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！