mongodb - 根据关键值对文档进行排序和排名？

Question

我正在尝试为集合中的学生提取得分最高的文档，并在下面形成查询:

{ name: "Person1", marks: 20  }
{ name: "Person2", marks: 20  }
{ name: "Person1", marks: 30  }
{ name: "Person1", marks: 25  }
{ name: "Person2", marks: 50  }
{ name: "Person1", marks: 90  }
{ name: "Person3", marks: 990 }

我的查询:

db.mytest1.aggregate( [          
  { $sort : { "name" : 1,"marks" : -1} },
  {$group:
    {
      _id: "$name",
      name: { $first: "$name" },
      marks: { $first: "$marks" }
   }}
])

有更好的方法吗？

如果我的场景是根据标记对文档进行编号，我该如何实现？

我想得到以下结果:

{ name: "Person1", marks: 90,  rank: 1 }
{ name: "Person1", marks: 30,  rank: 2 }
{ name: "Person1", marks: 25,  rank: 3 }
{ name: "Person1", marks: 20,  rank: 4 }
{ name: "Person2", marks: 50,  rank: 1 }
{ name: "Person2", marks: 20,  rank: 2 }
{ name: "Person3", marks: 990, rank: 3 }

Answer 1

我真的认为这是最实用的简单游标迭代，但稍后会详细介绍。

使用聚合框架进行“小型”分组的最快实用方法是使用 MongoDB 3.2 引入的 $unwind 中的 includeArrayIndex:

db.mytest1.aggregate([
  { "$sort": { "name" : 1,"marks" : -1} },
  { "$group": {
    "_id": "$name",
    "items": { "$push": "$$ROOT" }
  }},
  { "$unwind": { "path": "$items", "includeArrayIndex": "items.rank" } },
  { "$replaceRoot": { "newRoot": "$items" } },
  { "$sort": { "name" : 1,"marks" : -1} }
])

产生:

{ "name" : "Person1", "marks" : 90, "rank" : NumberLong(0) }
{ "name" : "Person1", "marks" : 30, "rank" : NumberLong(1) }
{ "name" : "Person1", "marks" : 25, "rank" : NumberLong(2) }
{ "name" : "Person1", "marks" : 20, "rank" : NumberLong(3) }
{ "name" : "Person2", "marks" : 50, "rank" : NumberLong(0) }
{ "name" : "Person2", "marks" : 20, "rank" : NumberLong(1) }
{ "name" : "Person3", "marks" : 990, "rank" : NumberLong(0) }

或者走得更远一点:

db.mytest1.aggregate([
  { "$sort": { "name" : 1,"marks" : -1} },
  { "$group": {
    "_id": "$name",
    "items": { "$push": "$$ROOT" }
  }},
  { "$unwind": { "path": "$items", "includeArrayIndex": "items.rank" } },
  { "$project": { 
    "_id": 0, 
    "name": "$items.name", 
    "marks": "$items.marks",
    "rank": { "$add": [ "$items.rank", 1 ] } 
  }},
  { "$sort": { "name" : 1,"marks" : -1} }
])

以你想要的方式。

{ "name" : "Person1", "marks" : 90, "rank" : 1 }
{ "name" : "Person1", "marks" : 30, "rank" : 2 }
{ "name" : "Person1", "marks" : 25, "rank" : 3 }
{ "name" : "Person1", "marks" : 20, "rank" : 4 }
{ "name" : "Person2", "marks" : 50, "rank" : 1 }
{ "name" : "Person2", "marks" : 20, "rank" : 2 }
{ "name" : "Person3", "marks" : 990, "rank" : 1 }

但是要小心，因为我们将所有内容都放入一个数组中进行“分组”，以便在提取时获得“索引”位置。这适用于小型列表，但您永远不会尝试使用数千个项目。

对于 1000 项，然后迭代游标并在中断处排名:

var current = null,
    rank = 0;

db.mytest1.find().sort({ "name": 1, "marks": -1 }).forEach(doc => {
  if ( doc.name != current || current == null ) {
    rank = 0;
    current = doc.name;
  }
  rank++;
  doc.rank = rank;
  delete doc._id;
  printjson(doc);
})

这是相同的结果:

{ "name" : "Person1", "marks" : 90, "rank" : 1 }
{ "name" : "Person1", "marks" : 30, "rank" : 2 }
{ "name" : "Person1", "marks" : 25, "rank" : 3 }
{ "name" : "Person1", "marks" : 20, "rank" : 4 }
{ "name" : "Person2", "marks" : 50, "rank" : 1 }
{ "name" : "Person2", "marks" : 20, "rank" : 2 }
{ "name" : "Person3", "marks" : 990, "rank" : 1 }

实际上，您也可以这样做，因为它既简单又快速。