在特定时间间隔内按日期分组 [英] 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屋！