DateTime Linq Join中的以前的非空值

Question

我正在尝试加入两个文件，一个在 daily 粒度上，一个在 ain ainthly granularity。

我目前有一个：

var combinedTrends = from daily in _dailyDict
                     from weekly in _weeklyDict
                     where DateTime.Parse(daily.Key) >= DateTime.Parse(weekly.Key.Substring(0, 10)) && DateTime.Parse(daily.Key) <= DateTime.Parse(weekly.Key.Substring(13, 10))
                     select new GoogleTrends
                     {
                         Date = DateTime.Parse(daily.Key),
                         WeekStart = DateTime.Parse(weekly.Key.Substring(0, 10)),
                         WeekEnd = DateTime.Parse(weekly.Key.Substring(13, 10)),
                         DailyIndex = daily.Value,
                         WeeklyIndex = weekly.Value
                     };

问题：我正在失去每周不存在的每日行。

输入：

1/1/2014    91
1/2/2014    82
1/3/2014    89
1/4/2014    100
1/5/2014    87
1/6/2014    64
1/7/2014    64
1/8/2014    64
1/9/2014    67
etc....
2014-01-05 - 2014-01-11 37
2014-01-12 - 2014-01-18 34
2014-01-19 - 2014-01-25 34
etc.....

预期输出

Date        WeekStart   WeekEnd     D   W
01-01-2014  (empty)     (empty)     91  (empty)
01-02-2014  (empty)     (empty)     82  (empty)
01-03-2014  (empty)     (empty)     89  (empty)
01-04-2014  (empty)     (empty)     100 (empty)
01-05-2014  01-05-2014  01-11-2014  87  37
01-06-2014  01-05-2014  01-11-2014  64  37
01-07-2014  01-05-2014  01-11-2014  64  37
01-08-2014  01-05-2014  01-11-2014  64  37
01-09-2014  01-05-2014  01-11-2014  67  37
etc....

Answer 1

好的，所以这不像简单的JOIN那样容易，并且可以更快地制作，但是这是我提出的解决方案：

解决方案1：

Calendar cal = DateTimeFormatInfo.CurrentInfo.Calendar;
var combinedTrends = from daily in _dailyDict
                        join weeklySub in _weeklyDict on cal.GetWeekOfYear(DateTime.Parse(daily.Key), CalendarWeekRule.FirstDay, DayOfWeek.Sunday) equals cal.GetWeekOfYear(DateTime.Parse(weeklySub.Key.Substring(0, 10)), CalendarWeekRule.FirstDay, DayOfWeek.Sunday) into weeklyGroup
                        from weekly in weeklyGroup.DefaultIfEmpty(new KeyValuePair<string, int>(DateTime.Parse(daily.Key).ToString("yyyy-MM-dd") + " - " + DateTime.Parse(daily.Key).ToString("yyyy-MM-dd"), 0))
                        select new GoogleTrends
                        {
                            Date = DateTime.Parse(daily.Key),
                            WeekStart = DateTime.Parse(weekly.Key.Substring(0, 10)),
                            WeekEnd = DateTime.Parse(weekly.Key.Substring(13, 10)),
                            DailyIndex = daily.Value,
                            WeeklyIndex = weekly.Value
                        };

本质上，我们采用您的原始查询，并添加以下内容：

join weeklySub in _weeklyDict on cal.GetWeekOfYear(DateTime.Parse(daily.Key), CalendarWeekRule.FirstDay, DayOfWeek.Sunday) equals cal.GetWeekOfYear(DateTime.Parse(weeklySub.Key.Substring(0, 10)), CalendarWeekRule.FirstDay, DayOfWeek.Sunday) into weeklyGroup
from weekly in weeklyGroup.DefaultIfEmpty(new KeyValuePair<string, int>(DateTime.Parse(daily.Key).ToString("yyyy-MM-dd") + " - " + DateTime.Parse(daily.Key).ToString("yyyy-MM-dd"), 0))

这样做的是告诉Linq检查daily项目的周数是否与weekly项目的周数匹配，如果是的，则返回。否则，它返回与每日记录的日期与本周的开始和结束，并带有0值。

解决方案2：

现在，我们可以添加辅助方法，该方法将获得一周中的第一天和最后一天，将直接程序字符串发送到weekly项目，并为其修改查询：

// We use this function because .NET does not implicitly consider the first and last weeks of the year the same.
public static int GetWeekOfYear(int weekNumber)
{
    if (weekNumber > 52)
        return 1;
    return weekNumber;
}
// This will get the week-range in a string of format "yyyy-MM-dd - yyyy-MM-dd".
public static string GetWeekRange(DateTime date, DayOfWeek firstDayOfWeek)
{
    int numberOfDayOfWeek = DayOfWeekToNumber(date.DayOfWeek, firstDayOfWeek);
    return date.AddDays(0 - numberOfDayOfWeek).ToString("yyyy-MM-dd") + " - " + date.AddDays(6 - numberOfDayOfWeek).ToString("yyyy-MM-dd");
}
// This converts the DayOfWeek to a number to allow us to easily determine how many days to add/subtract for the first/last days of the week.
public static int DayOfWeekToNumber(DayOfWeek dayOfWeek, DayOfWeek firstDayOfWeek)
{
    int[] r = new int[2];
    r[0] = 0;
    r[1] = 0;
    r[0] = DayOfWeekValue(firstDayOfWeek);
    r[1] = DayOfWeekValue(dayOfWeek);
    int result = r[1] - r[0];
    if (result < 0)
        result += 7;
    return result;
}
// This converts the DayOfWeek enum into a numerical value, Sunday being 0 and up from there.
public static int DayOfWeekValue(DayOfWeek dayOfWeek)
{
    switch (dayOfWeek)
    {
        case DayOfWeek.Sunday:
            return 0;
        case DayOfWeek.Monday:
            return 1;
        case DayOfWeek.Tuesday:
            return 2;
        case DayOfWeek.Wednesday:
            return 3;
        case DayOfWeek.Thursday:
            return 4;
        case DayOfWeek.Friday:
            return 5;
        case DayOfWeek.Saturday:
            return 6;
    }
    return -1;
}

