Summing values by a date range. For @dj_agiledev :D

design_doc.js

{
  _id: '_design/queries',
  views: {
    sum_by_date: {
      map: function (doc) {
        if (doc.created_at) {
          // normalize doc.created_at into an integer timestamp
          // in this case, milliseconds since epoch
          var date = new Date(doc.created_at).getTime();
          emit(doc.created_at, doc.VALUE_YOU_WANT_TO_SUM);
        }
      },
      reduce: '_sum'
    }
  }
}

the query

GET http://localhost:5984/DATABASE/_design/queries/_view/sum_by_date?startkey=START_TIMESTAMP&endkey=END_TIMESTAMP

There is one more level of complexity. I need to sum pay for each user across a date range. Here's a snippet of my map function and I just _sum in my reduce.

var date = new Date(doc.date);
var key = [date.getUTCFullYear(), date.getUTCMonth() + 1, date.getUTCDate(), doc.user_id];
return emit(key, doc.pay);

With a query across dates I get back a result for each user for each day that matches my start and end keys. Is there a way to squash it down even further?

_view/by_date?group_level=4&startkey=[2013,1,14]&endkey=[2013,1,15,{}]

Try group_level=3. That'll aggregate pay across users within the date range. Is that what you're looking for?

I need to do the opposite. Given the following data, I need to aggregate a report that shows a users total pay across the queried date range.

"rows" => [
  { "key" => [ 2013, 1, 14, uid_1 ], "value" => 100 },
  { "key" => [ 2013, 1, 14, uid_2 ], "value" => 150 }
  { "key" => [ 2013, 1, 15, uid_1 ], "value" => 200 },
] 

I'd like to retrieve something like this..

user_id, total_pay
uid_1,300
uid_2,150

This got it...

map

function(doc) {
  var data, date, key;
  date = new Date(doc.date);
  key = [date.getUTCFullYear(), date.getUTCMonth() + 1, date.getUTCDate(), doc.employee_number];
  data = {};
  data[doc.employee_number] = doc.pay;
  return emit(key, data);
});

reduce

(function(keys, values, rereduce) {
  var k, results, v, value, _i, _len;
  results = {};
  for (_i = 0, _len = values.length; _i < _len; _i++) {
    value = values[_i];
    for (k in value) {
      v = value[k];
      results[k] || (results[k] = 0);
      results[k] += v;
    }
  }
  return results;
});

Custom reduce FTW!

I think this is better accomplished by using a built-in reduce and a different key order:

 var date = new Date(doc.date);
 var key = [doc.user_id, date.getUTCFullYear(), date.getUTCMonth() + 1, date.getUTCDate()];
 return emit(key, doc.pay);

With a query across dates I get back a result for each user for each day that matches my start and end keys. Is there a way to squash it down even further?

 _view/by_date?group_level=4&startkey=[user_id, 2013,1,14]&endkey=[user_id, 2013,2,15]

Built-in reduces have strong performance gains, are less error prone, and allow you to neat things like:

 emit(key, [doc.pay, doc.hours, ..., <some other numbers>]);

And, then _sum will go and sum each element of the array, etc.