按DateTime对特定时间间隔进行分组

本文关键字：DateTime 定时间 | 更新日期: 2023-09-27 18:01:58

我有一个以下类的对象列表:

public class CounterData 
{
    public DateTime counterTime { get; set; }
    public int counterName { get; set; }
    public int count { get; set; }
}

例如，我有以下格式为{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对计数器进行分组。对于上面的例子，结果列表应该是:

{"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:00AM-9:15AM, counter1有2个条目。所以count的值是所有条目的和。其他计数器也一样。

我们可以使用LINQ GroupBy来解决这个问题吗?如果是，那又是怎样的呢?

按DateTime对特定时间间隔进行分组

这里有一个将时间四舍五入到最接近的15分钟的解决方案。它使用AddSeconds来消除DateTime的秒部分，然后使用AddMinutes和%来舍入分钟:

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到实体(即实体框架)，所以你需要稍微调整它使用DbFunctions方法:

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

这里有一个按15分钟分组约会的例子。它可能不是很有效，而且它不适用于EF。但只要你只做LINQ到对象，你应该没问题。

在这个问题的扩展帮助下:

public static class DateTimeExtensions
{
    public static DateTime RoundDown(this DateTime dt, TimeSpan d)
    {
        return new DateTime(((dt.Ticks + 1) / d.Ticks) * d.Ticks);
    }
}

分组:

var counter = new List<CounterData>
{
    new CounterData {counterTime = DateTime.Now.AddMinutes(10), counterName = "counter1" },
    new CounterData {counterTime = DateTime.Now.AddMinutes(15), counterName = "counter2" },
    new CounterData {counterTime = DateTime.Now.AddMinutes(20), counterName = "counter3" },
    new CounterData {counterTime = DateTime.Now.AddMinutes(25), counterName = "counter3" },
    new CounterData {counterTime = DateTime.Now.AddMinutes(30), counterName = "counter1" },
    new CounterData {counterTime = DateTime.Now.AddMinutes(35), counterName = "counter3" },
    new CounterData {counterTime = DateTime.Now.AddMinutes(40), counterName = "counter2" },
    new CounterData {counterTime = DateTime.Now.AddMinutes(45), counterName = "counter1" },
    new CounterData {counterTime = DateTime.Now.AddMinutes(50), counterName = "counter1" },
    new CounterData {counterTime = DateTime.Now.AddMinutes(55), counterName = "counter2" },
};
// Now it's grouped by date.
var groupedCounters = counter.GroupBy(x => x.counterTime.RoundDown(TimeSpan.FromMinutes(15))).Select(d => new { Date = d.Key, Counters = d.ToList() });
List<CounterData> result = new List<CounterData>();
// With foreach.
foreach (var groupedCounter in groupedCounters)
{
    // Now we group by name as well.
    var countersByName =
        groupedCounter.Counters.GroupBy(x => x.counterName)
            .Select(
                x =>
                    new CounterData
                    {
                        count = x.Sum(item => item.Count),
                        counterTime = groupedCounter.Date,
                        counterName = x.Key
                    });
    result.AddRange(countersByName);
}
// Or with LINQ.
foreach (var countersByName in groupedCounters.Select(groupedCounter => groupedCounter.Counters.GroupBy(x => x.counterName)
    .Select(
        x =>
            new CounterData
            {
                count = x.Sum(item => item.Count),
                counterTime = groupedCounter.Date,
                counterName = x.Key
            })))
{
    result.AddRange(countersByName);
}
相关文章：