在特定时间间隔内按日期分组 [英] Groupby DateTime for specific intervals
问题描述
我有以下类别的对象的列表:
I have a list of objects of the following class:
public class CounterData
{
public DateTime counterTime { get; set; }
public int counterName { get; set; }
public int count { get; set; }
}
例如,我有以下格式为{counterTime, counterName, count}
的数据列表:
For example I have the following list of data in the format {counterTime, counterName, count}
:
{"Aug 8 2016 9:00AM","counter1",11}
{"Aug 8 2016 9:05AM","counter2",12}
{"Aug 8 2016 9:11AM","counter3",47}
{"Aug 8 2016 9:12AM","counter3",20}
{"Aug 8 2016 9:13AM","counter1",12}
{"Aug 8 2016 9:30AM","counter3",61}
{"Aug 8 2016 9:35AM","counter2",35}
{"Aug 8 2016 9:39AM","counter1",16}
{"Aug 8 2016 9:40AM","counter1",92}
{"Aug 8 2016 9:53AM","counter2",19}
我想按每15分钟间隔汇总的counterName
和counterTime
对计数器进行分组.对于上面的示例,结果列表应为:
And I want to group the counters by counterName
and counterTime
aggregated for every 15 minute interval. For the above example the resultant list should be:
{"Aug 8 2016 9:00AM","counter1",23}
{"Aug 8 2016 9:00AM","counter2",12}
{"Aug 8 2016 9:00AM","counter3",67}
{"Aug 8 2016 9:30AM","counter3",61}
{"Aug 8 2016 9:30AM","counter2",35}
{"Aug 8 2016 9:30AM","counter1",108}
{"Aug 8 2016 9:45AM","counter2",19}
从9:00 AM-9:15AM,有2个counter1
条目.因此,count
值是条目的总和.其他计数器也是如此.
From 9:00AM-9:15AM, there were 2 entries of counter1
. So the count
value is a sum of the entries. Similarly for other counters.
我们可以使用 LINQ GroupBy 解决这个问题?如果是这样,怎么办?
Can we use LINQ GroupBy to solve this? If so then how?
推荐答案
以下是将时间向下舍入到最接近的15分钟的解决方案.它使用AddSeconds
本质上摆脱了DateTime
的秒部分,然后使用AddMinutes ="nofollow"> %
将分钟取整:
Here's a solution that rounds the times down to the nearest 15 minutes. It uses AddSeconds
to essentially get rid of the seconds part of the DateTime
, then AddMinutes
with %
to round down the minutes:
var data = counters
.GroupBy(cd => new
{
Date = cd.counterTime.AddSeconds(-cd.counterTime.Second)
.AddMinutes(-cd.counterTime.Minute % 15),
CounterName = cd.counterName
})
.Select(g => new
{
Date = g.Key.Date,
CounterName = g.Key.CounterName,
SumCount = g.Sum(cd => cd.count)
});
但是,这不适用于LINQ to Entities(即Entity Framework),因此您需要对其进行一些微调,以使用DbFunctions
方法:
However, this will not work with LINQ to Entities (i.e. Entity Framework), so you need to tweak it slightly to use the DbFunctions
methods:
var data = counters
.GroupBy(cd => new
{
Date = DbFunctions.AddMinutes(
DbFunctions.AddSeconds(cd.counterTime, -cd.counterTime.Second),
-cd.counterTime.Minute % 15),
CounterName = cd.counterName
})
.Select(g => new
{
Date = g.Key.Date,
CounterName = g.Key.CounterName,
SumCount = g.Sum(cd => cd.count)
});
这篇关于在特定时间间隔内按日期分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!