MongoDB每日分组聚合

Question

MongoDB每日分组聚合

mongodbmongodb-queryaggregation-framework

66

我有一些在Mongo中的文档看起来像这样:

{
  _id : ObjectId("..."),
  "make" : "Nissan",
  ..
},
{
  _id : ObjectId("..."),
  "make" : "Nissan",
  "saleDate" :  ISODate("2013-04-10T12:39:50.676Z"),
  ..
}

理想情况下，我希望能够按照制造商计算每天销售的车辆数量。然后，我希望查看今天或类似于今天到过去七天的窗口。

我使用了一些丑陋的代码来实现每日视图。

db.inventory.aggregate(
  { $match : { "saleDate" : { $gte: ISODate("2013-04-10T00:00:00.000Z"), $lt: ISODate("2013-04-11T00:00:00.000Z")  } } } ,
  { $group : { _id : { make : "$make", saleDayOfMonth : { $dayOfMonth : "$saleDate" } }, cnt : { $sum : 1 } } }
)

这随之产生了结果。

{
  "result" : [
    {
      "_id" : {
        "make" : "Nissan",
        "saleDayOfMonth" : 10
      },
      "cnt" : 2
    },
    {
      "_id" : {
        "make" : "Toyota",
        "saleDayOfMonth" : 10
      },
      "cnt" : 4
    },
  ],
  "ok" : 1
}

所以这样做是可以的，但我更喜欢不必更改查询中的两个日期时间值。然后，如上所述，我想能够运行此查询(再次运行时无需每次修改)并查看过去一周按天分组的相同结果。

哦，这是我用于查询的示例数据。

db.inventory.save({"make" : "Nissan","saleDate" :  ISODate("2013-04-10T12:39:50.676Z")});
db.inventory.save({"make" : "Nissan"});
db.inventory.save({"make" : "Nissan","saleDate" :  ISODate("2013-04-10T11:39:50.676Z")});
db.inventory.save({"make" : "Toyota","saleDate" :  ISODate("2013-04-09T11:39:50.676Z")});
db.inventory.save({"make" : "Toyota","saleDate" :  ISODate("2013-04-10T11:38:50.676Z")});
db.inventory.save({"make" : "Toyota","saleDate" :  ISODate("2013-04-10T11:37:50.676Z")});
db.inventory.save({"make" : "Toyota","saleDate" :  ISODate("2013-04-10T11:36:50.676Z")});
db.inventory.save({"make" : "Toyota","saleDate" :  ISODate("2013-04-10T11:35:50.676Z")});

提前致谢， Kevin

- Kevin

676Z的含义是什么？ - Aboozar Rajabi

仅仅是一个更新（来自2017年，哇这个问题太老了..），我已经更改了被接受的答案，因为Mongo框架已经发展到可以更轻松地解决这个问题。尽管如此，还是要感谢Asya的原始答案。@AboozarRajabi，“676Z”是ISO 8601时间格式的可选部分，在这种情况下，“676Z”代表2013-04-10T11:35:50.676Z的两个部分，第一个676是毫秒，而“Z”是表示UTC时区的快捷方式。 - Kevin

实际上，它已经发展得更加成熟了，截至 2017 年的 3.6 版本，您无需将日期转换为字符串即可完成此操作。更不用说我刚刚注意到我们都没有包括您问题的第二部分，即如何查看 "窗口，例如从今天到过去七天" - 过去七天，特别是包括没有销售的日子，与这个问题的基本部分有所不同。 - Asya Kamsky

我正在更新我的答案，基于3.6版本，并包括如何添加没有任何销售的日期（0销售）。 - Asya Kamsky

@Kevin，你真的应该重新考虑撤销接受的答案。 - Asya Kamsky

3个回答

62

更新：更新的答案基于3.6版本中的日期功能，并展示了如何在范围内包含没有销售量的日期（这在任何原始答案中都没有提到，包括我的答案）。

样本数据：

