首页 文章

Mongodb同时聚合(计数)多个字段

提问于
浏览
12

我有文件看起来像这样:

{
    "_id" : "someuniqueeventid",
    "event" : "event_type_1",
    "date" : ISODate("2014-01-14T00:00:00Z"),
}

我想按“ event ”进行分组,并计算一周中每一天发生的每种事件类型的数量 . 基本上,我希望得到类似的东西:

{
    "_id": "event_type_1",
    "1": "number of event_type_1 for Monday",
    "2": "number of event_type_1 for Tuesday",
    ...
},
{
    "_id": "event_type_2",
    ...
}

不幸的是,我坚持:

db.data.aggregate([ {$project: {date_of_week: {$dayOfWeek: "$date"}, event: "$event"}}, 
                    {$group: {_id: "$event", .... } ])

有任何想法吗?

2 回答

  • 0

    聚合框架不会根据数据创建密钥,也不应该这样做,因为"data"不是密钥而是实际数据,因此您应该坚持使用该模式 .

    这意味着你基本上可以这样做:

    db.data.aggregate([
        { "$group": {
            "_id": {
                "event_type": "$event",
                "day": { "$dayOfWeek": "$date" }
            },
            "count": { "$sum": 1 } 
        }}
    ])
    

    这将计算每个事件每周的每周发生次数,尽管在输出中有多个文档,但每个事件很容易更改为单个文档:

    db.data.aggregate([
        { "$group": {
            "_id": {
                "event_type": "$event",
                "day": { "$dayOfWeek": "$date" }
            },
            "count": { "$sum": 1 } 
        }},
        { "$group": {
            "_id": "$_id.event_type",
            "days": { "$push": { "day": "$day", "count": "$count" } }
        }}
    ])
    

    这是一个数组形式,但它仍然保留您想要的结果 .

    如果你真的想做你的确切形式,那么你想做这样的事情:

    db.data.aggregate([
        { "$group": {
            "_id": "$event",
            "1": {
                "$sum": {
                    "$cond": [
                        { "$eq": [{ "$dayOfWeek": "$date" }, 1 ] },
                        1,
                        0
                    ]
                }
            },
            "2": {
                "$sum": {
                    "$cond": [
                        { "$eq": [{ "$dayOfWeek": "$date" }, 2 ] },
                        1,
                        0
                    ]
                }
            },
            "3": {
                "$sum": {
                    "$cond": [
                        { "$eq": [{ "$dayOfWeek": "$date" }, 3 ] },
                        1,
                        0
                    ]
                }
            },
            "4": {
                "$sum": {
                    "$cond": [
                        { "$eq": [{ "$dayOfWeek": "$date" }, 4 ] },
                        1,
                        0
                    ]
                }
            },
            "5": {
                "$sum": {
                    "$cond": [
                        { "$eq": [{ "$dayOfWeek": "$date" }, 5 ] },
                        1,
                        0
                    ]
                }
            },
            "6": {
                "$sum": {
                    "$cond": [
                        { "$eq": [{ "$dayOfWeek": "$date" }, 6 ] },
                        1,
                        0
                    ]
                }
            },
            "7": {
                "$sum": {
                    "$cond": [
                        { "$eq": [{ "$dayOfWeek": "$date" }, 7 ] },
                        1,
                        0
                    ]
                }
            }
        }}
    )
    

    但这真的很长,所以恕我直言,我会坚持使用第一个或第二个解决方案,因为它们更短,更容易阅读 .

  • 16

    使用MongoDb 3.4.4和更新版本,您可以利用 $arrayToObject 运算符来获取计数 . 您需要运行以下聚合管道:

    db.data.aggregate([
        { 
            "$group": {
                "_id": {
                    "event": "$event",
                    "day": { "$substr": [ { "$dayOfWeek": "$date" }, 0, -1 ] }
                },
                "count": { "$sum": 1 }
            }
        },
        { 
            "$group": {
                "_id": "$_id.event",
                "counts": {
                    "$push": {
                        "k": "$_id.day",
                        "v": "$count"
                    }
                }
            }
        },
        { 
            "$project": {
                "counts": { "$arrayToObject": "$counts" }
            } 
        }    
    ])
    

相关问题