CODEWITHSUNDEEP
mongodbmapreducemongodb-queryaggregation-framework
pnzr
pnzr

Reputation: 225

MongoDB Aggregate for a sum on a per week basis for all prior weeks

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.

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}]

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.

Upvotes: 4

Views: 999

Answers (1)

Blakes Seven
Blakes Seven

Reputation: 50416

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.

Upvotes: 1

Related Questions