$group to group data by specified attribute
// prepare data
> db.company.insertOne({"name":"A","foundYear":1980,"numberOfEmployee":50})
{
"acknowledged" : true,
"insertedId" : ObjectId("5f96340713b68b37fd0ed626")
}
> db.company.insertOne({"name":"B","foundYear":1980,"numberOfEmployee":60})
{
"acknowledged" : true,
"insertedId" : ObjectId("5f96340f13b68b37fd0ed627")
}
> db.company.insertOne({"name":"C","foundYear":1980,"numberOfEmployee":70})
{
"acknowledged" : true,
"insertedId" : ObjectId("5f96341513b68b37fd0ed628")
}
> db.company.insertOne({"name":"D","foundYear":1981,"numberOfEmployee":50})
{
"acknowledged" : true,
"insertedId" : ObjectId("5f96341f13b68b37fd0ed629")
}
> db.company.insertOne({"name":"E","foundYear":1981,"numberOfEmployee":60})
{
"acknowledged" : true,
"insertedId" : ObjectId("5f96342713b68b37fd0ed62a")
}
> db.company.insertOne({"name":"F","foundYear":1981,"numberOfEmployee":70})
{
"acknowledged" : true,
"insertedId" : ObjectId("5f96342e13b68b37fd0ed62b")
}
> db.company.insertOne({"name":"G","foundYear":1980,"numberOfEmployee":70})
{
"acknowledged" : true,
"insertedId" : ObjectId("5f9634a013b68b37fd0ed62c")
}
// find data
> db.company.find().pretty()
{
"_id" : ObjectId("5f96340713b68b37fd0ed626"),
"name" : "A",
"foundYear" : 1980,
"numberOfEmployee" : 50
}
{
"_id" : ObjectId("5f96340f13b68b37fd0ed627"),
"name" : "B",
"foundYear" : 1980,
"numberOfEmployee" : 60
}
{
"_id" : ObjectId("5f96341513b68b37fd0ed628"),
"name" : "C",
"foundYear" : 1980,
"numberOfEmployee" : 70
}
{
"_id" : ObjectId("5f96341f13b68b37fd0ed629"),
"name" : "D",
"foundYear" : 1981,
"numberOfEmployee" : 50
}
{
"_id" : ObjectId("5f96342713b68b37fd0ed62a"),
"name" : "E",
"foundYear" : 1981,
"numberOfEmployee" : 60
}
{
"_id" : ObjectId("5f96342e13b68b37fd0ed62b"),
"name" : "F",
"foundYear" : 1981,
"numberOfEmployee" : 70
}
{
"_id" : ObjectId("5f9634a013b68b37fd0ed62c"),
"name" : "G",
"foundYear" : 1980,
"numberOfEmployee" : 70
}
// group by foundYear
> db.company.aggregate({$group:{_id:{foundYear:"$foundYear"},avg_numberOfEmployee:{$avg:"$numberOfEmployee"}}})
{ "_id" : { "foundYear" : 1981 }, "avg_numberOfEmployee" : 60 }
{ "_id" : { "foundYear" : 1980 }, "avg_numberOfEmployee" : 62.5 }
// group by foundYear, sort by numberOfEmployee
> db.company.aggregate([{$group:{_id:{foundYear:"$foundYear"},avg_numberOfEmployee:{$avg:"$numberOfEmployee"}}},{$sort:{avg_numberOfEmployee:-1}}])
{ "_id" : { "foundYear" : 1980 }, "avg_numberOfEmployee" : 62.5 }
{ "_id" : { "foundYear" : 1981 }, "avg_numberOfEmployee" : 60 }
List all name in group by using $group
// list all names by using $push
> db.company.aggregate([{$group: {_id:{foundYear:"$foundYear"},avgNumberOfEmployee:{$avg:"$numberOfEmployee"},names:{$push:"$name"}}},{$sort:{avgNumberOfEmployee:-1}}]).pretty()
{
"_id" : {
"foundYear" : 1980
},
"avgNumberOfEmployee" : 62.5,
"names" : [
"A",
"B",
"C",
"G"
]
}
{
"_id" : {
"foundYear" : 1981
},
"avgNumberOfEmployee" : 60,
"names" : [
"D",
"E",
"F"
]
}
it is a best practice to explicitly label values on which we group.
沒有留言:
張貼留言