mongodb - Through sales records, grouped by product, how to calculate the sales volume and the price of the latest sale?

Question

db.sales.insert({"商品":"上衣","销售时间":"2015-12-01 11:00","售价":1.3});
db.sales.insert({"商品":"上衣","销售时间":"2015-12-01 12:00","售价":1.4});
db.sales.insert({"商品":"裤子","销售时间":"2015-12-01 11:30","售价":2.3});
db.sales.insert({"商品":"裤子","销售时间":"2015-12-01 12:30","售价":2.4});
db.sales.insert({"商品":"鞋子","销售时间":"2015-12-01 11:00","售价":3.3});
db.sales.insert({"商品":"鞋子","销售时间":"2015-12-01 12:00","售价":3.4});
db.sales.insert({"商品":"鞋子","销售时间":"2015-12-01 12:30","售价":3.5});

db.sales.aggregate([
   { $group: { 
       _id: "$商品", 
       "销售量":{$sum:1} ,
       "最后销售时间": { $max: "$销售时间" } 
   } }
]);

Question: How to find the selling price corresponding to the last sales time?

Answer 1

Hope this helps.

Love MongoDB! Have Fun!

Answer 2

The direction is right, but currently there is no such syntax to get the operation of "the document corresponding to the maximum sales time". Let's work around it. We have the document that happens to be the "last sales time". Can we get it? To achieve this goal, you can sort the documents first. So the final statement should be like this: $first/$last这样的操作符。如果让$first

db.sales.aggregate([
    {$sort: {"商品": 1, "销售时间": -1}},
    {$group: {_id: "$商品", "销售量": {$sum: 1}, "最后销售时间": {$first: "$销售时间"}, "最后售价": {$first: "$售价"}}}
]);

In order to be more efficient,

can be accelerated by using indexes. The following indexes are required: $sort

db.sales.createIndex({"商品": 1, "销售时间": -1})

Theoretically, the sorted documents can be processed by

. Unfortunately, the current version has not yet made corresponding optimizations for this point. For corresponding optimization, please pay attention to the update status of Ticket SERVER-4507. $group利用而加速$group