使用Mongo聚合框架进行多组操作 [英] Multiple group operations using Mongo aggregation framework

问题描述

给出了一系列与调查和类别ID相关联的问题:

Given a set of questions that have linked survey and category id:

> db.questions.find().toArray();
[
    {
        "_id" : ObjectId("4fda05bc322b1c95b531ac25"),
        "id" : 1,
        "name" : "Question 1",
        "category_id" : 1,
        "survey_id" : 1,
        "score" : 5
    },
    {
        "_id" : ObjectId("4fda05cb322b1c95b531ac26"),
        "id" : 2,
        "name" : "Question 2",
        "category_id" : 1,
        "survey_id" : 1,
        "score" : 3
    },
    {
        "_id" : ObjectId("4fda05d9322b1c95b531ac27"),
        "id" : 3,
        "name" : "Question 3",
        "category_id" : 2,
        "survey_id" : 1,
        "score" : 4
    },
    {
        "_id" : ObjectId("4fda4287322b1c95b531ac28"),
        "id" : 4,
        "name" : "Question 4",
        "category_id" : 2,
        "survey_id" : 1,
        "score" : 7
    }
]

我可以通过以下方式找到类别平均值:

I can find the category average with:

db.questions.aggregate(
    { $group : {
        _id : "$category_id",
        avg_score : { $avg : "$score" }
    }
}
);

{
    "result" : [
        {
            "_id" : 1,
            "avg_score" : 4
        },
        {
            "_id" : 2,
            "avg_score" : 5.5
        }
    ],
    "ok" : 1
}

如何获取类别平均值的平均值(请注意，这与简单地对所有问题求平均值不同)?我假设我会进行多个分组操作，但失败了:

How can I get the average of category averages (note this is different than simply averaging all questions)? I would assume I would do multiple group operations but this fails:

> db.questions.aggregate(
...   { $group : {
...     _id : "$category_id",
...     avg_score : { $avg : "$score" },
...   }},
...   { $group : {
...     _id : "$survey_id",
...     avg_score : { $avg : "$score" },
...   }}
... );
{
    "errmsg" : "exception: the _id field for a group must not be undefined",
    "code" : 15956,
    "ok" : 0
}
>

推荐答案

重要的是要理解，Aggregate()参数中的操作形成了管道.这意味着流水线中任何元素的输入都是流水线中前一个元素生成的文档流.

It's important to understand that the operations in the argument to aggregate() form a pipeline. This meant that the input to any element of the pipeline is the stream of documents produced by the previous element in the pipeline.

在您的示例中，您的第一个查询创建了如下所示的文档管道:

In your example, your first query creates a pipeline of documents that look like this:

{
    "_id" : 2,
    "avg_score" : 5.5
},
{
    "_id" : 1,
    "avg_score" : 4
}

这意味着预告片的第二个元素是看到一系列文档，其中唯一的键是"_id"和"avg_score".此文档流中的键"category_id"和分数" 不再存在 .

This means that the second element of the pipline is seeing a series of documents where the only keys are "_id" and "avg_score". The keys "category_id" and "score" no longer exist in this document stream.

如果要在此流上进一步聚合，则必须使用在管道中此阶段看到的键进行聚合.由于要对平均值进行平均，因此需要为_id字段输入一个常数值，以便将所有输入文档分组为一个结果.

If you want to further aggregate on this stream, you'll have to aggregate using the keys that are seen at this stage in the pipeline. Since you want to average the averages, you need to put in a single constant value for the _id field, so that all of the input documents get grouped into a single result.

以下代码产生正确的结果:

The following code produces the correct result:

db.questions.aggregate(
    { $group : {
        _id : "$category_id",
        avg_score : { $avg : "$score" },
        }
    },
    { $group : {
        _id : "all",
        avg_score : { $avg : "$avg_score" },
        }
    }
);

运行时，将产生以下输出:

When run, it produces the following output:

 {
    "result" : [
        {
        "_id" : "all",
        "avg_score" : 4.75
        }
    ],
    "ok" : 1
 }

这篇关于使用Mongo聚合框架进行多组操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！