LINQ 按时间段聚合和分组 [英] LINQ aggregate and group by periods of time

问题描述

我试图了解如何使用 LINQ 按时间间隔对数据进行分组；然后理想地聚合每个组.

I'm trying to understand how LINQ can be used to group data by intervals of time; and then ideally aggregate each group.

找到大量具有明确日期范围的示例，我正在尝试按 5 分钟、1 小时、1 天等时间段进行分组.

Finding numerous examples with explicit date ranges, I'm trying to group by periods such as 5-minutes, 1-hour, 1-day.

例如，我有一个用一个值包装 DateTime 的类:

For example, I have a class that wraps a DateTime with a value:

public class Sample
{
     public DateTime timestamp;
     public double value;
}

这些观察结果作为一个系列包含在 List 集合中:

These observations are contained as a series in a List collection:

List<Sample> series;

因此，要按每小时的时间段分组并按平均值汇总值，我正在尝试执行以下操作:

So, to group by hourly periods of time and aggregate value by average, I'm trying to do something like:

var grouped = from s in series
              group s by new TimeSpan(1, 0, 0) into g
              select new { timestamp = g.Key, value = g.Average(s => s.value };

这从根本上是有缺陷的，因为它对 TimeSpan 本身进行了分组.我无法理解如何在查询中使用 TimeSpan(或任何表示间隔的数据类型).

This is fundamentally flawed, as it groups the TimeSpan itself. I can't understand how to use the TimeSpan (or any data type representing an interval) in the query.

推荐答案

您可以将时间戳四舍五入到下一个边界(即向下到过去最近的 5 分钟边界)并将其用作分组:

You could round the time stamp to the next boundary (i.e. down to the closest 5 minute boundary in the past) and use that as your grouping:

var groups = series.GroupBy(x =>
{
    var stamp = x.timestamp;
    stamp = stamp.AddMinutes(-(stamp.Minute % 5));
    stamp = stamp.AddMilliseconds(-stamp.Millisecond - 1000 * stamp.Second);
    return stamp;
})
.Select(g => new { TimeStamp = g.Key, Value = g.Average(s => s.value) })
.ToList();

以上通过在分组中使用修改的时间戳来实现这一点，它将分钟设置为前 5 分钟的边界并删除秒和毫秒.当然，同样的方法可以用于其他时间段，即小时和天.

Above achieves that by using a modified time stamp in the grouping, which sets the minutes to the previous 5 minute boundary and removes the seconds and milliseconds. The same approach of course can be used for other time periods, i.e. hours and days.

基于这个组成的样本输入:

Based on this made up sample input:

var series = new List<Sample>();
series.Add(new Sample() { timestamp = DateTime.Now.AddMinutes(3) });
series.Add(new Sample() { timestamp = DateTime.Now.AddMinutes(4) });
series.Add(new Sample() { timestamp = DateTime.Now.AddMinutes(5) });
series.Add(new Sample() { timestamp = DateTime.Now.AddMinutes(6) });
series.Add(new Sample() { timestamp = DateTime.Now.AddMinutes(7) });
series.Add(new Sample() { timestamp = DateTime.Now.AddMinutes(15) });

为我制作了 3 个组，一组的分组时间戳为 3:05，一组为 3:10，另一组为下午 3:20(您的结果可能因当前时间而异).

3 groups were produced for me, one with grouping timestamp 3:05, one with 3:10 and one with 3:20 pm (your results may vary based on current time).

这篇关于LINQ 按时间段聚合和分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！