如何获得具有相应文档数组的每个组的字段的最大值? [英] How to get the maximum value of a field for each group with the array of corresponding documents?

问题描述

我有一个类似

{
    "_id" : ObjectId("5738cb363bb56eb8f76c2ba8"),
    "records" : [
        {
            "Name" : "Joe",
            "Salary" : 70000,
            "Department" : "IT"
        }
    ]
},
{
    "_id" : ObjectId("5738cb363bb56eb8f76c2ba9"),
    "records" : [
        {
            "Name" : "Henry",
            "Salary" : 80000,
            "Department" : "Sales"
        },
        {
            "Name" : "Jake",
            "Salary" : 40000,
            "Department" : "Sales"
        }
    ]
},
{
    "_id" : ObjectId("5738cb363bb56eb8f76c2baa"),
    "records" : [
        {
            "Name" : "Sam",
            "Salary" : 90000,
            "Department" : "IT"
        },
        {
            "Name" : "Tom",
            "Salary" : 50000,
            "Department" : "Sales"
        }
    ]
}

我希望获得每个部门薪水最高的结果

I want to have the results with the highest salary by each department

{"Name": "Sam", "Salary": 90000, "Department": "IT"}
{"Name": "Henry", "Salary": 80000, "Department": "Sales"}

我可以获得最高的薪水.但是我无法获得相应的员工姓名.

I could get the highest salary. But I could not get the corresponding employee names.

db.HR.aggregate([

    { "$unwind": "$records" },
    { "$group": 
        {
            "_id": "$records.Department",
            "max_salary": { "$max": "$records.Salary" }
        }
    }   
])

有人可以帮我吗?

推荐答案

您需要 $unwind ，然后在 $group 阶段.您还可以使用 $last 运算符需要按升序对文档进行排序

You need to $sort your document after $unwind and use the $first operator in the $group stage. You can also use the $last operator in which case you will need to sort your documents in ascending order

db.HR.aggregate([
    { '$unwind': '$records' }, 
    { '$sort': { 'records.Salary': -1 } }, 
    { '$group': { 
        '_id': '$records.Department', 
        'Name': { '$first': '$records.Name' } , 
        'Salary': { '$first': '$records.Salary' }
    }}
])

产生:

{ "_id" : "Sales", "Name" : "Henry", "Salary" : 80000 }
{ "_id" : "IT", "Name" : "Sam", "Salary" : 90000 }

---

要返回每个部门的最高薪水和雇员清单，您需要使用 $max 在小组阶段返回每个小组的最大薪水"，然后使用 $push 累加器运算符，以返回每个组的所有雇员的名称"和薪水"列表.在此处，您需要在<在href ="https://docs.mongodb.org/manual/reference/operator/aggregation/project/" rel ="nofollow"> $project 阶段返回名称列表以及最高薪水.当然 $cond 此处用于比较每个员工的薪水达到最大值. $setDifference 会执行其工作，该工作会过滤掉所有并且只要被过滤的数据是唯一的"就可以.在这种情况下，应该"很好，但是如果任何两个结果包含相同的名称"，则通过将两者视为一个来歪曲结果.

---

To return the maximum salary and employees list for each department you need to use the $max in your group stage to return the maximum "Salary" for each group then use $push accumulator operator to return a list of "Name" and "Salary" for all employees for each group. From there you need to use the $map operator in your $project stage to return a list of names alongside the maximum salary. Of course the $cond here is used to compare each employee salary to the maximum value. The $setDifference does his work which is filter out all false and is fine as long as the data being filtered is "unique". In this case it "should" be fine, but if any two results contained the same "name" then it would skew results by considering the two to be one.

db.HR.aggregate([
    { '$unwind': '$records' },
    { '$group': {
        '_id': '$records.Department', 
        'maxSalary': { '$max': '$records.Salary' }, 
        'persons': { 
            '$push': {
                'Name': '$records.Name', 
                'Salary': '$records.Salary' 
            }
        }
    }}, 
    { '$project': { 
        'maxSalary': 1, 
        'persons': { 
            '$setDifference': [
                { '$map': {
                    'input': '$persons', 
                    'as': 'person', 
                    'in': {
                        '$cond': [
                            { '$eq': [ '$$person.Salary', '$maxSalary' ] }, 
                            '$$person.Name', 
                            false
                        ]
                    }
                }}, 
                [false]
            ]
        }
    }}
])

产生:

{ "_id" : "Sales", "maxSalary" : 80000, "persons" : [ "Henry" ] }
{ "_id" : "IT", "maxSalary" : 90000, "persons" : [ "Sam" ] }

这篇关于如何获得具有相应文档数组的每个组的字段的最大值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！