英文:
mongo group and sort by largest groups
问题
以下是您提供的内容的翻译：
假设我有以下的文件结构，其中我保存了员工、他们所在的城市以及他们所在的公司：
{
"name": "john",
"city": "milan",
"company": "apple"
},
{
"name": "nicole",
"city": "rome",
"company": "microsoft"
}
我试图实现的目标，最好在一个请求内，是为我提供按城市和公司分别排名前 n 的群组。我想要得到排名前 n 的城市和他们工作的排名前 n 的公司。
例如：
[
{
"milan": 23,
"rome": 21,
"new york": 18,
"berlin": 10
},
{
"facebook": 10,
"apple": 8,
"amazon": 8,
"google": 2
}
]
我可以使用简单的聚合管道和 $sum，但那会扫描整个集合，不会给我排名前（最受欢迎）的结果。
英文:
Say I have the following documents structure where I keep employees, their respective location, and company they work for:
{
"name": "john",
"city": "milan",
"company": "apple"
},
{
"name": "nicole",
"city": "rome",
"company": "microsoft"
}
What I'm trying to achieve, ideally within one request, is to give me top n groups individually across city and company. I want to have top n cities and top n companies they work for.
For exmaple:
[
{
"milan": 23,
"rome": 21,
"new york": 18,
"berlin": 10
},
{
"facebook": 10,
"apple": 8,
"amazon": 8,
"google": 2
}
]
What I can do is simple aggregation pipeline with $sum but that scans the whole collection and does not give me top (most popular) results.
答案1
得分: 2
你可以在这种情况下使用 $facet，正如@user20042973在评论中提到的。
我使用了 k 和 v 的格式，这样我就可以在以后使用 $arrayToObject 将其转换为对象。
db.collection.aggregate([
{
$facet: {
cities: [
{ $group: { _id: "$city", v: { $sum: 1 } } },
{ $sort: { v: -1 } },
{ $limit: n },
{ $project: { k: "$_id", v: "$v", _id: 0 } }
],
companies: [
{ $group: { _id: "$company", v: { $sum: 1 } } },
{ $sort: { v: -1 } },
{ $limit: n },
{ $project: { k: "$_id", v: "$v", _id: 0 } }
]
}
},
{ $project: { converted: [ { $arrayToObject: "$cities" }, { $arrayToObject: "$companies" } ] } },
{ $unwind: "$converted" },
{ $replaceRoot: { newRoot: "$converted" } }
])
英文:
you can use $facet in this case as mentioned in the comment by @user20042973
i used the k and v format so that I can use $arrayToObject on a later stage to convert it to an object
db.collection.aggregate([
{
$facet: {
cities: [
{ $group: { _id: "$city", v: { $sum: 1 } } },
{ $sort: { v: -1 } },
{ $limit: n },
{ $project: { k: "$_id", v: "$v", _id: 0 } }
],
companies: [
{ $group: { _id: "$company", v: { $sum: 1 } } },
{ $sort: { v: -1 } },
{ $limit: n },
{ $project: { k: "$_id", v: "$v", _id: 0 } }
]
}
},
{ $project: { converted: [ { $arrayToObject: "$cities" }, { $arrayToObject: "$companies" } ] } },
{ $unwind: "$converted" },
{ $replaceRoot: { newRoot: "$converted" } }
])
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论