Mongo DB根据关键字段查找具有最高价值的所有记录 [英] Mongo DB find all records with highest value depending on a key field

问题描述

我想为集合中的每个用户获取具有最高价值的记录的注释.

I would like to get for each user in my collection the comment of the record with the highest value.

//myCol
[
    {'user' : 1, 'value':20, "comment": "cloudy", "date":"2018-12-01"},
    {'user' : 2, 'value':5, "comment": "sun","date":"2018-12-01"},
    {'user' : 3, 'value':13, "comment": "rain","date":"2018-13-01"},
    {'user' : 4, 'value':13, "comment": "cloudy","date":"2018-13-01"},
    {'user' : 2, 'value':20, "comment": "rain","date":"2018-01-20"},
    {'user' : 1, 'value':2, "comment": "cloudy","date":"2018-02-02"},
]

将给出以下结果:

//Result 
[
    {'user' : 1, 'value':20, "comment": "cloudy", "date":"2018-12-01"},
    {'user' : 2, 'value':20, "comment": "rain","date":"2018-01-20"},
    {'user' : 3, 'value':13, "comment": "rain","date":"2018-13-01"},
    {'user' : 4, 'value':13, "comment": "cloudy","date":"2018-13-01"},
]

在MySQL中，我将使用一些联接逻辑

With MySQL I would use some join logic

SELECT user FROM myTable as t0
LEFT JOIN (SELECT user, max(value) as maxVal from myTable ) t1 on t0.user = t1.user and t0.value = t1.maxVal
WHERE maxVal IS NOT NULL

但是对于Mongo，这种逻辑似乎并不存在.

With Mongo however this logic does not seem to exist.

我尝试通过首先获取每个用户的最大值将代码分成两部分:

I tried to split the code in two by getting first the max values for each user:

max_list = myCol.aggregate(
   [
     {
       "$group":
         {
           "_id": "$user",
           "maxValue": { "$max": "$value" }
         }
     },
     { 
            "$project" : {
                "user" : "$_id", 
                "maxValue":"$maxValue",
                "_id":0
            }
        }
   ]
)
 ==> [{'user':1, 'maxValue':20}...]

为此，我为使用find函数的一种好方法scratch之以鼻，尤其是$in的一种好用法，以便仅获取与my_list中存在的两个值都匹配的结果

With this I'm scratching my head for a good way to use the find function and especially the good use of $in to get only the results which match both the values present in my_list

推荐答案

可以，但是方法略有不同:

It does, but the approach is slightly different:

db.myCol.aggregate([
    {$sort: {value:-1}},
    {$group:{
        _id: "$user",
        doc: {$first: "$$ROOT"}
    }},
    {$replaceRoot: {newRoot: "$doc"} }
])

这篇关于Mongo DB根据关键字段查找具有最高价值的所有记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！