Mongodb 同时聚合(计数)多个字段 [英] Mongodb aggregate (count) on multiple fields simultaneously

问题描述

我有这样的文档:

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

我想按event"分组并计算一周中每一天发生的每种事件类型的数量.基本上，我想得到类似的东西:

I want to group by "event" and count how many of each event type occured in each day of the week. Basically, I want to get something like:

{
    "_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", .... } ])

有什么想法吗?

推荐答案

聚合框架不会基于数据创建键，你也不应该这样做，因为数据"不是关键但实际上是数据，所以你应该坚持这个模式.

The aggregation framework won't create keys based on data, nor should you even be doing so as "data" is not a key but actually data, so you should stick to the pattern.

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

That means you can basically just do this:

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

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

And that will count the occurrences per day of week per event, albeit in multiple documents in the output, but this is easy to change to a single document per event:

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

这是一个数组形式，但它仍然保存着你想要的结果.

And that is in an array form, but it still holds the results you want.

如果你真的一心想要做你的确切形式，那么你想做这样的事情:

If you are really bent on doing your exact form then you want to do something like this:

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
                ]
            }
        }
    }}
)

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

But that is really long winded so IMHO I would stick with the first or maybe second solution as they are shorter and more easy to read.

这篇关于Mongodb 同时聚合(计数)多个字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！