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中，有一个新的数据聚合运算符:

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屋！