使用linq的表聚合(计算平均值)[英] Table aggregation using linq (calculate Average value)

本文是小编为大家收集整理的关于使用linq的表聚合(计算平均值)的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

如何使用LINQ查询在表下方聚集

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值的每个时段,

仅具有不同的标记名称.

例如. 输出应为

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"

---编辑 - 这些是我尝试过的几个查询.

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的新手,所以无法提取适当的结果.

我已经写了SQL查询,它给出了完美的结果

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中无法使用此查询或能够将其转换相同.

任何帮助pls ..

推荐答案

按数据进行分组总是很棘手的,因为在生成SQL代码时,C#上的许多功能都不可用,并且可能引起问题.

由于我无法重现您的环境,因此我仅创建了一些基于LINQ的示例.您可能必须使用truncateTime重新创建它,以使其在DB级别完全运行.

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可以合并,但是我离开了,以获得更好的可读性.

请参阅有关truncateTime的更多信息.

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

问题描述

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

expected result should be like

only distinct tagname per timeslot with avegate value into that slot,

e.g. output should be

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();

I'm new to LINQ, so not able to pull proper result..

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

This query gives perfect result but since code is in linq not able to use this query or able to convert the same.

Any help pls..

推荐答案

Grouping by DateTimes is always tricky as many functions available at C# are not available when generating SQL code and probably can cause problems.

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();

I would say, that probably the 1st Select and GroupBy can be merged, but I left separated for better readibility.

Refer to this for more information about TruncateTime.