然后我们完成查询：

Calendar cal = DateTimeFormatInfo.CurrentInfo.Calendar;
DayOfWeek firstDayOfWeek = DayOfWeek.Sunday;
var combinedTrends = from daily in _dailyDict
                        join weeklySub in _weeklyDict on GetWeekOfYear(cal.GetWeekOfYear(DateTime.Parse(daily.Key), CalendarWeekRule.FirstDay, firstDayOfWeek)) equals GetWeekOfYear(cal.GetWeekOfYear(DateTime.Parse(weeklySub.Key.Substring(0, 10)), CalendarWeekRule.FirstDay, firstDayOfWeek)) into weeklyGroup
                        from weekly in weeklyGroup.DefaultIfEmpty(new KeyValuePair<string, int>(GetWeekRange(DateTime.Parse(daily.Key), firstDayOfWeek), 0))
                        select new GoogleTrends
                        {
                            Date = DateTime.Parse(daily.Key),
                            WeekStart = DateTime.Parse(weekly.Key.Substring(0, 10)),
                            WeekEnd = DateTime.Parse(weekly.Key.Substring(13, 10)),
                            DailyIndex = daily.Value,
                            WeeklyIndex = weekly.Value
                        };

就个人而言，您应该采用哪种方法，我认为我提到的后者可能是最好的。

顺便说一句，该解决方案的输出为：

Date       WeekStart  WeekEnd    D   W
01-01-2014 12-29-2013 01-04-2014 91  0
01-02-2014 12-29-2013 01-04-2014 82  0
01-03-2014 12-29-2013 01-04-2014 89  0
01-04-2014 12-29-2013 01-04-2014 100 0
01-05-2014 01-05-2014 01-11-2014 87  37
01-06-2014 01-05-2014 01-11-2014 64  37
01-07-2014 01-05-2014 01-11-2014 64  37
01-08-2014 01-05-2014 01-11-2014 64  37
01-09-2014 01-05-2014 01-11-2014 67  37

注意：如评论中所示，GetWeekOfYear(int)功能之所以存在，是因为.NET将开始部分一周作为一年中的第一个（1）一周，但不会以第一个为单位结束部分一周，而是作为第一个星期，而是每周53。在这种情况下，此功能返回1，以允许正确加入两个部分。

另外，这实际上是一个有趣的问题。

测试环境：

使用以下代码和评论来测试这些解决方案：

Dictionary<string, int> _dailyDict = new Dictionary<string, int>();
_dailyDict.Add("12/31/2013", 91);
_dailyDict.Add("1/1/2014", 91);
_dailyDict.Add("1/2/2014", 82);
_dailyDict.Add("1/3/2014", 89);
_dailyDict.Add("1/4/2014", 100);
_dailyDict.Add("1/5/2014", 87);
_dailyDict.Add("1/6/2014", 64);
_dailyDict.Add("1/7/2014", 64);
_dailyDict.Add("1/8/2014", 64);
_dailyDict.Add("1/9/2014", 67);
Dictionary<string, int> _weeklyDict = new Dictionary<string, int>();
// This line was removed and added to ensure that weeks that didn't exist would still return daily's.
//_weeklyDict.Add("2013-12-29 - 2014-01-04", 1);
_weeklyDict.Add("2014-01-05 - 2014-01-11", 37);
_weeklyDict.Add("2014-01-12 - 2014-01-18", 34);
_weeklyDict.Add("2014-01-19 - 2014-01-25", 34);

使用以下代码生成输出：

foreach (var trend in combinedTrends)
{
    Console.WriteLine(trend.Date.ToString("MM-dd-yyyy") + "t" + trend.WeekStart.ToString("MM-dd-yyyy") + "t" + trend.WeekEnd.ToString("MM-dd-yyyy") + "t" + trend.DailyIndex.ToString() + "t" + trend.WeeklyIndex.ToString());
}

参考：

https://msdn.microsoft.com/en-us/library/bb311040.aspx

获取给定日期的正确周数

https://msdn.microsoft.com/en-us/library/system.globalization.calendar.calendar.getweekofyear.aspx

Answer 2

尝试使用JOIN

var combinedTrends = from daily in _dailyDict
                                 join weekly in _weeklyDict
                                 on DateTime.Parse(daily.Key) equals DateTime.Parse(weekly.Key.Substring(0, 10))
                                 select new GoogleTrends
                                 {
                                     Date = DateTime.Parse(daily.Key),
                                     WeekStart = DateTime.Parse(weekly.Key.Substring(0, 10)),
                                     WeekEnd = DateTime.Parse(weekly.Key.Substring(13, 10)),
                                     DailyIndex = daily.Value,
                                     WeeklyIndex = weekly.Value
                                 };