MongoDB based on time aggregate example

You need to accumulate TranslateFields values for the following collection, grouped by day according to LastUpdate.

Rs_test:SECONDARY > db.new_result.find () {"_ id": ObjectId ("57fb0756e31f84a56ed41889"), "LastUpdate": ISODate ("2016-09-02T01:35:02.471Z"), "TranslateFields": 9} {"_ id": ObjectId ("57fb0756e31f84a56ed4188a"), "LastUpdate": ISODate ("2016-09-05T11:13:28.344Z"), "TranslateFields": 10} {"_ id": ObjectId ("57fb0756e31f84a56ed4188b") LastUpdate: ISODate ("2016-09-05T09:26:41.016Z"), "TranslateFields": 33} {"_ id": ObjectId ("57fb0756e31f84a56ed4188c"), "LastUpdate": ISODate ("2016-09-02T13:34:50.114Z"), "TranslateFields": 12} {"_ id": ObjectId ("57fb0756e31f84a56ed4188d"), "LastUpdate": ISODate ("2016-08-26T03:49:52.369Z"), "TranslateFields": 17}

If you are in SQL Server, the group statistics should be written like this:

SELECT CONVERT (varchar,LastUpdate,112), SUM (TranslateFields) FROM dbo.new_result GROUP BY CONVERT (varchar,LastUpdate,112) ORDER BY 1

So in MongoDB, there are three aggregation methods: group, aggregate, and mapReduce

/ version 2.6 aggregate method db.new_result.aggregate ({$group: {_ id: {year: {$year: "$LastUpdate"}, month: {$month: "$LastUpdate"}, day: {$dayOfMonth: "$LastUpdate"}}, totalTime: {$sum: "$TranslateFields"} {$sort: {"_ id.year": 1, "_ id.month": 1, "_ id.day": 1}) / / version 3.0 aggregate method db.new_result.aggregate ({$group: {yearMonthDay: {$dateToString: {format: "% Y-%m-%d" Date: "$LastUpdate"}}, totalTime: {$sum: "$TranslateFields"}, {$sort: {"yearMonthDay": 1}}) / / group method db.new_result.group ({keyf: function (doc) {var date = new Date (doc.LastUpdate) Var dateKey = "+ date.getFullYear () +"-"+ (date.getMonth () + 1) +"-+ date.getDate (); return {'day':dateKey};}, initial: {"time": 0}, reduce: function (doc, prev) {prev.time + = doc.TranslateFields;}, finalize: function Finalize (out) {return out;}) / / first saved as date / / 1 db.tmp_result.find ({"value.Status": 3}, {"value.TranslateFields": 1 "value.LastUpdate": 1}. ForEach (function (item) {db.new_result.save ({"LastUpdate": item.value.LastUpdate.getFullYear () + "-" + (item.value.LastUpdate.getMonth () + 1) + "-" + item.value.LastUpdate.getDate (), "TranslateFields": item.value.TranslateFields}) }) / / 2 db.new_result.aggregate ({$group: {_ id: "$LastUpdate", totalTime: {$sum: "$TranslateFields"}, {"$sort": {"_ id": 1}})

For the aggregate method, it is best to match before $group to reduce the amount of data, and if there is an index on the filtered key, the query will also take the index.

Db.TranslateTicket.aggregate ({"$match": {"LastUpdate": {"$gte": ISODate ("2016-06-19T00:00:00.000Z"), "$lt": ISODate ("2016-09-19T00:00:00.000Z")}, "Status": 3}} {"$group": {_ id: {month: {$month: "$LastUpdate"}, day: {$dayOfMonth: "$LastUpdate"}, year: {$year: "$LastUpdate"}}, totalTime: {$sum: "$CharactersCount"}, {"$sort": {"_ id.year": 1, "_ id.month": 1 "_ id.day": 1}})

In this case, it is best to create the following index:

Db.TranslateTicket.createIndex ({"LastUpdate": 1, "Status": 1}, {background:1})