MongoDB聚合$ divide计算字段 [英] MongoDB aggregation $divide computed fields

问题描述

我试图基于计算字段在MongoDB查询中计算百分比-不确定是否可行.我想做的是计算失败百分比:(failed count / total) * 100

I am trying to compute a percentage in a MongoDB query based on computed fields - not sure if this is possible or not. What I'd like to be able to do is calculate the failure percentage: (failed count / total) * 100

以下是一些示例文档:

    {
            "_id" : ObjectId("52dda5afe4b0a491abb5407f"),
            "type" : "build",
            "time" : ISODate("2014-01-20T22:39:43.880Z"),
            "data" : {
                    "buildNumber" : 30,
                    "buildResult" : "SUCCESS"
            }
    },
    {
            "_id" : ObjectId("52dd9fede4b0a491abb5407a"),
            "type" : "build",
            "time" : ISODate("2014-01-20T22:15:07.901Z"),
            "data" : {
                    "buildNumber" : 4,
                    "buildResult" : "FAILURE"
            }
    },
    {
            "_id" : ObjectId("52dda153e4b0a491abb5407b"),
            "type" : "build",
            "time" : ISODate("2014-01-20T22:21:07.790Z"),
            "data" : {
                    "buildNumber" : 118,
                    "buildResult" : "SUCCESS"
            }
    }

这是我要使用的查询.问题出在FailPercent/$ divide行中:

Here is the query I am trying to work with. The issue is in the FailPercent/$divide line:

db.col.aggregate([    
    { $match: { "data.buildResult" : { $ne : null } } },
    { $group: {          
        _id: {              
            month: { $month: "$time" },             
            day: { $dayOfMonth: "$time" },             
            year: { $year: "$time" },                       
        },         
        Aborted: { $sum: { $cond :  [{ $eq : ["$data.buildResult", "ABORTED"]}, 1, 0]} },
        Failure: { $sum: { $cond :  [{ $eq : ["$data.buildResult", "FAILURE"]}, 1, 0]} },
        Unstable: { $sum: { $cond : [{ $eq : ["$data.buildResult", "UNSTABLE"]}, 1, 0]} },
        Success: { $sum: { $cond :  [{ $eq : ["$data.buildResult", "SUCCESS"]}, 1, 0]} },
        Total: { $sum: 1 },
        FailPercent: { $divide: [ "Failure", "Total" ] }
    } },     
    { $sort: { "_id.year": 1, "_id.month": 1, "_id.day": 1 } } 
])

推荐答案

您几乎明白了.唯一需要做的更改就是您必须在附加的project阶段中计算FailPercent，因为总计只能在group阶段完成后才能使用.试试这个:

You almost got it. Only change that would be required is that you'll have to compute the FailPercent in an additional project phase, because the total is only available after the completion of the group phase. Try this:

db.foo.aggregate([    
    { $match: { "data.buildResult" : { $ne : null } } },
    { $group: {          
        _id: {              
            month: { $month: "$time" },             
            day: { $dayOfMonth: "$time" },             
            year: { $year: "$time" },                       
        },         
        Aborted: { $sum: { $cond :  [{ $eq : ["$data.buildResult", "ABORTED"]}, 1, 0]} },
        Failure: { $sum: { $cond :  [{ $eq : ["$data.buildResult", "FAILURE"]}, 1, 0]} },
        Unstable: { $sum: { $cond : [{ $eq : ["$data.buildResult", "UNSTABLE"]}, 1, 0]} },
        Success: { $sum: { $cond :  [{ $eq : ["$data.buildResult", "SUCCESS"]}, 1, 0]} },
        Total: { $sum: 1 }
    } }, 
    {$project:{Aborted:1, Failure:1, Unstable:1, Success:1, Total:1, FailPercent: { $divide: [ "$Failure", "$Total" ]}}},
    { $sort: { "_id.year": 1, "_id.month": 1, "_id.day": 1 } } 
])

这篇关于MongoDB聚合$ divide计算字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！