使用linq进行表聚合(计算平均值) [英] Table aggregation using linq (calculate Average value)
问题描述
如何使用linq查询汇总下表
How to Aggregate below table using linq query
Date tagname value
06-06-2018 14:15:00 Poll.Registers Block 0.310-PT-304_(4) 54.73497
06-06-2018 14:15:00 Poll.Registers Block 0.310-PT-304_(5) 3.417564
06-06-2018 14:15:00 Poll.Registers Block 0.310-PT-304_(4) 94.82829
06-06-2018 14:15:00 Poll.Registers Block 0.310-PT-304_(4) 15.08091
06-06-2018 14:15:00 Poll.Registers Block 0.310-PT-304_(5) 3.6422
06-06-2018 14:15:00 Poll.Registers Block 0.310-PT-304_(4) 5.078211
06-06-2018 14:15:00 Poll.Registers Block 0.310-PT-304_(4) 68.00956
06-06-2018 14:30:00 Poll.Registers Block 0.310-PT-304_(5) 94.6864
06-06-2018 14:30:00 Poll.Registers Block 0.310-PT-304_(4) 32.43211
06-06-2018 14:30:00 Poll.Registers Block 0.310-PT-304_(4) 65.16206
06-06-2018 14:30:00 Poll.Registers Block 0.310-PT-304_(5) 81.18947
06-06-2018 14:30:00 Poll.Registers Block 0.310-PT-304_(4) 4.419947
06-06-2018 14:30:00 Poll.Registers Block 0.310-PT-304_(4) 95.77668
06-06-2018 14:30:00 Poll.Registers Block 0.310-PT-304_(5) 10.43907
06-06-2018 14:30:00 Poll.Registers Block 0.310-PT-304_(4) 79.12902
06-06-2018 14:30:00 Poll.Registers Block 0.310-PT-304_(4) 62.20364
06-06-2018 14:30:00 Poll.Registers Block 0.310-PT-304_(5) 97.43433
06-06-2018 14:30:00 Poll.Registers Block 0.310-PT-304_(4) 25.74978
06-06-2018 14:45:00 Poll.Registers Block 0.310-PT-304_(5) 50.49747
06-06-2018 14:45:00 Poll.Registers Block 0.310-PT-304_(4) 65.33123
06-06-2018 14:45:00 Poll.Registers Block 0.310-PT-304_(4) 18.90912
06-06-2018 14:45:00 Poll.Registers Block 0.310-PT-304_(5) 55.9916
06-06-2018 14:45:00 Poll.Registers Block 0.310-PT-304_(4) 23.86106
06-06-2018 14:45:00 Poll.Registers Block 0.310-PT-304_(4) 18.72116
06-06-2018 14:45:00 Poll.Registers Block 0.310-PT-304_(5) 0.06596069
预期结果应类似于
每个时隙只有唯一的标记名，并且在该时隙中具有avegate值，
only distinct tagname per timeslot with avegate value into that slot,
例如 输出应该是
06-06-2018 14:15:00 Poll.Register Block 0.310-PT-304(4) "Value should be avg"
06-06-2018 14:15:00 Poll.Register Block 0.310-PT-304(5) "Value should be avg"
06-06-2018 14:30:00 Poll.Register Block 0.310-PT-304(4) "Value should be avg"
06-06-2018 14:30:00 Poll.Register Block 0.310-PT-304(5) "Value should be avg"
06-06-2018 14:45:00 Poll.Register Block 0.310-PT-304(4) "Value should be avg"
06-06-2018 14:45:00 Poll.Register Block 0.310-PT-304(5) "Value should be avg"
-编辑 这些是我尝试过的几个查询.
--- EDIT -- These are couple of queries I've tried..
var g = (from x in ObjEntities.TagDataValues
where x.ValueDateTime >= FromDate && x.ValueDateTime <= EndDate && MachineName.Contains(x.MachineName) && ServerName.Contains(x.ServerName) && Tags.Contains(x.TagName)
select new
{
TagName = x.TagName,
MachineName = x.MachineName,
ServerName = x.ServerName,
TagValue = x.TagValue,
DtTime = x.ValueDateTime
}).ToList().GroupBy(cd => new
{
date = cd.DtTime.AddSeconds(-cd.DtTime.Second).AddMinutes(-cd.DtTime.Minute % 15),
tagname = cd.TagName,
tagvalue = cd.TagValue
}).ToList().Select(o => new
{
Date = o.Key.date,
tagname = o.Key.tagname,
value = o.Key.tagvalue
}).ToList().GroupBy(tr => tr.tagname).Select(x => new {
TagName = x.Key,
Value = x.Average(gf => gf.value),
Date = x.Select(gf => gf.Date).Distinct()
}).ToList();
var g = (from x in ObjEntities.TagDataValues
where x.ValueDateTime >= FromDate && x.ValueDateTime <= EndDate && MachineName.Contains(x.MachineName) && ServerName.Contains(x.ServerName) && Tags.Contains(x.TagName)
select new
{
TagName = x.TagName,
MachineName = x.MachineName,
ServerName = x.ServerName,
TagValue = x.TagValue,
DtTime = x.ValueDateTime
}).ToList().GroupBy(cd => new
{
date = cd.DtTime.AddSeconds(-cd.DtTime.Second).AddMinutes(-cd.DtTime.Minute % 15),
tagname = cd.TagName,
tagvalue = cd.TagValue
}).ToList().Select(o => new
{
Date = o.Key.date,
tagname = o.Key.tagname,
value = o.Key.tagvalue
}).ToList().GroupBy(tr => new {
TagName = tr.tagname,
DateTime = tr.Date
} ).Select(x => new {
TagName = x.Key,
Value = x.Average(gf => gf.value),
Date = x.Key.DateTime
}).ToList();
我是LINQ的新手，所以无法获得正确的结果.
I'm new to LINQ, so not able to pull proper result..
我已经为它编写了sql查询，从
I've written sql query for same, its give perfect result as of
declare @StartDate DateTime = CAST('06/06/2018 14:26:56' AS datetime) declare @EndDate DateTime = CAST('06/06/2018 14:32:56' AS datetime) SELECT CONVERT(nvarchar, ValueDateTime, 113) as MINUTE , avg(TagDataValue.TagValue) Value, TagName FROM TagDataValue WHERE ValueDateTime >= @StartDate AND ValueDateTime <= @EndDate and TagName in ('Poll.Registers Block 0.310-PT-304_(4)','Poll.Registers Block
0.310-PT-304_(5)') GROUP BY CONVERT(nvarchar, ValueDateTime, 113) , TagName
此查询给出了理想的结果，但是由于代码在linq中无法使用此查询或无法将其转换.
This query gives perfect result but since code is in linq not able to use this query or able to convert the same.
任何帮助.
推荐答案
按DateTimes分组总是很棘手，因为生成SQL代码时C#上可用的许多功能不可用，并且可能会引起问题.
Grouping by DateTimes is always tricky as many functions available at C# are not available when generating SQL code and probably can cause problems.
由于我无法重现您的环境，因此创建了一些仅基于linq的示例.您可能必须使用TruncateTime重新创建，以便它可以在数据库级别完全运行.
As I have no way to reproduce your environment, I have created some example based only linq. You probably will have to recreated using TruncateTime so that it runs fully at DB level.
var g = entityList
.Where(x => x.ValueDateTime >= FromDate && x.ValueDateTime <= ToDate && MachineNames.Contains(x.MachineName))
.Select(x => new
{
quarterDateTime = x.ValueDateTime
.AddSeconds(-x.ValueDateTime.Second)
.AddMinutes(-x.ValueDateTime.Minute % 15),
x.MachineName,
x.Value
})
.GroupBy( x => new { x.quarterDateTime, x.MachineName })
.Select( x => new { x.Key.quarterDateTime, x.Key.MachineName, AverageValue = x.Average(p => p.Value) })
.OrderBy( x => x.quarterDateTime )
.ToList();
我想说的是，第一选择和GroupBy可能可以合并，但为了更好的可读性我分开了.
I would say, that probably the 1st Select and GroupBy can be merged, but I left separated for better readibility.
请参考此有关TruncateTime的更多信息.
Refer to this for more information about TruncateTime.
这篇关于使用linq进行表聚合(计算平均值)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
