开发者

LINQ group by Hour and Type (click/open ratio)

I have a table with the following fields:

  1. Action => Click/Open
  2. DateTime => Date and time action occured

example:

Action | DateTime


  1. click | 3/12/2010 3:00AM
  2. click | 3/12/2010 3:12AM
  3. open | 3/12/2010 3:34AM
  4. click | 3/12/2010 4:12AM
  5. 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; }
}
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