是否可以使用MongoDB的聚合框架对多个列进行分组和求和? [英] Is it possible to group and sum multiple columns with MongoDB's aggregation framework?

问题描述

给出此MongoDB集合:

Given this MongoDB collection:

[
  { character: 'broquaint', race: 'Halfling', class: 'Hunter' },
  { character: 'broquaint', race: 'Halfling', class: 'Hunter' },
  { character: 'broquaint', race: 'Halfling', class: 'Rogue' },
  { character: 'broquaint', race: 'Naga',     class: 'Fighter' },
  { character: 'broquaint', race: 'Naga',     class: 'Hunter' }
]

我想获得每个种族和级别的计数，即

I would like to get a count of each race and class i.e

{
  race:  { 'Halfling': 3, 'Naga': 2 },
  class: { 'Hunter': 3, 'Rogue': 1, 'Fighter': 1 }
}

我一直在尝试使用聚合框架( 替换现有的地图/缩小)，但只能将其移到最远 作为获取组合的计数，即

And I've been trying to do this using the aggregation framework (to replace an existing map/reduce) but have only been able to get as far as getting counts for the combinations i.e

{ '_id': { race: 'Halfling', class: 'Hunter' },  count: 2 }
{ '_id': { race: 'Halfling', class: 'Rogue' }    count: 1 }
{ '_id': { race: 'Naga',     class: 'Fighter' }, count: 1 }
{ '_id': { race: 'Naga',     class: 'Hunter' },  count: 1 }

这很简单，可以通过编程将其减少到所需的数量 结果，但我希望能够将其留给MongoDB.

Which is simple enough to reduce programmatically to the desired result but I was hoping to be able to leave that to MongoDB.

作为参考，这是我到目前为止的代码:

For reference here's the code I have so far:

db.games.aggregate(
  { '$match': { character: 'broquaint' } },
  {
    '$group': {
      _id:   { race: '$race', background: '$background'},
      count: { '$sum': 1 }
    }
  }
)

所以问题是-鉴于我可以找到示例集合 完全通过MongoDB的聚合框架获得所需的输出?

So the question is - given that example collection can I arrive at my desired output purely through MongoDB's aggregation framework?

对于任何可能在此先表示感谢的帮助！

For any help that might be rendered many thanks in advance!

推荐答案

是的，您可以使用聚合框架来做到这一点.它不会很漂亮，但是它仍然会比使用mapreduce更快.

Yes, you can do this with aggregation framework. It won't be pretty, but then it'll still be much faster than with mapreduce...

简而言之(输出的格式与您提供的格式不同，但内容相同):

Here it is in a nutshell (output a different format than what you give but same content):

> group1 = {
    "$group" : {
        "_id" : "$race",
        "class" : {
            "$push" : "$class"
        },
        "count" : {
            "$sum" : 1
        }
    }
};
> unwind = { "$unwind" : "$class" };
> group2 = {
    "$group" : {
        "_id" : "$class",
        "classCount" : {
            "$sum" : 1
        },
        "races" : {
            "$push" : {
                "race" : "$_id",
                "raceCount" : "$count"
            }
        }
    }
};
> unwind2 = { "$unwind" : "$races" };
> group3 ={
    "$group" : {
        "_id" : 1,
        "classes" : {
            "$addToSet" : {
                "class" : "$_id",
                "classCount" : "$classCount"
            }
        },
        "races" : {
            "$addToSet" : "$races"
        }
    }
};
> db.races.aggregate(group1, unwind, group2, unwind2, group3);
{
    "result" : [
        {
            "_id" : 1,
            "classes" : [
                {
                    "class" : "Fighter",
                    "classCount" : 1
                },
                {
                    "class" : "Hunter",
                    "classCount" : 3
                },
                {
                    "class" : "Rogue",
                    "classCount" : 1
                }
            ],
            "races" : [
                {
                    "race" : "Naga",
                    "raceCount" : 2
                },
                {
                    "race" : "Halfling",
                    "raceCount" : 3
                }
            ]
        }
    ],
    "ok" : 1
}

这篇关于是否可以使用MongoDB的聚合框架对多个列进行分组和求和?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！