Split last 2 weeks of data into current and previo

问题:

The trendData is a list of EventLogs using the current UTC date as the end date and a start date of the current UTC date -13 (the db stores dates as UTC)

DateTime dUtcEnd = DateTime.UtcNow.Date;
DateTime dUtcStart = dUtcEnd.AddDays(-13);

var trendData = (from e in db.EventLogs
                where e.EventDateTime >= dUtcStart && e.EventDateTime <= dUtcEnd
                      && e.EventType == 'some event'
                select e).ToList();

Is there a way to Take and Skip in week increments in order to populate array values for the current and previous weeks?

Something along these lines? Somehow I imagine that the Take and Skip need to base it on the EventDateTime in order to determine what current and previous weeks are considered.

var currentTrend = trendData.Take(7).Reverse();
var previousTrend = trendData.Skip(7).Take(7).Reverse();

---

In order to give some further context into what is trying to be accomplished, below is an idea of how the Highcharts arrays are going to be populated.

The results returned would contain 3 arrays.

WeeklyTrend results = new WeeklyTrend();
results.Categories = new string[7];
results.ThisWeek = new int[7];
results.LastWeek = new int[7];

Populate the Categories with the Today value decremented by one day each time for the current week.

int dayCounter = 6;
for (int i = 0; i < 7; i++)
{
    results.Categories[i] = dUtcEnd.AddDays(-dayCounter).ToShortDateString();
    dayCounter--;
}

Look in the Categories array in order to find the array value where the EventDateTime matches the date in the array.

foreach (var cTrend in currentTrend)
{
    var dayOfWeek = Array.FindIndex(results.Categories, c => c.Contains(cTrend.EventDateTime.ToShortDateString()));

    results.ThisWeek[dayOfWeek]++;
}

Look in the Categories array in order to find the array value where the EventDateTime matches the date in the array with days added to match the current week..

foreach (var pTrend in previousTrend)
{
    var dayOfWeek = Array.FindIndex(results.Categories, c => c.Contains(pTrend.EventDateTime.AddDays(6).ToShortDateString()));

    results.LastWeek[dayOfWeek]++;
}

The end goal is to populate a Highcharts trend chart that shows data from the current week compared to the previous week.

回答1:

my inclination is to use a GroupBy inside your linq. with this in mind, I did a quick google and came up with a few here on SO. One that caught my eye was this one:

C# Linq or Lambda expression Group by Week IEnumerable list with date field

basically, to paraphrase it:

DateTime dUtcEnd = DateTime.UtcNow.Date;
DateTime dUtcStart = dUtcEnd.AddDays(-13);

var eventLogs = new List<EventLogs>();

var weekTrendGroups = eventLogs
    .Where(x => x.EventDateTime >= dUtcStart 
                && x.EventDateTime <= dUtcEnd
                && x.EventType == "some event")
    .Select(p => new
    {
        Event = p,
        Year = p.EventDateTime.Year,
        Week = CultureInfo.InvariantCulture.Calendar.GetWeekOfYear
                (p.EventDateTime, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday)
    })
    .GroupBy(x => new { x.Year, x.Week })
    .Select((g, i) => new
    {
        WeekGroup = g,
        WeekNum = i + 1,
        Year = g.Key.Year,
        CalendarWeek = g.Key.Week
    });

foreach (var eventLogGroup in weekTrendGroups)
{
    Console.WriteLine("Week " + eventLogGroup.WeekNum);
    foreach (var proj in eventLogGroup.WeekGroup)
        Console.WriteLine("{0} {1} {2}",
            proj.Event.EventType,
            proj.Event.EventDateTime.ToString("d"),
            proj.Event.Id);
}

[edit] - fixed function to match your data attributes.

so, here the data is neatly chopped by week on the boundary of DayOfWeek.Monday in this case. You can of course change to suit. You'd then be able to take the WeekNum and party on the groups within that.

This looks like a neat solution as you can then of course apply your own predicates to it to slice it as you see fit.

回答2:

"Somehow I imagine that the Take and Skip need to base it on the EventDateTime in order to determine what current and previous weeks are considered."

Are you simply looking for this?

var previousTrend = trendData.OrderBy(e => e.EventDateTime).Take(7).Reverse();
var currentTrend = trendData.OrderBy(e => e.EventDateTime).Skip(7).Take(7).Reverse();