7月份MongoDB聚合的一个月内的每日活动的总和

Question

我需要得到7月份每天完成的步骤的总和。我预计会有31个结果。

以下是数据库中的一个示例：

/* 1 */
{
"_id" : ObjectId("5ff0a5878da39cdbf696ffea"),
"dateTime" : ISODate("2020-07-03T17:20:00.000Z"),
"value" : "14"
}
/* 2 */
{
"_id" : ObjectId("5ff0a5878da39cdbf696ffeb"),
"dateTime" : ISODate("2020-07-03T17:43:00.000Z"),
"value" : "57"
}

"；值"；是完成的步骤。

Answer 1

如果你想用一点JS帮助来计算0的天数，这里有一个解决方案(你需要在jsMonth中提供哪个月，在jsDayCount中提供哪个年(：

mongos> db.d.find()
{ "_id" : ObjectId("5ff62e4484088fbb4acaf035"), "dateTime" : ISODate("2020-07-03T17:20:00Z"), "value" : "14" }
{ "_id" : ObjectId("5ff62e4484088fbb4acaf036"), "dateTime" : ISODate("2020-07-05T17:20:00Z"), "value" : "14" }
{ "_id" : ObjectId("5ff62e4484088fbb4acaf037"), "dateTime" : ISODate("2020-07-03T17:20:00Z"), "value" : "50" }
{ "_id" : ObjectId("5ff62e4484088fbb4acaf038"), "dateTime" : ISODate("2020-08-10T17:20:00Z"), "value" : "100" }
mongos> var jsMonth=7;var jsDayCount=new Date(2020, jsMonth , 0).getDate();
mongos> db.d.aggregate([ 
{$project:{ byDay:{$dayOfMonth:{date:"$dateTime",timezone:"+0200"}},_id:0 , val:{$toInt:"$value"} ,mon: {$month:{date:'$dateTime',timezone:"+0200"}}  }} ,{$match:{ mon:jsMonth   }}  ,
{$group:{_id:"$byDay" , perDayTotal:{$sum:"$val"}}}    , {$sort:{_id:1}}  , 
{$group:{_id:"total" , arr:{$push:{day:"$_id",perDay:"$perDayTotal"   }} }}  , 
{ $addFields:{ arr:{ $reduce:{ input:{$setDifference:[ {$range:[1,jsDayCount,1]} ,"$arr.day"  ]   }, initialValue:"$arr",in:{ $concatArrays: 
["$$value" ,[{day:"$$this", perDay:0  }] ]     }     }  }  } }   ,
{$unwind:"$arr"}, 
{$project:{_id:0 ,day:"$arr.day" ,    countPerDay:"$arr.perDay"}}   , $sort:{"day":1}} ])
{ "day" : 1, "countPerDay" : 0 }
{ "day" : 2, "countPerDay" : 0 }
{ "day" : 3, "countPerDay" : 64 }
{ "day" : 4, "countPerDay" : 0 }
{ "day" : 5, "countPerDay" : 14 }
{ "day" : 6, "countPerDay" : 0 }
{ "day" : 7, "countPerDay" : 0 }
{ "day" : 8, "countPerDay" : 0 }
{ "day" : 9, "countPerDay" : 0 }
{ "day" : 10, "countPerDay" : 0 }
{ "day" : 11, "countPerDay" : 0 }
{ "day" : 12, "countPerDay" : 0 }
{ "day" : 13, "countPerDay" : 0 }
{ "day" : 14, "countPerDay" : 0 }
{ "day" : 15, "countPerDay" : 0 }
{ "day" : 16, "countPerDay" : 0 }
{ "day" : 17, "countPerDay" : 0 }
{ "day" : 18, "countPerDay" : 0 }
{ "day" : 19, "countPerDay" : 0 }
{ "day" : 20, "countPerDay" : 0 }
{ "day" : 21, "countPerDay" : 0 }
{ "day" : 22, "countPerDay" : 0 }
{ "day" : 23, "countPerDay" : 0 }
{ "day" : 24, "countPerDay" : 0 }
{ "day" : 25, "countPerDay" : 0 }
{ "day" : 26, "countPerDay" : 0 }
{ "day" : 27, "countPerDay" : 0 }
{ "day" : 28, "countPerDay" : 0 }
{ "day" : 29, "countPerDay" : 0 }
{ "day" : 30, "countPerDay" : 0 }
mongos> 

Answer 2

根据请求，也许这样的东西可以完成任务：

db.the_steps_collection.aggregate([ {$project:{ byDay:{$substr:["$dateTime",0,10]},_id:0 , val:{$toInt:"$value"}   }} ,{$match:{ byDay:/^2020-07/   }}  , {$group:{_id:"$byDay" , perDayTotal:{$sum:"$val"}}}   ])

或者这似乎更快：

db.the_steps_collection.aggregate([ {$project:{ byDay:{$substr:["$dateTime",0,10]},_id:0 , val:{$toInt:"$value"} ,mon:{$month: '$dateTime'}  }} ,{$match:{ mon:7   }}  , {$group:{_id:"$byDay" , perDayTotal:{$sum:"$val"}}}   ])

稍微干净一点(时区可能不同(：

db.the_steps_collection.aggregate([ {$project:{ byDay:{$dayOfMonth:{date:"$dateTime",timezone:"+0200"}},_id:0 , val:{$toInt:"$value"} ,mon:{$month:{date:'$dateTime',timezone:"+0200"}}  }} ,{$match:{ mon:7   }}  , {$group:{_id:"$byDay" , perDayTotal:{$sum:"$val"}}}  ,{$sort:{_id:1}} ])