初学mongodb的聚合，略生疏，关于下面这道题题目：查询founded_year为2004含有5个和以上的funding_rounds,funding_rounds里的raised_amount之和的平均值最小问这个文档的name是啥文档大体结构(已略去无关字段)：我的解答如下(已验证结果是正确的)：现想问：我的这个写法是否很冗余，有没有更优化的聚合写法呢?因为初学，没接触太多聚合符，所以请求拓展
1 回答
烙印99
TA贡献1829条经验 获得超13个赞
我也是初学一周这样,不知道还有不有更好的写法
源数据
db.test3.insert([
{
"name" : "Wetpaint",
"founded_year" : 2004,
"funding_rounds" : [
{
"raised_amount" : 5250000
},
{
"raised_amount" : 9500000
},
{
"raised_amount" : 25000000
},
{
"raised_amount" : 9500000
},
{
"raised_amount" : 25000000
}
]
},{
"name" : "Wddddt",
"founded_year" : 2004,
"funding_rounds" : [
{
"raised_amount" : 5250000
},
{
"raised_amount" : 9500000
},
{
"raised_amount" : 25000000
}
]
},
{
"name" : "Wcccct",
"founded_year" : 2004,
"funding_rounds" : [
{
"raised_amount" : 520000
},
{
"raised_amount" : 900000
},
{
"raised_amount" : 2500000
},
{
"raised_amount" : 950000
},
{
"raised_amount" : 25000000
}
]
},{
"name" : "Wetaaat",
"founded_year" : 2004,
"funding_rounds" : [
{
"raised_amount" : 5250000
},
{
"raised_amount" : 9500000
},
{
"raised_amount" : 25000000
}
]
}
])
sql
db.getCollection('test3').aggregate([
{
$project: {
'name': 1,
'founded_year': 1,
'funding_rounds': 1,
'funding_rounds_size': {$size: '$funding_rounds'},
'funding_rounds_avg': {$avg: '$funding_rounds.raised_amount'}
}
},{
$match: {
'founded_year': 2004,
'funding_rounds_size': {$gte: 5}
}
},{
$sort: {
'funding_rounds_avg': 1
}
}
])
感觉这种查询方法会好点,虽然写的有点多,不过效率应该快点
db.getCollection('test3').aggregate([
{
$match: {
'founded_year': 2004
}
},{
$project: {
'name': 1,
'founded_year': 1,
'funding_rounds': 1,
'funding_rounds_size': {$size: '$funding_rounds'},
'funding_rounds_avg': {$avg: '$funding_rounds.raised_amount'}
}
},{
$match: {
'funding_rounds_size': {$gte: 5}
}
},{
$sort: {
'funding_rounds_avg': 1
}
}
])
添加回答
举报
0/150
提交
取消