在MongoDB中按条件分组 [英] Group By Condition in MongoDB

问题描述

我在MongoDB中有一系列文档(检查事件)，如下所示:

I have a series of documents (check events) in MongoDB that look like this:

{
    "_id" : ObjectId("5397a78ab87523acb46f56"),
    "inspector_id" : ObjectId("5397997a02b8751dc5a5e8b1"),
    "status" : 'defect',
    "utc_timestamp" : ISODate("2014-06-11T00:49:14.109Z")
}

{
    "_id" : ObjectId("5397a78ab87523acb46f57"),
    "inspector_id" : ObjectId("5397997a02b8751dc5a5e8b2"),
    "status" : 'ok',
    "utc_timestamp" : ISODate("2014-06-11T00:49:14.109Z")
}

我需要得到一个看起来像这样的结果集:

I need to get a result set that looks like this:

[
  {
    "date" : "2014-06-11",
    "defect_rate" : '.92' 
  },  
  {
    "date" : "2014-06-11",
    "defect_rate" : '.84' 
  }, 
]

换句话说，我需要获取每天的平均缺陷率.这可能吗?

In other words, I need to get the average defect rate per day. Is this possible?

推荐答案

您想要的是聚合框架:

db.collection.aggregate([
    { "$group": {
        "_id": {
            "year": { "$year": "$utc_timestamp" },
            "month": { "$month": "$utc_timestamp" },
            "day": { "$dayOfMonth": "$utc_timestamp" },
        },
        "defects": {
            "$sum": { "$cond": [
                { "$eq": [ "$status", "defect" ] },
                1,
                0
            ]}
        },
        "totalCount": { "$sum": 1 }
    }},
    { "$project": {
        "defect_rate": {
            "$cond": [
                { "$eq": [ "$defects", 0 ] },
                0,
                { "$divide": [ "$defects", "$totalCount" ] }
            ]
        }
    }}
])

因此，您首先需要使用日期聚合运算符进行分组，并获得totalCount特定日期的商品数量.在这里使用 $cond 运算符可以确定是否状态"实际上是否是缺陷，其结果是有条件的 $sum ，其中仅计算缺陷"值.

So first you group on the day using the date aggregation operators and get the totalCount of items on the given day. The use of the $cond operator here determines whether the "status" is actually a defect or not and the result is a conditional $sum where only the "defect" values are counted.

将这些内容每天分组后，您只需 $divide 结果，并通过 $cond 进行另一次检查确保您没有被零除.

Once those are grouped per day you simply $divide the result, with another check with $cond to make sure you are not dividing by zero.

这篇关于在MongoDB中按条件分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！