我有一个如下形式的聚合查询

db.mycollection.aggregate([  
  {
    "$match": 
    {
      "Time": { $gte: ISODate("2016-01-30T00:00:00.000+0000") }
    }
  },  
  { 
    "$group": 
    {
      "_id": 
      {  
        "day": { "$dayOfYear": "$Time" },  
        "hour": { "$hour": "$Time" } 
      },  
      "Dishes": { "$addToSet": "$Dish" }  
    }
  },  
  { 
    "$group": 
    {  
      "_id": "$_id.hour",  
      "Food": 
      {   
        "$push": 
        {  
          "Day": "$_id.day",  
          "NumberOfDishes": { "$size":"$Dishes" }  
        }  
      }  
    }
  },  
  {
    "$project":
      {
        "Hour": "$_id",
        "Food": "$Food",
        "_id" : 0
      }
  },  
  { 
    "$sort": { "Hour": 1 }
  }  
]);

而不是在一个小时的持续时间内像上面那样做，例如0-1,1-2,2-3,3-4,4-5,...,23-24，我希望能够在两个小时内完成。例如0-2,2-4,4-6,...,22-24。有办法吗？

最佳答案

提示:使用arithmetic aggregation operators在 $project

比方说，H=floor(hour/2)，其中 hour 是从文档日期算起的实际小时数。然后你可以通过应用 $floor 得到 H和 $divide迄今为止的运营商

"H": { $floor: { $divide: [ { "$hour": "$Time" }, 2 ] } }

这里 H 对应小时对 (Hours=[0,2) => H=0, Hours=[2,4) = > H=1, Hours=[22,24) => H=11 等)，你可以将它传递给 $group舞台与

$group: { "_id": { "day": { $dayOfYear: "$Time" }, "H": "$H" } }

然后您可以输出特定 H 的小时数对

"Hours": [ { $multiply: [ "$H", 2 ] }, { $sum: [ { $multiply: [ "$H", 2 ] }, 2 ] } ]

给定文档集合

{ "Time" : ISODate("2016-01-30T01:00:00Z"), "Dish" : "dish1" }
{ "Time" : ISODate("2016-01-30T02:00:00Z"), "Dish" : "dish2" }
{ "Time" : ISODate("2016-01-30T03:00:00Z"), "Dish" : "dish3" }
{ "Time" : ISODate("2016-01-30T04:00:00Z"), "Dish" : "dish4" }
{ "Time" : ISODate("2016-01-30T05:00:00Z"), "Dish" : "dish5" }
{ "Time" : ISODate("2016-01-30T06:00:00Z"), "Dish" : "dish6" }
{ "Time" : ISODate("2016-01-30T07:00:00Z"), "Dish" : "dish7" }
{ "Time" : ISODate("2016-01-30T08:00:00Z"), "Dish" : "dish8" }
{ "Time" : ISODate("2016-01-30T09:00:00Z"), "Dish" : "dish9" }

并在其上使用下一个聚合

db.mycollection.aggregate([  
  {
    "$match": 
    {
      "Time": { $gte: ISODate("2016-01-30T00:00:00.000+0000") }
    }
  },
  {
    "$project":
    {
      "Dish": 1,
      "Time": 1,
      "H": { $floor: { $divide: [ { $hour: "$Time" }, 2 ] } }
    }
  },
  { 
    "$group": 
    {
      "_id": 
      {  
        "day": { $dayOfYear: "$Time" },  
        "H": "$H" 
      },
      "Dishes": { $addToSet: "$Dish" }  
    }
  },  
  { 
    "$group": 
    {  
      "_id": "$_id.H",  
      "Food": 
      {   
        "$push": 
        {  
          "Day": "$_id.day",  
          "NumberOfDishes": { $size: "$Dishes" }  
        }  
      }  
    }
  },
  { 
    "$sort": { "_id": 1 }
  },
  {
    "$project":
      {
        "Hours": [ { $multiply: [ "$_id", 2 ] }, { $sum: [ { $multiply: [ "$_id", 2 ] }, 2 ] } ],
        "Food": "$Food",
        "_id": 0
      }
  }
]);

提供结果

{ "Food" : [ { "Day" : 30, "NumberOfDishes" : 1 } ], "Hours" : [ 0, 2 ] }
{ "Food" : [ { "Day" : 30, "NumberOfDishes" : 2 } ], "Hours" : [ 2, 4 ] }
{ "Food" : [ { "Day" : 30, "NumberOfDishes" : 2 } ], "Hours" : [ 4, 6 ] }
{ "Food" : [ { "Day" : 30, "NumberOfDishes" : 2 } ], "Hours" : [ 6, 8 ] }
{ "Food" : [ { "Day" : 30, "NumberOfDishes" : 2 } ], "Hours" : [ 8, 10 ] }

关于Mongodb 每两小时聚合一次，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/38734389/