在LINQ中按时间范围分组

g.005 发表于 Dev

g.005

假设我们有一个类似的类：

    class Forecast
    {
      int LocationId;
      int DepartmentId;
      DateTime StartDate;
      DateTime EndDate;
      int CountOfEmployees;
    }

我有一个预测列表： List<Forecasts> forecasts

该列表按15分钟间隔分组，例如：

forecast[0] = new Forecast { LocationId = 1, DepartmentId = 1, StartTime = 2018-10-01 06:00:00.000, EndTime = 2018-10-01 06:15:00.000, CountOfEmployees = 2  }
forecast[1] = new Forecast { LocationId = 1, DepartmentId = 1, StartTime = 2018-10-01 06:15:00.000, EndTime = 2018-10-01 06:30:00.000, CountOfEmployees = 1  }
forecast[2] = new Forecast { LocationId = 1, DepartmentId = 1, StartTime = 2018-10-01 06:30:00.000, EndTime = 2018-10-01 06:45:00.000, CountOfEmployees = 3  }
forecast[3] = new Forecast { LocationId = 1, DepartmentId = 1, StartTime = 2018-10-01 06:45:00.000, EndTime = 2018-10-01 07:00:00.000, CountOfEmployees = 1  }
forecast[4] = new Forecast { LocationId = 1, DepartmentId = 1, StartTime = 2018-10-01 07:00:00.000, EndTime = 2018-10-01 07:15:00.000, CountOfEmployees = 2  }
forecast[5] = new Forecast { LocationId = 1, DepartmentId = 1, StartTime = 2018-10-01 07:15:00.000, EndTime = 2018-10-01 07:30:00.000, CountOfEmployees = 2  }
forecast[6] = new Forecast { LocationId = 1, DepartmentId = 1, StartTime = 2018-10-01 07:30:00.000, EndTime = 2018-10-01 07:45:00.000, CountOfEmployees = 5 }
forecast[7] = new Forecast { LocationId = 1, DepartmentId = 1, StartTime = 2018-10-01 07:45:00.000, EndTime = 2018-10-01 08:00:00.000, CountOfEmployees = 3  }
forecast[8] = new Forecast { LocationId = 2, DepartmentId = 2, StartTime = 2018-10-01 06:00:00.000, EndTime = 2018-10-01 06:15:00.000, CountOfEmployees = 2  }
forecast[9] = new Forecast { LocationId = 2, DepartmentId = 2, StartTime = 2018-10-01 06:15:00.000, EndTime = 2018-10-01 06:30:00.000, CountOfEmployees = 1  }
forecast[10] = new Forecast { LocationId = 2, DepartmentId = 2, StartTime = 2018-10-01 06:30:00.000, EndTime = 2018-10-01 06:45:00.000, CountOfEmployees = 3  }
forecast[11] = new Forecast { LocationId = 2, DepartmentId = 2, StartTime = 2018-10-01 06:45:00.000, EndTime = 2018-10-01 07:00:00.000, CountOfEmployees = 1  }
forecast[12] = new Forecast { LocationId = 2, DepartmentId = 2, StartTime = 2018-10-01 07:00:00.000, EndTime = 2018-10-01 07:15:00.000, CountOfEmployees = 2  }
forecast[13] = new Forecast { LocationId = 2, DepartmentId = 2, StartTime = 2018-10-01 07:15:00.000, EndTime = 2018-10-01 07:30:00.000, CountOfEmployees = 2  }
forecast[14] = new Forecast { LocationId = 2, DepartmentId = 2, StartTime = 2018-10-01 07:30:00.000, EndTime = 2018-10-01 07:45:00.000, CountOfEmployees = 5 }
forecast[15] = new Forecast { LocationId = 2, DepartmentId = 2, StartTime = 2018-10-01 07:45:00.000, EndTime = 2018-10-01 08:00:00.000, CountOfEmployees = 3  }

