javascript - 关于mongodb的聚合问题，如何优化呢

问题描述

问 题

初学mongodb的聚合，略生疏，关于下面这道题

题目：
查询founded_year为2004
含有5个和以上的funding_rounds,
funding_rounds里的raised_amount之和的平均值最小
问这个文档的name是啥

文档大体结构(已略去无关字段)：

{
    "_id" : ObjectId("52cdef7c4bab8bd675297d8a"),
    "name" : "Wetpaint",
    "founded_year" : 2005,
    "funding_rounds" : [ 
        {
            "raised_amount" : 5250000
        }, 
        {
            "raised_amount" : 9500000
        }, 
        {
            "raised_amount" : 25000000
        }
    ]
}

我的解答如下(已验证结果是正确的)：
现想问：我的这个写法是否很冗余，有没有更优化的聚合写法呢?
因为初学，没接触太多聚合符，所以请求拓展

db.getCollection('companies').aggregate([
{
    $match:{
        'founded_year':{$eq:2004}
    }
},
{
    $project:{
     
        'funding_rounds':'$funding_rounds',
        'funding_rounds_size':{$size:'$funding_rounds'}
    }
},
{
    $match:{
        'funding_rounds_size':{$gte:5}
    }
},
{
    $unwind:'$funding_rounds'
},
{
    $group:{
        _id:"$_id",
        'num':{$avg:'$funding_rounds.raised_amount'}
   }
},
{
    $sort:{
        'num':1
    }
}
])

解决方案

我也是初学一周这样,不知道还有不有更好的写法

源数据

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
    }
}
])

这篇关于javascript - 关于mongodb的聚合问题，如何优化呢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！