db.inventory.find()
{ "_id" : ObjectId("5aca30eefa1585de22d7095f"), "make" : "Nissan", "saleDate" : ISODate("2013-04-10T12:39:50.676Z") }
{ "_id" : ObjectId("5aca30eefa1585de22d70960"), "make" : "Nissan" }
{ "_id" : ObjectId("5aca30effa1585de22d70961"), "make" : "Nissan", "saleDate" : ISODate("2013-04-10T11:39:50.676Z") }
{ "_id" : ObjectId("5aca30effa1585de22d70962"), "make" : "Toyota", "saleDate" : ISODate("2013-04-09T11:39:50.676Z") }
{ "_id" : ObjectId("5aca30effa1585de22d70963"), "make" : "Toyota", "saleDate" : ISODate("2013-04-10T11:38:50.676Z") }
{ "_id" : ObjectId("5aca30effa1585de22d70964"), "make" : "Toyota", "saleDate" : ISODate("2013-04-10T11:37:50.676Z") }
{ "_id" : ObjectId("5aca30effa1585de22d70965"), "make" : "Toyota", "saleDate" : ISODate("2013-04-10T11:36:50.676Z") }
{ "_id" : ObjectId("5aca30effa1585de22d70966"), "make" : "Toyota", "saleDate" : ISODate("2013-04-10T11:35:50.676Z") }
{ "_id" : ObjectId("5aca30f9fa1585de22d70967"), "make" : "Toyota", "saleDate" : ISODate("2013-04-11T11:35:50.676Z") }
{ "_id" : ObjectId("5aca30fffa1585de22d70968"), "make" : "Toyota", "saleDate" : ISODate("2013-04-13T11:35:50.676Z") }
{ "_id" : ObjectId("5aca3921fa1585de22d70969"), "make" : "Honda", "saleDate" : ISODate("2013-04-13T00:00:00Z") }

将startDate和endDate定义为变量，并在聚合中使用它们：

startDate = ISODate("2013-04-08T00:00:00Z");
endDate = ISODate("2013-04-15T00:00:00Z");

db.inventory.aggregate([
  { $match : { "saleDate" : { $gte: startDate, $lt: endDate} } },
  {$addFields:{
     saleDate:{$dateFromParts:{
                  year:{$year:"$saleDate"},
                  month:{$month:"$saleDate"},
                  day:{$dayOfMonth:"$saleDate"}
     }},
     dateRange:{$map:{
        input:{$range:[0, {$subtract:[endDate,startDate]}, 1000*60*60*24]},
        in:{$add:[startDate, "$$this"]}
     }}
  }},
  {$unwind:"$dateRange"},
  {$group:{
     _id:"$dateRange", 
     sales:{$push:{$cond:[
                {$eq:["$dateRange","$saleDate"]},
                {make:"$make",count:1},
                {count:0}
     ]}}
  }},
  {$sort:{_id:1}},
  {$project:{
     _id:0,
     saleDate:"$_id",
     totalSold:{$sum:"$sales.count"},
     byBrand:{$arrayToObject:{$reduce:{
        input: {$filter:{input:"$sales",cond:"$$this.count"}},
        initialValue: {$map:{input:{$setUnion:["$sales.make"]}, in:{k:"$$this",v:0}}}, 
        in:{$let:{
           vars:{t:"$$this",v:"$$value"},
           in:{$map:{
              input:"$$v",
              in:{
                 k:"$$this.k",
                 v:{$cond:[
                     {$eq:["$$this.k","$$t.make"]},
                     {$add:["$$this.v","$$t.count"]},
                     "$$this.v"
                 ]}
              }
           }}
        }}
     }}}
  }}
])

在样本数据上，这会给出以下结果：

{ "saleDate" : ISODate("2013-04-08T00:00:00Z"), "totalSold" : 0, "byBrand" : {  } }
{ "saleDate" : ISODate("2013-04-09T00:00:00Z"), "totalSold" : 1, "byBrand" : { "Toyota" : 1 } }
{ "saleDate" : ISODate("2013-04-10T00:00:00Z"), "totalSold" : 6, "byBrand" : { "Nissan" : 2, "Toyota" : 4 } }
{ "saleDate" : ISODate("2013-04-11T00:00:00Z"), "totalSold" : 1, "byBrand" : { "Toyota" : 1 } }
{ "saleDate" : ISODate("2013-04-12T00:00:00Z"), "totalSold" : 0, "byBrand" : {  } }
{ "saleDate" : ISODate("2013-04-13T00:00:00Z"), "totalSold" : 2, "byBrand" : { "Honda" : 1, "Toyota" : 1 } }
{ "saleDate" : ISODate("2013-04-14T00:00:00Z"), "totalSold" : 0, "byBrand" : {  } }

