如何平均构建MongoDB管道以排序

Question

我正在尝试完成具有以下要求的管道：

设计和实施管道以显示名称，资金总数以及从资金回合中筹集的平均资金对于拥有资金回合数据的公司，不包括那些没有资金的公司。仅按平均筹集的数量显示前十名。

集合结构的一个示例如下：

"name" : "Wetpaint",
"funding_rounds" : [
                {
                        "id" : 888,
                        "round_code" : "a",
                        "source_url" : "http://seattlepi.nwsource.com/business/246734_wiki02.html",
                        "source_description" : "",
                        "raised_amount" : 5250000,
                        "raised_currency_code" : "USD",
                        "funded_year" : 2005,
                        "funded_month" : 10
                        "funded_day" : 1,
                        "investments" : [
                                {
                                        "company" : null,
                                        "financial_org" : {
                                                "name" : "Frazier Technology Ventures",
                                                "permalink" : "frazier-technology-ventures"
                                        },
                                        "person" : null
                                },
                                {
                                        "company" : null,
                                        "financial_org" : {
                                                "name" : "Trinity Ventures",
                                                "permalink" : "trinity-ventures"
                                        },
                                        "person" : null
                                }
                        ]
                }, ...

我使用了类似管道设计的一些结构，但它没有显示回合的总数，平均值不正确。

db.research.aggregate([ 
    {$project: {"_id": 0, "name": 1, "funding_rounds": 1, "total_rounds":             
    {"$size":  "$funding_rounds"}}}, 
    {$match: {"total_rounds" : {"$gte" : 1}}}, 
    {$unwind: "$funding_rounds"}, 
    {$group: {"_id": "$name", "average": {"$avg": "$funding_rounds.raised_amount"}}}, 
    {$project: {"Name": "$_id", "_id": 0, "Total_funding_rounds": "$total_rounds", "Average_amount_raised": "$average"}},
    {$sort: {"average": -1}}, 
    {$limit: 10}
])

结果应按平均筹集的金额订购，并排除没有任何资金的那些。

预期：

{ "Name" : "Cameron Health", "Average_amount_raised" : 60500000 }
{ "Name" : "Luminus Devices", "Average_amount_raised" : 33956593.25 }
{ "Name" : "Nellix", "Average_amount_raised" : 6500000 }
{ "Name" : "Purfresh", "Average_amount_raised" : 6270000 }
{ "Name" : "Treehouse", "Average_amount_raised" : 4847049.25 }
{ "Name" : "SolarPower Israel", "Average_amount_raised" : 1100000 }
{ "Name" : "DocASAP", "Average_amount_raised" : 799998 }
{ "Name" : "Getyoo", "Average_amount_raised" : 376666.6666666667 }

实际：

{ "Name" : "Nellix", "Average_amount_raised" : 6500000 }
{ "Name" : "Luminus Devices", "Average_amount_raised" : 33956593.25 }
{ "Name" : "Purfresh", "Average_amount_raised" : 6270000 }
{ "Name" : "Treehouse", "Average_amount_raised" : 4847049.25 }
{ "Name" : "SolarPower Israel", "Average_amount_raised" : 1100000 }
{ "Name" : "Getyoo", "Average_amount_raised" : 376666.6666666667 }
{ "Name" : "Embedster", "Average_amount_raised" : 0 }
{ "Name" : "EnteGreat", "Average_amount_raised" : 0 }
{ "Name" : "DocASAP", "Average_amount_raised" : 799998 }
{ "Name" : "Cameron Health", "Average_amount_raised" : 60500000 }

Answer 1

我的最终代码看起来像：

db.research.aggregate([
{$match: {"total_money_raised": {"$ne": "$0"}}}, 
{$project: {"_id": 0, "name": 1, "funding_rounds": 1}}, 
{$unwind: "$funding_rounds"}, 
{$group: {"_id": "$name", "average": {"$avg": "$funding_rounds.raised_amount"}, "total_rounds": {"$sum": 1}}}, 
{$sort: {"average": -1}}, {$project: {"Name": "$_id", "_id": 0, "total_rounds": "$total_rounds", "Average_amount_raised": "$average"}}, 
{$limit:10}
])

结果：

{ "total_rounds" : 4, "Name" : "Clearwire", "Average_amount_raised" : 1425000000 }
{ "total_rounds" : 1, "Name" : "Terra-Gen Power", "Average_amount_raised" : 1200000000 }
{ "total_rounds" : 1, "Name" : "Xerox", "Average_amount_raised" : 1100000000 }
{ "total_rounds" : 1, "Name" : "Blackberry", "Average_amount_raised" : 1000000000 }
{ "total_rounds" : 1, "Name" : "AOL", "Average_amount_raised" : 1000000000 }
{ "total_rounds" : 1, "Name" : "Telefonica", "Average_amount_raised" : 743000000 }
{ "total_rounds" : 6, "Name" : "DeNA", "Average_amount_raised" : 736000000 }
{ "total_rounds" : 1, "Name" : "Comcast", "Average_amount_raised" : 725000000 }
{ "total_rounds" : 1, "Name" : "FriendFinder Networks", "Average_amount_raised" : 551200000 }
{ "total_rounds" : 2, "Name" : "Sirius XM Radio, Inc.", "Average_amount_raised" : 527875000 }

Answer 2

尝试一下：

db.research.aggregate([
    {
        "$match": {
            "funding_rounds.0": {
                "$exists": true
            }
        }
    },
    {
        "$project": {
            "total_rounds": {
                "$size": "$funding_rounds"
            },
            "name": "$name",
            "Average_amount_raised": {
                "$avg": "$funding_rounds.raised_amount"
            },
            "_id": 0
        }
    },
    {
        "$sort": {
            "Average_amount_raised": -1
        }
    }
])

您可以看到它在这里工作：https：//mongoplayground.net/p/lq2q1atfdzu

对应的C＃代码：

using MongoDB.Entities;
using System.Linq;
namespace StackOverflow
{
    public class Program
    {
        public class Company : Entity
        {
            public string name { get; set; }
            public FundingRound[] funding_rounds { get; set; }
        }
        public class FundingRound
        {
            public int raised_amount { get; set; }
        }
        private static void Main(string[] args)
        {
            new DB("test");
            (new[] {
                new Company {
                    name = "Wet Paint",
                    funding_rounds = new[]
                    {
                        new FundingRound { raised_amount = 100},
                        new FundingRound { raised_amount = 100}
                    }
                }
            }).Save();
            var result = DB.Queryable<Company>()
                           .Where(c => c.funding_rounds.Count() > 0)
                           .Select(c => new
                           {
                               total_rounds = c.funding_rounds.Count(),
                               c.name,
                               Average_amount_raised = c.funding_rounds.Average(r => r.raised_amount)
                           })
                           .OrderByDescending(x => x.Average_amount_raised)
                           .ToList();
        }
    }
}