MongoDB汇总前几周的每周总和 [英] MongoDB Aggregate for a sum on a per week basis for all prior weeks

问题描述

我在MongoDB中有一系列文档.一个示例文档将是

I've got a series of docs in MongoDB. An example doc would be

{ 
  createdAt: Mon Oct 12 2015 09:45:20 GMT-0700 (PDT),
  year: 2015,
  week: 41 
}

想象一下，这些跨越一年中的所有几周，并且同一周可能有很多周.我想以一种这样的方式汇总它们，即结果值是每个星期及其所有前几个星期的总和(包括总文档数).

Imagine these span all weeks of the year and there can be many in the same week. I want to aggregate them in such a way that the resulting values are a sum of each week and all its prior weeks counting the total docs.

因此，如果在一年的第一周有10个左右的东西，而在第二周有20个左右的东西，则结果可能是

So if there were something like 10 in the first week of the year and 20 in the second, the result could be something like

[{ week: 1, total: 10, weekTotal: 10},
 { week: 2, total: 30, weekTotal: 20}]

创建汇总以查找weekTotal很容易.包括显示第一部分的投影

Creating an aggregation to find the weekTotal is easy enough. Including a projection to show the first part

db.collection.aggregate([
  {
    $project: {
      "createdAt": 1,
      year: {$year: "$createdAt"},
      week: {$week: "$createdAt"},
      _id: 0
    }
  },
  {
    $group: {
      _id: {year: "$year", week: "$week"},
      weekTotal : { $sum : 1 }
    }
  },
]);

但是，根据该周以及前几周的总和来算是很棘手的事情.

But getting past this to sum based on that week and those weeks preceding is proving tricky.

推荐答案

聚合框架无法执行此操作，因为所有操作一次只能有效地查看一个文档或分组边界.为了在服务器"上执行此操作，您需要访问全局变量以保持运行总计"的内容，这意味着mapReduce代替:

The aggregation framework is not able to do this as all operations can only effectively look at one document or grouping boundary at a time. In order to do this on the "server" you need something with access to a global variable to keep the "running total", and that means mapReduce instead:

db.collection.mapReduce(
    function() {

        Date.prototype.getWeekNumber = function(){
            var d = new Date(+this);
            d.setHours(0,0,0);
            d.setDate(d.getDate()+4-(d.getDay()||7));
            return Math.ceil((((d-new Date(d.getFullYear(),0,1))/8.64e7)+1)/7);
        };


        emit({ year: this.createdAt.getFullYear(), week: this.createdAt.getWeekNumber() }, 1);
    },
    function(values) {
        return Array.sum(values);
    },
    { 
        out: { inline: 1 },
        scope: { total: 0 },
        finalize: function(value) {
            total += value;
            return { total: total, weekTotal: value }
        }
    }
)

如果您可以忍受客户端"上发生的操作，那么您需要遍历汇总结果并类似地将总数相加:

If you can live with the operation occuring on the "client" then you need to loop through the aggregation result and similarly sum up the totals:

var total = 0;

db.collection.aggregate([
    { "$group": {
        "_id": {
            "year": { "$year": "$createdAt" },
            "week": { "$week": "$createdAt" }
        },
        "weekTotal": { "$sum": 1 }
    }},
    { "$sort": { "_id": 1 } }
]).map(function(doc) {
    total += doc.weekTotal;
    doc.total = total;
    return doc;
});

这对您是否最需要在服务器还是客户端上发生完全无关紧要.但是，由于聚合基准线没有这样的全局变量"，因此您可能不应该在不输出到其他集合的情况下将其用于任何进一步的处理.

It's all a matter of whether it makes the most sense to you of whether this needs to happen on the server or on the client. But since the aggregation pipline has no such "globals", then you probably should not be looking at this for any further processing without outputting to another collection anyway.

这篇关于MongoDB汇总前几周的每周总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！