这个聚合也可以使用两个$group阶段和一个简单的$project来完成，而不是使用一个复杂的$project和一个$group。下面是示例：

db.inventory.aggregate([
   {$match : { "saleDate" : { $gte: startDate, $lt: endDate} } },
   {$addFields:{saleDate:{$dateFromParts:{year:{$year:"$saleDate"}, month:{$month:"$saleDate"}, day:{$dayOfMonth : "$saleDate" }}},dateRange:{$map:{input:{$range:[0, {$subtract:[endDate,startDate]}, 1000*60*60*24]},in:{$add:[startDate, "$$this"]}}}}},
   {$unwind:"$dateRange"},
   {$group:{
      _id:{date:"$dateRange",make:"$make"},
      count:{$sum:{$cond:[{$eq:["$dateRange","$saleDate"]},1,0]}}
   }},
   {$group:{
      _id:"$_id.date",
      total:{$sum:"$count"},
      byBrand:{$push:{k:"$_id.make",v:{$sum:"$count"}}}
   }},
   {$sort:{_id:1}},
   {$project:{
      _id:0,
      saleDate:"$_id",
      totalSold:"$total",
      byBrand:{$arrayToObject:{$filter:{input:"$byBrand",cond:"$$this.v"}}}
   }}
])

相同的结果：

{ "saleDate" : ISODate("2013-04-08T00:00:00Z"), "totalSold" : 0, "byBrand" : { "Honda" : 0, "Toyota" : 0, "Nissan" : 0 } }
{ "saleDate" : ISODate("2013-04-09T00:00:00Z"), "totalSold" : 1, "byBrand" : { "Honda" : 0, "Nissan" : 0, "Toyota" : 1 } }
{ "saleDate" : ISODate("2013-04-10T00:00:00Z"), "totalSold" : 6, "byBrand" : { "Honda" : 0, "Toyota" : 4, "Nissan" : 2 } }
{ "saleDate" : ISODate("2013-04-11T00:00:00Z"), "totalSold" : 1, "byBrand" : { "Toyota" : 1, "Honda" : 0, "Nissan" : 0 } }
{ "saleDate" : ISODate("2013-04-12T00:00:00Z"), "totalSold" : 0, "byBrand" : { "Toyota" : 0, "Nissan" : 0, "Honda" : 0 } }
{ "saleDate" : ISODate("2013-04-13T00:00:00Z"), "totalSold" : 2, "byBrand" : { "Honda" : 1, "Toyota" : 1, "Nissan" : 0 } }
{ "saleDate" : ISODate("2013-04-14T00:00:00Z"), "totalSold" : 0, "byBrand" : { "Toyota" : 0, "Honda" : 0, "Nissan" : 0 } }

基于2.6的原始答案：

你可能想看看我的博客文章，了解如何在聚合框架中处理各种日期操作，请点击此处。

你可以使用$project阶段将日期截断为每日分辨率，然后对整个数据集（或其中一部分）运行聚合，并按日期和制造商进行聚合。

以你的示例数据为例，假设你想知道今年按制造商和日期出售的车辆数量：

match={"$match" : {
               "saleDate" : { "$gt" : new Date(2013,0,1) }
      }
};

proj1={"$project" : {
        "_id" : 0,
        "saleDate" : 1,
        "make" : 1,
        "h" : {
            "$hour" : "$saleDate"
        },
        "m" : {
            "$minute" : "$saleDate"
        },
        "s" : {
            "$second" : "$saleDate"
        },
        "ml" : {
            "$millisecond" : "$saleDate"
        }
    }
};

proj2={"$project" : {
        "_id" : 0,
        "make" : 1,
        "saleDate" : {
            "$subtract" : [
                "$saleDate",
                {
                    "$add" : [
                        "$ml",
                        {
                            "$multiply" : [
                                "$s",
                                1000
                            ]
                        },
                        {
                            "$multiply" : [
                                "$m",
                                60,
                                1000
                            ]
                        },
                        {
                            "$multiply" : [
                                "$h",
                                60,
                                60,
                                1000
                            ]
                        }
                    ]
                }
            ]
        }
    }
};

