在特定时间间隔内按日期分组

SohamC 发表于 Dev

苏咸

我有以下类别的对象的列表：

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:00 AM-9:15AM，有2个条目counter1。因此，该count值是条目的总和。其他柜台也是如此。

我们可以使用LINQ GroupBy解决此问题吗？如果是这样，那又如何？

戴维

下面是查房的时间解决下来到最近的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 to Entities（即Entity Framework），因此您需要对其进行一些微调以使用以下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)
    });

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

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