如何在猫鼬中按多个字段排序的列表中获得项目排名 [英] How to get item ranking in list sorted by multiple fields in Mongoose

问题描述

我在MongoDB集合中有许多用户记录(> 10000)，可以按分数降序+时间升序+奖励降序排序.如何使用Mongoose进行排序，从而获得列表中一位用户的排名?假设索引已正确构建.

解决方案

按照您的排序顺序计算该用户之前的用户数.我将从简单(非复合排序)的情况开始，因为复合情况下的查询更加复杂，即使其思想完全相同.

> db.test.drop()
> for (var i = 0; i < 10; i++) db.test.insert({ "x" : i })
> db.test.find({ }, { "_id" : 0 }).sort({ "x" : -1 }).limit(5)
{ "x" : 9 }
{ "x" : 8 }
{ "x" : 7 }
{ "x" : 6 }
{ "x" : 5 }

对于此顺序，文档{ "x" : i }的排名是{ "x" : j }中带有i < j

的文档数

> var rank = function(id) {
    var i = db.test.findOne({ "_id" : id }).x
    return db.test.count({ "x" : { "$gt" : i } })
}
> var id = db.test.findOne({ "x" : 5 }).id
> rank(id)
4

排名将基于0.类似地，如果您要计算文档{ "x" : i }的排名{ "x" : 1 }，则可以将文档{ "x" : j }的数量与i > j进行计数.

对于复合排序，可以使用相同的过程，但是实现起来比较棘手，因为复合索引中的顺序是按字典顺序排序的，即对于{ "a" : 1, "b" : 1}，(a, b) < (c, d)排序(如果a < c或a = c且b < d，因此我们需要一个更复杂的查询来表达这种情况.这是一个复合索引的示例:

> db.test.drop()
> for (var i = 0; i < 3; i++) {
    for (var j = 0; j < 3; j++) {
        db.test.insert({ "x" : i, "y" : j })
    }
}
> db.test.find({}, { "_id" : 0 }).sort({ "x" : 1, "y" : -1 })
{ "x" : 0, "y" : 2 }
{ "x" : 0, "y" : 1 }
{ "x" : 0, "y" : 0 }
{ "x" : 1, "y" : 2 }
{ "x" : 1, "y" : 1 }
{ "x" : 1, "y" : 0 }
{ "x" : 2, "y" : 2 }
{ "x" : 2, "y" : 1 }
{ "x" : 2, "y" : 0 }

要找到文档{ "x" : i, "y" : j }的等级，您需要按照{ "x" : 1, "y" : -1 }的顺序找到文档{ "x" : a, "y" : b }的数量，以使(i, j) < (a, b)排成一列.给定排序规范，这等效于条件i < a或i = a和j > b:

> var rank = function(id) {
    var doc = db.test.findOne(id)
    var i = doc.x
    var j = doc.y
    return db.test.count({
        "$or" : [
            { "x" : { "$lt" : i } },
            { "x" : i, "y" : { "$gt" : j } }
        ]
    })
}
> id = db.test.findOne({ "x" : 1, "y" : 1 })._id
> rank(id)
4

最后，如果您使用的是由三部分组成的复合索引

{ "score" : -1, "time" : 1, "bonus" : -1 }

rank函数应为

> var rank = function(id) {
    var doc = db.test.findOne(id)
    var score = doc.score
    var time = doc.time
    var bonus = doc.bonus
    return db.test.count({
        "$or" : [
            { "score" : { "$gt" : score } },
            { "score" : score, "time" : { "$lt" : time } },
            { "score" : score, "time" : time, "bonus" : { "$gt" : bonus } }
        ]
    })
}

I have a number of user records (> 10000) in a MongoDB collection which can be sorted by score desc + time asc + bonus desc. How can I get the ranking of one user in the list according to this sorting using Mongoose? Assume index has been built correctly.

解决方案

Count the number of users that come before this user in your sort order. I'll start with the case of a simple (non-compound sort) because the query in the compound case is more complicated, even though the idea is exactly the same.

> db.test.drop()
> for (var i = 0; i < 10; i++) db.test.insert({ "x" : i })
> db.test.find({ }, { "_id" : 0 }).sort({ "x" : -1 }).limit(5)
{ "x" : 9 }
{ "x" : 8 }
{ "x" : 7 }
{ "x" : 6 }
{ "x" : 5 }

For this order, the ranking of a document { "x" : i } is the number of documents { "x" : j } with i < j

> var rank = function(id) {
    var i = db.test.findOne({ "_id" : id }).x
    return db.test.count({ "x" : { "$gt" : i } })
}
> var id = db.test.findOne({ "x" : 5 }).id
> rank(id)
4

The ranking will be based at 0. Similarly, if you want to compute the rank for the document { "x" : i } in the sort { "x" : 1 }, you would count the number of docs { "x" : j } with i > j.

For a compound sort, the same procedure works, but it is trickier to implement because the order in a compound index is lexicographic, i.e., for the sort { "a" : 1, "b" : 1}, (a, b) < (c, d) if a < c or a = c and b < d, so we need a more complicated query to express this condition. Here's an example for a compound index:

> db.test.drop()
> for (var i = 0; i < 3; i++) {
    for (var j = 0; j < 3; j++) {
        db.test.insert({ "x" : i, "y" : j })
    }
}
> db.test.find({}, { "_id" : 0 }).sort({ "x" : 1, "y" : -1 })
{ "x" : 0, "y" : 2 }
{ "x" : 0, "y" : 1 }
{ "x" : 0, "y" : 0 }
{ "x" : 1, "y" : 2 }
{ "x" : 1, "y" : 1 }
{ "x" : 1, "y" : 0 }
{ "x" : 2, "y" : 2 }
{ "x" : 2, "y" : 1 }
{ "x" : 2, "y" : 0 }

To find the rank for the document { "x" : i, "y" : j }, you need to find the number of documents { "x" : a, "y" : b } in the order { "x" : 1, "y" : -1 } such that (i, j) < (a, b). Given the sort specification, this is equivalent to the condition i < a or i = a and j > b:

> var rank = function(id) {
    var doc = db.test.findOne(id)
    var i = doc.x
    var j = doc.y
    return db.test.count({
        "$or" : [
            { "x" : { "$lt" : i } },
            { "x" : i, "y" : { "$gt" : j } }
        ]
    })
}
> id = db.test.findOne({ "x" : 1, "y" : 1 })._id
> rank(id)
4

Finally, in your case of a three-part compound index

{ "score" : -1, "time" : 1, "bonus" : -1 }

the rank function would be

> var rank = function(id) {
    var doc = db.test.findOne(id)
    var score = doc.score
    var time = doc.time
    var bonus = doc.bonus
    return db.test.count({
        "$or" : [
            { "score" : { "$gt" : score } },
            { "score" : score, "time" : { "$lt" : time } },
            { "score" : score, "time" : time, "bonus" : { "$gt" : bonus } }
        ]
    })
}

这篇关于如何在猫鼬中按多个字段排序的列表中获得项目排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！