Aggregate data in DataTable in time intervals (5 minutes)

Question

I have a DataTable

            DataTable dt = new DataTable();
            dt.Columns.Add("ts");
            dt.Columns.Add("agent");
            dt.Columns.Add("host");
            dt.Columns.Add("metric");
            dt.Columns.Add("val");

My data comes in 15 seconds intervals; and I need to get MAX "val" for a period of 5 minutes for each host/agent/metric (including the 5 min timestamp indicator)

This is the colosest thing that I have.

        var q1 = from r in dt.Rows.Cast<DataRow>()
                 let ts = Convert.ToDateTime(r[0].ToString())
                    group r by new DateTime(ts.Year, ts.Month, ts.Day, ts.Hour, ts.Minute, ts.Second)
                        into g
                        select new
                        {                                
                            ts = g.Key,
                            agentName = g.Select(r => r[1].ToString()),
                            Sum = g.Sum(r => (int.Parse(r[4].ToString()))),
                            Average = g.Average(r => (int.Parse(r[4].ToString()))),
                            Max = g.Max(r => (int.Parse(r[4].ToString())))
                        };

Pretty lousy

Servy · Accepted Answer · 2013-11-27T19:17:24.323

1

To group the times by five minute intervals we can simply divide the Ticks in the time by the size of our interval, which we can pre-compute. In this case, it's the number of ticks in five minutes:

long ticksInFiveMinutes = TimeSpan.TicksPerMinute * 5;

The query then becomes:

var query = from r in dt.Rows.Cast<DataRow>()
            let ts = Convert.ToDateTime(r[0].ToString())
            group r by new { ticks = ts.Ticks / ticksInFiveMinutes, agent, host }
            into g
            let key = new DateTime(g.Key * ticksInFiveMinutes)
            select new
            {
                ts = key,
                agentName = g.Select(r => r[1].ToString()),
                Sum = g.Sum(r => (int.Parse(r[4].ToString()))),
                Average = g.Average(r => (int.Parse(r[4].ToString()))),
                Max = g.Max(r => (int.Parse(r[4].ToString())))
            };

edited Nov 27 '13 at 19:17

answered Nov 26 '13 at 21:56

Servy

202,030
26
332
449

`ts = g.Key` produces the integer number; how do I actually get the timetamp (they should be in 5 min increments) – Andrew Nov 26 '13 at 22:08
@Andrew There is a constructor for `DateTime` that accepts a number of ticks; don't forget to multiply it by `ticksInFiveMinutes` first though. – Servy Nov 26 '13 at 22:12
I noticed that it is only grouping by time, how do I add dditional group by agent, host and metric? – Andrew Nov 27 '13 at 15:32
I tried it, it gives me an error: `Error 1 Invalid anonymous type member declarator. Anonymous type members must be declared with a member assignment, simple name or member access. ` – Andrew Nov 27 '13 at 15:43
@Andrew So then declare the anonymous type through member assignment, as the error says. – Servy Nov 27 '13 at 15:45

score 0 · Answer 2 · edited May 23 '17 at 10:25

How about the following approach... Define a GetHashcode method:

 public DateTime Arrange5Min(DateTime value)
 {
    var stamp = value.timestamp;
    stamp = stamp.AddMinutes(-(stamp.Minute % 5));
    stamp = stamp.AddMilliseconds(-stamp.Millisecond - 1000 * stamp.Second);
    return stamp;
 }

public int MyGetHashCode(DataRow r)
{
        unchecked // Overflow is fine, just wrap
    {
       int hash = 17;
       // Suitable nullity checks etc, of course :)
       hash = hash * 23 + r[1].ToString().GetHashCode();
       hash = hash * 23 + r[2].ToString().GetHashCode();
       hash = hash * 23 + r[3].ToString().GetHashCode();

       var stamp = Arrange5Min(Convert.ToDateTime(r[0].ToString()));

       hash = hash * 23 + stamp.GetHashCode();
       return hash;
    } 
}

borrowed from here: What is the best algorithm for an overridden System.Object.GetHashCode? and LINQ aggregate and group by periods of time

Then use the function in Linq

 var q1 = from r in dt.Rows.Cast<DataRow>()
 group r by MyGetHashCode(r)
 into g
 let intermidiate = new { 
   Row = g.First(), 
   Max = g.Max(v => (int.Parse(r[4].ToString())))
 }
 select 
  new {
    Time = Arrange5Min(Convert.ToDateTime(intermidiate[0].ToString())),
    Host = intermidiate.Row[2].ToString(),
    Agent = intermidiate.Row[1].ToString(),
    Metric = intermidiate.Row[3].ToString(),
    Max = g.Max(v => (int.Parse(r[4].ToString())))
 }

Hash codes, by their very nature, will collide. This doesn't handle collisions at all. — Servy, Nov 26 '13 at 21:47

Aggregate data in DataTable in time intervals (5 minutes)

2 Answers2