1. IT宝库
  2. 编程技术问答
  3. 其他开发
  4. 使用linq的表聚合（计算平均值）

使用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.

上一篇：在对多个字段使用AddOrUpdate匹配的ASP.NET MVC播种时，出现 "在此情况下只支持原始类型或枚举 "的错误。
下一篇：LINQ挑战:将此代码从方法链转换为标准LINQ
相关问答
  Python按月聚合并计算平均值
  删除重复项目并使用LINQ计算平均值
  MySQL计算平均值
  计算阵列的平均值
  我需要计算平均值
  帮我计算平均值
  计算Java的平均值
  你如何计算平均值？
  使用属性值计算平均值
  如何计算和使用CVMAT平均值
更多内容  更多相关问答
相关标签/搜索
python date csv pandas aggregate c# linq group-by mysql sql