mongodb - 通过销售记录，按商品分组，如何计算销售量和最近一次销售的价格?

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: "$销售时间" } 
   } }
]);

问题：如何求最后销售时间对应的售价？

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