LINQ&按日期范围对数据进行分组 [英] LINQ & Grouping data by a date range

问题描述

说我有以下数据集，我希望根据匹配描述的日期是否在几秒钟之内对描述"列进行分组.

Say I have the below set of data where I wish to group the Description column based on whether the dates for matching Descriptions are within a few seconds of each other.

我要查找的输出是该组的描述和最小日期.描述可能相同，但日期可能不同，在这种情况下，我需要两个输出行.

The output I'm looking for is the Description and the minimum Date for that group. It is possible that the Description could be the same but the dates might be days different, in this case I would want two outputted rows.

在上述情况下，请查看"TEST s"说明，在该处我想要两个输出的分组行

In the case above take a look at the Description "TEST s" where I would want two outputted grouped rows

测试s 2014-12-04 16:27:44.903

TEST s 2014-12-04 16:27:44.903

测试s 2014-12-04 17:21:21.233

TEST s 2014-12-04 17:21:21.233

使用Linq可以吗?

推荐答案

尝试一下:

var q = from item in lstMyTable
        group item by item.ItemDate.ToString("MM/dd/yyyy HH:mm") into ItemGroup
        select new
        {
            Description = ItemGroup.First().Description,
            Date = ItemGroup.OrderBy(x => x.ItemDate).First().ItemDate
        };

以上linq查询按日期+小时+分钟进行分组，而忽略秒.应用于 ItemGroup 的 OrderBy 可确保我们获得最小日期，如操作说明中所述.

The above linq query groups by date + hour + minutes, ignoring seconds. The OrderBy applied to ItemGroup ensures that we get the mininum date, as described in the OP.

使用此输入:

List<MyTable> lstMyTable = new List<MyTable>()
{
  new MyTable() { Description = "TEST s", ItemDate = new DateTime(2014, 12, 4, 16, 27, 11) },
  new MyTable() { Description = "TEST s", ItemDate = new DateTime(2014, 12, 4, 16, 27, 12) },
  new MyTable() { Description = "TEST s", ItemDate = new DateTime(2014, 12, 4, 16, 27, 13) },

  new MyTable() { Description = "TEST s", ItemDate = new DateTime(2014, 12, 4, 17, 21, 11) },
  new MyTable() { Description = "TEST s", ItemDate = new DateTime(2014, 12, 4, 17, 21, 12) },
  new MyTable() { Description = "TEST s", ItemDate = new DateTime(2014, 12, 4, 17, 21, 13) }
};

您得到以下结果:

[0] = { Description = "TEST s", Date = {4/12/2014 4:27:11 pm} }
[1] = { Description = "TEST s", Date = {4/12/2014 5:21:11 pm} }

如果您也想按描述进行分组，则只需用如下代码替换 group by 子句:

If you want to also group by description then simply substitue the group by clause with sth like this:

group item by new 
{ 
   a = item.ItemDate.ToString("MM/dd/yyyy HH:mm") ,
   b = item.Description 
} into ItemGroup

最后，通过将 Datetime 字段转换为 Ticks ，您可以弄乱各个记录之间所需的时间距离，例如

Finally, by converting the Datetime field to Ticks, you can fiddle with the required time distance between separate records, e.g.

group item by new 
{ 
    a = item.ItemDate.Ticks.ToString().Substring(0, item.ItemDate.Ticks.ToString().Length - 10), // instead of .ToString("MM/dd/yyyy HH:mm") ,
    b = item.Description 
} into ItemGroup

您可以通过更改从 item.ItemDate.Ticks 减去的位数来代替，例如10等于9或8，等等.

You can play around by varying the number of digits subtracted from item.ItemDate.Ticks, substituting e.g. 10 with 9 or 8, etc.

这篇关于LINQ&按日期范围对数据进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！