search

Home  >  Q&A  >  body text

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

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

问题:如何求最后销售时间对应的售价?

漂亮男人漂亮男人2753 days ago713

reply all(2)I'll reply

  • 高洛峰

    高洛峰2017-05-02 09:25:33

    Hope this helps.

    Love MongoDB! Have Fun!

    reply
    0
  • ringa_lee

    ringa_lee2017-05-02 09:25:33

    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

    reply
    0
  • Cancelreply