我希望将结果以60分钟的时间间隔，位置和部门以及countOfEmployees的总和分组。因此，预期结果应该是这样的：

result[0] = new Forecast { LocationId = 1, DepartmentId = 1, StartTime = 2018-10-01 06:00:00.000, EndTime = 2018-10-01 07:00:00.000, CountOfEmployees = 7  }
result[1] = new Forecast { LocationId = 1, DepartmentId = 1, StartTime = 2018-10-01 07:00:00.000, EndTime = 2018-10-01 08:00:00.000, CountOfEmployees = 12  }
result[2] = new Forecast { LocationId = 2, DepartmentId = 2, StartTime = 2018-10-01 07:00:00.000, EndTime = 2018-10-01 08:00:00.000, CountOfEmployees = 12  }
result[3] = new Forecast { LocationId = 2, DepartmentId = 2, StartTime = 2018-10-01 07:00:00.000, EndTime = 2018-10-01 08:00:00.000, CountOfEmployees = 12  }

有人能指出我正确的方向吗？

D-Shih

您可以尝试将linqselect与group by

建立时间在LINQ分组GroupBy为您逻辑EndTime，StartTime，LocationId

StartTime并EndTime每小时。

var result = forecast
.GroupBy(_ => new {
    _.LocationId,
    StartTime = new DateTime(_.StartTime.Year,
                              _.StartTime.Month,
                              _.StartTime.Day,
                              _.StartTime.Hour,0,0,0),
    EndTime = new DateTime(_.StartTime.Year,
                              _.StartTime.Month,
                              _.StartTime.Day,
                              _.StartTime.Hour + 1, 0, 0, 0)
})
.Select(x => new {
    x.Key.LocationId,
    x.Key.StartTime,
    x.Key.EndTime,
    CountOfEmployees = x.Sum(y=>y.CountOfEmployees)});

C＃在线

结果

LocationId : 1 StartTime : 10/1/2018 6:00:00 AM EndTime : 10/1/2018 7:00:00 AM  CountOfEmployees : 7
LocationId : 1 StartTime : 10/1/2018 7:00:00 AM EndTime : 10/1/2018 8:00:00 AM  CountOfEmployees : 12
LocationId : 2 StartTime : 10/1/2018 6:00:00 AM EndTime : 10/1/2018 7:00:00 AM  CountOfEmployees : 7
LocationId : 2 StartTime : 10/1/2018 7:00:00 AM EndTime : 10/1/2018 8:00:00 AM  CountOfEmployees : 12

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。

编辑于 2020-12-17

我来说两句

0 条评论

登录后参与评论

上一篇：“从表名中选择*”和“从表名中选择a。*”之间的技术区别是什么？

在LINQ中按时间范围分组

在LINQ中按时间范围分组

蓝屏死机没有修复解决方案

计算数据帧中每行的NA

UITableView的项目向下滚动后更改颜色，然后快速备份

Node.js中未捕获的异常错误，发生调用

在 Python 2.7 中。如何从文件中读取特定文本并分配给变量

Linux的官方Adobe Flash存储库是否已过时？

验证REST API参数

ggplot：对齐多个分面图-所有大小不同的分面

Mac OS X更新后的GRUB 2问题

通过 Git 在运行 Jenkins 作业时获取 ClassNotFoundException

带有错误“ where”条件的查询如何返回结果？

用日期数据透视表和日期顺序查询

VB.net将2条特定行导出到DataGridView

如何从视图一次更新多行（ASP.NET - Core）

Java Eclipse中的错误13，如何解决？

尝试反复更改屏幕上按钮的位置 - kotlin android studio

离子动态工具栏背景色

应用发明者仅从列表中选择一个随机项一次

当我尝试下载 StanfordNLP en 模型时，出现错误

python中的boto3文件上传

在同一Pushwoosh应用程序上Pushwoosh多个捆绑ID