Thursday, November 8, 2012

mongodb mapreduce to use as group by and sorted before running


//SQL: select siteref, panelno, count(*), sum(rental) as total rental, sum(lighting) as totallighting from campaign where `year` >= 2011 group by siteref, panelno order by siteref asc;
//please take note this sort is before the grouping
// if you want after grouping result, query the result from output table, and sort it there

db.campaign.runCommand({
  mapreduce: "campaign",
  sort: { "siteref": 1 },
  map: function() {
    //month starts on 0
    day = new Date(this.year, this.mon-1, 1, 0, 0, 0);
    oSite = db.site.findOne({siteref: this.siteref, panelno: this.panelno});
    emit({siteref: this.siteref, panelno: this.panelno, advertiser: this.advertiser},
      { rental: this.rental, lighting: this.lighting });
  },
  reduce: "function(key, values) {\
    var output = { cnt: 0, totalrental: 0, totallighting: 0, aa: 1};\
    values.forEach(function(v) {\
      output['cnt']++;\
      output['totalrental'] += v['rental'];\
      output['totallighting'] += v['lighting'];\
    });\
    return output;\
  }",
  out: {
    "replace": "campaign_report"
  },
  query: {
    year: { $gte: 2011}
  }
});

No comments: