我有一个集合如下

{

    "_id" : ObjectId("553b2c740f12bb30f85bd41c"),
    "symbol" : "EUR/GBP",
    "order_id" : "PW_BarclaysTrades60530",
    "ticket_id" : "PW_BarclaysTrades.60530",
    "basketid" : "TESTBASKET-1428483828043",
    "date_sent" : ISODate("2015-04-07T18:30:00.000Z"),
    "destination" : "BarclaysTrades",
    "order_price" : 0.0000000000000000,
    "order_quantity" : 4000000.0000000000000000,
    "order_type" : 1.0000000000000000,
    "parent_quantity" : 250000000.0000000000000000,
    "time_sent" : "09:03:48",
    "side" : 1,
    "tif" : "0",
    "execution_id" : 88939,
    "date_recvd" : ISODate("2015-04-07T18:30:00.000Z"),
    "exe_quantity" : 50000.0000000000000000,
    "time_recvd" : "09:03:48",
    "execution_price" : 2.5000000000000000,
    "execution_type" : 1
}

我想获取集合中每个目的地的 execution_price 大于 average(execution_price) 的文档

尝试按如下方式聚合:

db.orders_by_symbol.aggregate( [
{ $limit:300000 },
{ $match:{ destination: "PAPER" } },
{ $group:{_id:{Destination:"$destination"},avg_exec_price:  
              {$avg:"$execution_price"} ,"data":{"$push": "$$ROOT"}}},
{$unwind:"$data"},
{$match:{execution_price:{$ne: "$avg_exec_price"}}},
{$project:{_id:0,symbol:"$data.symbol",destination:"$data.destination",
          execution_id:"$data.execution_id",
          exec_price:"$data.execution_price",
         avg_ex_price:"$avg_exec_price"}}], 
{allowDiskUse:true})

得到如下结果

{

    "result" : [ 
        {
            "symbol" : "EUR/GBP",

            "destination" : "PAPER",
            "execution_id" : 89109,
            "exec_price" : 6.5000000000000000,
            "avg_ex_price" : 95.0747920857049140
        }, 
        {
            "symbol" : "EUR/GBP",
            "destination" : "PAPER",
            "execution_id" : 89110,
            "exec_price" : 6.0000000000000000,
            "avg_ex_price" : 95.0747920857049140
        }, 
        {
            "symbol" : "EUR/GBP",
            "destination" : "PAPER",
            "execution_id" : 89111,
            "exec_price" : 6.5000000000000000,
            "avg_ex_price" : 95.0747920857049140
        }

但是，当我将“$ne”运算符更改为“$gt”时，不会产生任何结果。 exec_price 和 avg_ex_price 都是双数据类型。不确定为什么它没有按预期工作。

最佳答案

使用 MongoDB Server 3.6 及更新版本:

var pipeline = [
    { "$match": { "destination": "PAPER" } },
    { "$facet": {
        "average": [
            { "$group": {
                "_id": null,
                "avg_exec_price": { "$avg": "$execution_price" }
            } }
        ],
        "data": [
            { "$project": { 
                "_id": 0,
                "symbol": 1,
                "destination": 1,
                "execution_id": 1,
                "execution_price": 1
            } }                   
        ]
    } },
    { "$addFields": {
        "average": { "$arrayElemAt": ["$average", 0] }
    } },
    { "$addFields": {
        "data": { 
            "$filter" : {
                "input": {
                    "$map": {
                        "input": "$data",
                        "as": "el",
                        "in": {
                            "symbol": "$$el.symbol",
                            "destination": "$$el.symbol",
                            "execution_id": "$$el.symbol",
                            "exec_price": "$$el.execution_price",
                            "avg_exec_price": "$average.avg_exec_price"
                        }
                    }
                },
                "as": "doc",
                "cond": {
                    "$gt" : [ 
                        "$$doc.exec_price", 
                        "$$doc.avg_exec_price"
                    ]
                }
            }
        }
    } },  
    { "$unwind": "$data" },  
    { "$replaceRoot": {  "newRoot": "$data" } }
];

对于不支持上述运算符和管道的 MongoDB 版本，请使用 $project运算符创建一个附加字段，通过 $gt 存储两个字段的比较聚合运算符:

var pipeline = [
    { "$match": {
        "destination": "PAPER"
    } },
    { "$group": {
        "_id": null,
        "avg_exec_price": { "$avg": "$execution_price" },
        "data": { "$addToSet": "$$ROOT" }
    } },
    { "$unwind": "$data" },
    { "$project": { 
        "_id": 0,
        "data": 1,
        "avg_exec_price": 1,            
        "isGreaterThanAverage": { 
            "$gt" : [ "$data.execution_price", "$avg_exec_price" ] 
        }
    } },    
    { "$match": {            
        "isGreaterThanAverage": true
    } },
    { "$project": { 
        "_id": 0,
        "symbol": "$data.symbol",
        "destination": "$data.destination",
        "execution_id": "$data.execution_id",
        "exec_price": "$data.execution_price",
        "avg_ex_price": "$avg_exec_price"
    } }
];

现在要测试上述聚合，假设您有以下最小测试用例集合:

db.test.insert([{
    "symbol" : "EUR/GBP",    
    "destination" : "PAPER",    
    "execution_id" : 88939,    
    "execution_price" : 1.8
},
{
    "symbol" : "EUR/GBP",    
    "destination" : "PAPER",    
    "execution_id" : 88921,    
    "execution_price" : 6.8
},
{
    "symbol" : "USD/GBP",    
    "destination" : "foo",    
    "execution_id" : 88955,    
    "execution_price" : 3.1
},
{
    "symbol" : "AUD/GBP",    
    "destination" : "PAPER",    
    "execution_id" : 88941,    
    "execution_price" : 1.1
},
{
    "symbol" : "EUR/GBP",    
    "destination" : "PAPER",    
    "execution_id" : 88907,    
    "execution_price" : 9.4
}]);

运行上面的聚合

db.test.aggregate(pipeline);

将产生结果:

/* 0 */
{
    "result" : [ 
        {
            "symbol" : "EUR/GBP",
            "destination" : "PAPER",
            "execution_id" : 88907,
            "exec_price" : 9.4,
            "avg_ex_price" : 4.775
        }, 
        {
            "symbol" : "EUR/GBP",
            "destination" : "PAPER",
            "execution_id" : 88921,
            "exec_price" : 6.8,
            "avg_ex_price" : 4.775
        }
    ],
    "ok" : 1
}

关于MongoDB 聚合 $group 和 $match 组结果，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/29958579/