Mongodb - 在一个查询中查询今天的总数、周的总数和月份的总数 [英] Mongodb - query today's totals, week's totals, and month's totals in one query
问题描述
我的数据库中有如下所示的对象:
<代码>{"_id": ObjectId("563f8c320ef987c122aeeb4a"),数字":1515，"createdAt": ISODate("2015-10-29T21:14:26.477Z"),}我想编写一个聚合，按特定 ID 对所有内容进行分组，并汇总今天、本周和本月的总数，并在一个查询中完成.我为此任务编写了三个单独的查询，但我想知道是否可以提高效率并在一个查询中完成.
编辑
有人提到 mapReduce 作为解决方案.这似乎是一个很有前途的解决方案，但我无法从简单的查询中获得任何返回值.以下是我尝试过的:
var o = {};o.map = function () { 发射( this.num, this.createdAt ) }o.reduce = function (k, vals) { return vals }o.query = {_id:req.user._id}Submission.mapReduce(o, function (err, results) {控制台日志(结果)})控制台记录一个空数组.我还尝试将 _id 转换为 mongoose 对象 id，但它仍然返回一个空数组.
这实际上是一个关于您期望输出是什么样子的问题，因为任何聚合结果本质上都需要在最低级别分组，然后按更高的粒度"逐步分组，直到最大级别(月")是到达.这种暗示数据最终是按月"分组的，除非您以其他方式对其进行细分.
本质上，逐步$group:
db.collection.aggregate([//每天的第一个总数.在此处用数学四舍五入日期{$组":{_ID": {"$add": [{$减去":[{ "$subtract": [ "$createdAt", new Date(0) ] },{$mod":[{ "$subtract": [ "$createdAt", new Date(0) ] },1000*60*60*24]}]},新日期(0)]},"周": { "$first": { "$week": "$createdAt" } },"月": { "$first": { "$month": "$createdAt" } },总计":{$sum":$num"}}},//然后按周分组{$组":{"_id": "$week","月": { "$first": "$month" },天": {$推":{"day": "$_id",总计":$ 总计"}},总计":{$sum":$total"}}},//然后按月分组{$组":{"_id": "$month",周":{$推":{"周": "$_id","total": "$total",天":$天"}},总计":{$sum":$total"}}}])因此，每天汇总的第一个级别之后的每个级别都会逐渐推送到数组内容中，因为它是四舍五入"值，然后在该级别汇总总计.
如果你想要一个更平坦的输出，每天有一个记录，包含每周和每月的总数以及一天的总数，那么只需在管道的末尾附加两个 $unwind 语句:
{ "$unwind": "$weeks" },{ "$unwind": "$weeks.days" }和可选的 $project 将虚线"字段显示为更扁平和可读的内容(如果必须).
如果您要跨越年"，那么至少从周"级别将这样的操作包含在分组键中，这样您就不可能合并来自不同年份的数据并且将它们分开.
在四舍五入日期时使用 日期数学" 方法也是我自己的一般偏好，因为它返回一个 Date 对象，但在day"以外的其他级别使用，您可以交替使用 日期聚合运算符.
不需要 mapReduce，因为这相当直观，而且一个月中的天数是有限的，这意味着在聚合时在内容中嵌套数组时的 BSON 限制不会被打破.>
I have objects in my db that look like this:
{
"_id": ObjectId("563f8c320ef987c122aeeb4a"),
"num": 1515,
"createdAt": ISODate("2015-10-29T21:14:26.477Z"),
}
I'd like to write an aggregation that groups all by a specific id and sums up the total for today, this week, and this month, and do it in one query. I've written three separate queries for this task, but I'm wondering if I can make this more efficient and get it done in one query.
Edit
Somebody mentioned mapReduce as a solution. It seems like a promising solution, but I'm not able to get anything to return from a simple query. The following is what I have tried:
var o = {};
o.map = function () { emit( this.num, this.createdAt ) }
o.reduce = function (k, vals) { return vals }
o.query = {
_id: req.user._id
}
Submission.mapReduce(o, function (err, results) {
console.log(results)
})
The console logs an empty array. I've also tried casting the _id to a mongoose object id, but it still returns an empty array.
This is more really a question of what you expect the output to look like, as any aggregated result essentially needs to group at the lowest level and then progressively group at higher "grains" until the largest level ( "month" ) is reached. This kind of implies data grouped by "month" ultimately, unless you break it down otherwise.
In essence, progressively $group:
db.collection.aggregate([
// First total per day. Rounding dates with math here
{ "$group": {
"_id": {
"$add": [
{ "$subtract": [
{ "$subtract": [ "$createdAt", new Date(0) ] },
{ "$mod": [
{ "$subtract": [ "$createdAt", new Date(0) ] },
1000 * 60 * 60 * 24
]}
]},
new Date(0)
]
},
"week": { "$first": { "$week": "$createdAt" } },
"month": { "$first": { "$month": "$createdAt" } },
"total": { "$sum": "$num" }
}},
// Then group by week
{ "$group": {
"_id": "$week",
"month": { "$first": "$month" },
"days": {
"$push": {
"day": "$_id",
"total": "$total"
}
},
"total": { "$sum": "$total" }
}},
// Then group by month
{ "$group": {
"_id": "$month",
"weeks": {
"$push": {
"week": "$_id",
"total": "$total",
"days": "$days"
}
},
"total": { "$sum": "$total" }
}}
])
So each level after the first which sums up per day is then progressively pushed into array content for it's "round up" value and the totals are then summed at that level as well.
If you want a flatter output with one record per day containing it's weekly and monthly totals as well as the day total, then just append two $unwind statements to the end of the pipeline:
{ "$unwind": "$weeks" },
{ "$unwind": "$weeks.days" }
And optionally $project the "dotted" fields out to something flatter and readable if you must.
If you are spanning "years" with this, then include such an operation in the grouping key at least from the "week" level so you are not possibly combining data from different years and they are separated.
It is also my own general preference to use the "date math" approach when rounding dates as it returns a Date object, but as is used at the other levels than "day", you can just alternately use the date aggregation operators instead.
No need for mapReduce as this is fairly intuitive and there is a finite amount of days in a month that means the BSON limit when nesting arrays in the content while aggregating will not be broken.
这篇关于Mongodb - 在一个查询中查询今天的总数、周的总数和月份的总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
