每日分组内的 MongoDB 聚合 [英] MongoDB aggregate within daily grouping

问题描述

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

I have some docs in mongo that looks something like this:

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

理想情况下，我希望能够按品牌计算每天售出的车辆数量.然后我想查看今天或过去 7 天的窗口，例如今天.

Ideally, I'd like to be able to count, by make, the number of vehicles sold per day. I'd then like to view either today, or a window such as today through the last seven days.

我能够用一些丑陋的代码完成日常视图

I was able to accomplish the daily view with some ugly code

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
}

所以没关系，但我更希望不必更改查询中的两个日期时间值.然后，正如我上面提到的，我希望能够运行此查询(同样，无需每次都修改它)并查看上周按天划分的相同结果.

So that is ok, but I would much prefer to not have to change the two datetime values in the query. Then, as I mentioned above, I'd like to be able to run this query (again, without having to modify it each time) and see the same results binned by day over the last week.

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

Oh and here is the sample data I've been using for the query

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")});

提前致谢，凯文

推荐答案

在 Mongo 2.8 RC2 中有一个新的数据聚合操作符:$dateToString 可用于按一天分组并在结果中简单地包含YYYY-MM-DD":

In Mongo 2.8 RC2 there is a new data aggregation operator: $dateToString which can be used to group by a day and simply have a "YYYY-MM-DD" in the result:

文档中的示例:

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" }

这篇关于每日分组内的 MongoDB 聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！