LINQ group by Hour and Type (click/open ratio)
I have a table with the following fields:
- Action => Click/Open
- DateTime => Date and time action occured
example:
Action | DateTime
- click | 3/12/2010 3:00AM
- click | 3/12/2010 3:12AM
- open | 3/12/2010 3:34AM
- click | 3/12/2010 4:12AM
- click | 3/13/2开发者_StackOverflow社区010 1:12PM
etc..
I need to group these by hour so that I have a number of clicks and opens hourly.
how would i do it? thanks
var poo = new[]
{
new
{
Type = "Click",
Time = DateTime.Parse("March 12, 2010 3:00AM")
},
new
{
Type = "Click",
Time = DateTime.Parse("March 12, 2010 3:12AM")
},
new
{
Type = "Open",
Time = DateTime.Parse("March 12, 2010 3:34AM")
},
new
{
Type = "Click",
Time = DateTime.Parse("March 12, 2010 4:12AM")
},
new
{
Type = "Click",
Time = DateTime.Parse("March 13, 2010 1:12PM")
}
};
var result = from s in poo
group s by new {s.Type, s.Time.Hour} into p
select new
{
p.Key.Type,
p.Key.Hour,
Count = p.Count()
};
foreach (var s in result)
{
Console.WriteLine(s.Count + " - " + s.Type + " - " + s.Hour);
}
Output:
2 - Click - 3
1 - Open - 3
1 - Click - 4
1 - Click - 13
That what you're looking for?
you can do like this using lambda
var data = new[]
{
new { Actn = "click", tm = "3/12/2010 3:00AM" },
new { Actn = "click" , tm = "3/12/2010 3:12AM"},
new { Actn = "open" , tm = "3/12/2010 3:34AM"},
new { Actn = "click" , tm = "3/12/2010 4:12AM"},
new { Actn = "click" , tm = "3/13/2010 1:12PM"}
};
var groupby = data.GroupBy(r => new { Convert.ToDateTime(r.tm).Hour, r.Actn });
foreach (var group in groupby)
{
Console.WriteLine("{0} = {1}", group.Key, group.Count());
}
I solved it like so:
List<MySummary> summary =
entries.Select(i => new { i, dt = i.Date }).GroupBy(
@t => new { date = @t.dt.Date, time = @t.dt.Hour }, @t => @t.i).Select(
g =>
new MySummary()
{
Date = g.Key.date.AddHours(g.Key.time),
Opens = g.Count(x => x.Type == "Open"),
Clicks = g.Count(x => x.Type == "Click")
}).ToList();
Please let me know if you guys think this is incorrect..but from the looks of it..it gets the right data.
Model:
public class MySummary
{
public DateTime Date { get; set; }
public int Opens { get; set; }
public int Clicks { get; set; }
}
精彩评论