group={"$group" : {
        "_id" : {
            "m" : "$make",
            "d" : "$saleDate"
        },
        "count" : {
            "$sum" : 1
        }
    }
};

现在运行聚合操作会给您：

db.inventory.aggregate(match, proj1, proj2, group)
{
    "result" : [
        {
            "_id" : {
                "m" : "Toyota",
                "d" : ISODate("2013-04-10T00:00:00Z")
            },
            "count" : 4
        },
        {
            "_id" : {
                "m" : "Toyota",
                "d" : ISODate("2013-04-09T00:00:00Z")
            },
            "count" : 1
        },
        {
            "_id" : {
                "m" : "Nissan",
                "d" : ISODate("2013-04-10T00:00:00Z")
            },
            "count" : 2
        }
    ],
    "ok" : 1
}

你可以添加另一个{$project}阶段来美化输出，也可以添加一个{$sort}步骤，但基本上对于每个日期，对于每个制造商，你都会得到销售数量的计数。

- Asya Kamsky

1

Asya，谢谢你，这正是我在寻找的。我也曾经为函数的拆分而苦苦挣扎，所以这真的很有帮助。再次感谢你。 - Kevin

@SrikarJammi 感谢您的问题 - 我最初错过了问题的那部分，看起来其他答案也是如此。我更新了我的答案，包括为没有销售的日期生成0值。 - Asya Kamsky

4

$range的结束值将超过32位整数的范围，因此无法正常工作。 - Augie Gardner

首先，只有当您的日期范围超过596天时，int32才会溢出；其次，我不确定这是否相关，因为在bson中，日期表示为长整型（int64）。 - Asya Kamsky

请注意，范围不是从0到结束日期。它是从0到delta（结束日期减去开始日期）。如果您想将其保持较小，则可以按天数计数，而不是毫秒，并通过在日期计算部分进行乘法转换。 - Asya Kamsky

显示剩余7条评论

3

我喜欢user1083621的答案，但此方法在后续操作中存在一些限制 - 因为您无法在（例如）下一个聚合管道阶段中将其用作日期字段。您既不能比较，也不能使用任何日期聚合运算符，而且聚合后的结果是字符串！所有这些问题都可以通过投影原始日期字段来解决，但在这种情况下，您将在分组阶段保留它时遇到一些困难。最后，有时您只想操作一天的开始时间，而不是任意的时间。因此，这就是我的方法：

{'$project': {
    'start_of_day': {'$subtract': [
        '$date',
        {'$add': [
            {'$multiply': [{'$hour': '$date'}, 3600000]},
            {'$multiply': [{'$minute': '$date'}, 60000]},
            {'$multiply': [{'$second': '$date'}, 1000]},
            {'$millisecond': '$date'}
        ]}
    ]},
}}

它会给你这个：

{
    "start_of_day" : ISODate("2015-12-03T00:00:00.000Z")
},
{
    "start_of_day" : ISODate("2015-12-04T00:00:00.000Z")
}

无法确定它是否比user1083621的方法更快。

- egvo

这不是和我的答案几乎完全相同吗（除了阶段的格式）？ - Asya Kamsky

@asya-kamsky 可能是这样。也许我最初在你提到的网站上找到了它。但我真的很担心答案的呈现方式。那是一个很长的答案，我发现它太长了，无法精确阅读，所以我才发布了我的答案。如果有人发现我的答案更有帮助，那是他们的选择，因为所有的特权都在你这边——当我发布我的答案时，你的答案已经排名第一了。 - egvo

哈哈，你以为我的答案之前已经够长了 - 现在加上了最新的 3.6 方法，长度几乎翻倍了。:) - Asya Kamsky

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- ephigenia · Accepted Answer

在Mongo 2.8 RC2中，出现了一个新的数据聚合操作符：$dateToString。该操作符可用于按天分组，并在结果中简单地得到一个"YYYY-MM-DD"的日期格式：

文档中的示例：

db.sales.aggregate(
  [
     {
         $project: {
                yearMonthDay: { $dateToString: { format: "%Y-%m-%d", date: "$date" } },
                time: { $dateToString: { format: "%H:%M:%S:%L", date: "$date" } }
         }
     }
  ]
)

会导致以下结果:

{ "_id" : 1, "yearMonthDay" : "2014-01-01", "time" : "08:15:39:736" }