使用linq按日期分组查询填充缺失的日期

Question

我有一个Linq查询，基本上是通过按照年、月、日进行分组来计算特定日期创建了多少条目。问题在于，由于某些日期没有任何条目，因此我需要用0计数的条目填充这些缺失的“日历天”。

我猜想这可能可以通过Union或其他方式完成，或者甚至可以使用一些简单的for循环来处理查询后的记录。

以下是查询代码：

from l in context.LoginToken
 where l.CreatedOn >= start && l.CreatedOn <= finish
 group l by
 new{l.CreatedOn.Year, l.CreatedOn.Month, l.CreatedOn.Day} into groups
 orderby groups.Key.Year , groups.Key.Month , groups.Key.Day
     select new StatsDateWithCount {
                                    Count = groups.Count(),
                                     Year =  groups.Key.Year,
                                    Month = groups.Key.Month,
                                      Day = groups.Key.Day
                                                                  }));

如果我有数据为2009年12月1日至12月4日，如下（简化）：

12/1/2009 20
12/2/2009 15
12/4/2009 16

我希望通过代码添加一个日期为12/3/2009 0的条目。

我知道通常应该在数据库中使用非规范化表来完成这个操作，您可以将其与数据一起填充或者连接到日历表，但是我的问题是如何通过代码完成这个操作？
是否可以使用Linq完成？是否应该使用Linq完成？

- Greg Roberts

---

可能是LINQ按日期分组 - 包括空日期而不使用join的重复项。 - Robert Synoradzki

Answer 1

我今天刚做了这件事。我从数据库中收集了完整的数据，然后生成了一个“示例空”表格。最后，我将空表格与真实数据进行了外连接，并使用DefaultIfEmpty()结构来处理当数据库中缺少行时如何填充默认值。

以下是我的代码：

int days = 30;

// Gather the data we have in the database, which will be incomplete for the graph (i.e. missing dates/subsystems).
var dataQuery =
    from tr in SourceDataTable
    where (DateTime.UtcNow - tr.CreatedTime).Days < 30
    group tr by new { tr.CreatedTime.Date, tr.Subsystem } into g
    orderby g.Key.Date ascending, g.Key.SubSystem ascending
    select new MyResults()
    {
        Date = g.Key.Date, 
        SubSystem = g.Key.SubSystem,
        Count = g.Count()
    };

// Generate the list of subsystems we want.
var subsystems = new[] { SubSystem.Foo, SubSystem.Bar }.AsQueryable();

// Generate the list of Dates we want.
var datetimes = new List<DateTime>();
for (int i = 0; i < days; i++)
{
    datetimes.Add(DateTime.UtcNow.AddDays(-i).Date);
}

// Generate the empty table, which is the shape of the output we want but without counts.
var emptyTableQuery =
    from dt in datetimes
    from subsys in subsystems
    select new MyResults()
    {
        Date = dt.Date, 
        SubSystem = subsys,
        Count = 0
    };

// Perform an outer join of the empty table with the real data and use the magic DefaultIfEmpty
// to handle the "there's no data from the database case".
var finalQuery =
    from e in emptyTableQuery
    join realData in dataQuery on 
        new { e.Date, e.SubSystem } equals 
        new { realData.Date, realData.SubSystem } into g
    from realDataJoin in g.DefaultIfEmpty()
    select new MyResults()
    {
        Date = e.Date,
        SubSystem = e.SubSystem,
        Count = realDataJoin == null ? 0 : realDataJoin.Count
    };

return finalQuery.OrderBy(x => x.Date).AsEnumerable();

- user178581

---

这与我最终所做的非常相似，但是在结果上进行了联合操作，而不是执行连接操作。 - Greg Roberts

Answer 2

今日免费次数已满, 请开通会员/明日再来

var orders = db.Orders
             .GroupBy(o => o.OrderDate)
             .Select(o => new 
             {
                OrderDate = o.Key,
                OrderCount = o.Count(),
                Sales = o.Sum(i => i.SubTotal)
             }
             .OrderBy(o => o.OrderDate);

为了使我的函数正常工作，请注意这个列表必须按日期排序。如果有一天没有销售，列表中就会出现空洞。

现在让我们来看一下将填充默认值（匿名类型实例）的函数。

    private static IEnumerable<T> FillInEmptyDates<T>(IEnumerable<DateTime> allDates, IEnumerable<T> sourceData, Func<T, DateTime> dateSelector, Func<DateTime, T> defaultItemFactory)
    {
        // iterate through the source collection
        var iterator = sourceData.GetEnumerator();
        iterator.MoveNext();

        // for each date in the desired list
        foreach (var desiredDate in allDates)
        {
            // check if the current item exists and is the 'desired' date
            if (iterator.Current != null && 
                dateSelector(iterator.Current) == desiredDate)
            {
                // if so then return it and move to the next item
                yield return iterator.Current;
                iterator.MoveNext();

                // if source data is now exhausted then continue
                if (iterator.Current == null)
                {
                    continue;
                }

                // ensure next item is not a duplicate 
                if (dateSelector(iterator.Current) == desiredDate)
                {
                    throw new Exception("More than one item found in source collection with date " + desiredDate);
                }
            }
            else
            {
                // if the current 'desired' item doesn't exist then
                // create a dummy item using the provided factory
                yield return defaultItemFactory(desiredDate);
            }
        }
    }

使用方法如下：

// first you must determine your desired list of dates which must be in order
// determine this however you want    
var desiredDates = ....; 

// fill in any holes
var ordersByDate = FillInEmptyDates(desiredDates, 

                               // Source list (with holes)
                               orders, 

                               // How do we get a date from an order
                               (order) => order.OrderDate,

                               // How do we create an 'empty' item 
                               (date) => new 
                               {
                                     OrderDate = date,
                                     OrderCount = 0,
                                     Sales = 0
                               });

• 必须确保所需日期列表中没有重复项
• desiredDates和sourceData都必须按顺序排列
• 由于该方法是通用的，如果您使用的是匿名类型，则编译器会自动告诉您，如果您的“默认”项与常规项的“形状”不同。
• 目前，我在sourceData中包含了检查重复项的代码，但在desiredDates中没有这样的检查。
• 如果您想确保列表按日期排序，则需要添加额外的代码。

- Simon_Weaver

---

我认为这是一个非常特定的“业务”场景，试图将其挤入“优雅”的linq结构是适得其反的 - 但这是我能想到的第二个最优雅的解决方案。 - Simon_Weaver

Answer 3

本质上，我在这里所做的是创建了一个相同类型的列表，其中包含范围内的所有日期和计数为0的值。然后将原始查询结果与此列表合并。最大的障碍只是创建自定义的IEqualityComparer。更多细节请参见：点击这里

- Greg Roberts

Answer 4

您可以生成从“start”开始到“finish”结束的日期列表，然后逐步检查每个日期的计数数量。

- Gacek

---

这样做可以，但我想看一下如何使用一些 Linq 构造，比如 Union 运算符来完成它。 - Greg Roberts