mongo进阶笔记--Chapter 8: 聚合

Parameter	Details
pipeline	数组（数据聚合操作或阶段的序列）
options	文件（可选，仅当管道以数组形式存在时可用）

聚合操作处理数据记录并返回计算结果。聚合操作会将多个文档中的值组合在一起，并对分组数据执行各种操作，以返回单一结果。MongoDB 提供了三种执行聚合的方法：聚合管道、map-reduce 函数和单用途聚合方法。

摘自 Mongo 手册 https://docs.mongodb.com/manual/aggregation/

Section 8.1: 计数

如何获得借记和贷记交易的数量？一种方法是使用 count() 函数，如下所示。

> db.transactions.count({cr_dr : "D"});

> db.transactions.find({cr_dr : "D"}).length();

但如果事先不知道 cr_dr 的可能值怎么办？这时就需要使用聚合框架了。请看下面的聚合查询。

> db.transactions.aggregate(
    [
        {
            $group : {
                _id : '$cr_dr', // group by type of transaction
                // Add 1 for each document to the count for this type of transaction
                count : {$sum : 1}
            }
        }
    ]
);

结果是

{
    "_id" : "C",
    "count" : 3
}
{
    "_id" : "D",
    "count" : 5
}

Section 8.2: 求和

如何获取金额总和？请看下面的汇总查询。

> db.transactions.aggregate(
    [
        {
            $group : {
                _id : '$cr_dr',
                count : {$sum : 1}, //counts the number
                totalAmount : {$sum : '$amount'} //sums the amount
            }
        }
    ]
);

结果是

{
    "_id" : "C",
    "count" : 3.0,
    "totalAmount" : 120.0
}
{
    "_id" : "D",
    "count" : 5.0,
    "totalAmount" : 410.0
}

另一个版本是金额和费用的总和。

> db.transactions.aggregate(
    [
        {
            $group : {
                _id : '$cr_dr',
                count : {$sum : 1},
                totalAmount : {$sum : { $sum : ['$amount', '$fee']}}
            }
        }
    ]
);

结果是

{
    "_id" : "C",
    "count" : 3.0,
    "totalAmount" : 128.0
}
{
    "_id" : "D",
    "count" : 5.0,
    "totalAmount" : 422.0
}

Section 8.3: 平均值

如何获取借记和贷记交易的平均金额？

> db.transactions.aggregate(
    [
        {
            $group : {
                _id : '$cr_dr', // group by type of transaction (debit or credit)
                count : {$sum : 1}, // number of transaction for each type
                totalAmount : {$sum : { $sum : ['$amount', '$fee']}}, // sum
                averageAmount : {$avg : { $sum : ['$amount', '$fee']}} // average
            }
        }
    ]
)

结果是

{
    "_id" : "C", // Amounts for credit transactions
    "count" : 3.0,
    "totalAmount" : 128.0,
    "averageAmount" : 40.0
}
{
    "_id" : "D", // Amounts for debit transactions
    "count" : 5.0,
    "totalAmount" : 422.0,
    "averageAmount" : 82.0
}

Section 8.4: 数组的操作

当你想处理数组中的数据项时，首先需要解卷数组。解卷操作会为数组中的每个条目创建一个文档。当你拥有大量文件和大型数组时，你会看到文件数量激增。

{ "_id" : 1, "item" : "myItem1", sizes: [ "S", "M", "L"] }
{ "_id" : 2, "item" : "myItem2", sizes: [ "XS", "M", "XL"] }

db.inventory.aggregate( [ { $unwind : "$sizes" }] )

一个重要的注意事项是，当文档不包含数组时，数组将丢失。从 mongo 3.2 及以上版本开始，增加了一个解卷选项 “preserveNullAndEmptyArrays”。该选项可确保在数组丢失时保留文档。

{ "_id" : 1, "item" : "myItem1", sizes: [ "S", "M", "L"] }
{ "_id" : 2, "item" : "myItem2", sizes: [ "XS", "M", "XL"] }
{ "_id" : 3, "item" : "myItem3" }

db.inventory.aggregate( [ { $unwind : { path: "$sizes", includeArrayIndex: "arrayIndex" } }] )

Section 8.5: 对工作和学习有用的聚合查询示例

聚合用于在 mongo 查询中执行复杂的数据搜索操作，而这些操作在普通的 “查找 ”查询中是无法完成的。创建一些虚拟数据：

db.employees.insert({"name":"Adma","dept":"Admin","languages":["german","french","english","hindi"],"age":30, "totalExp":10});
db.employees.insert({"name":"Anna","dept":"Admin","languages":["english","hindi"],"age":35,"totalExp":11});
db.employees.insert({"name":"Bob","dept":"Facilities","languages":["english","hindi"],"age":36,"totalExp":14});
db.employees.insert({"name":"Cathy","dept":"Facilities","languages":["hindi"],"age":31,"totalExp":4});
db.employees.insert({"name":"Mike","dept":"HR","languages":["english", "hindi","spanish"],"age":26, "totalExp":3});
db.employees.insert({"name":"Jenny","dept":"HR","languages":["english", "hindi","spanish"],"age":25, "totalExp":3});

按主题举例：

match： 用于匹配文档（如 SQL where 子句）

db.employees.aggregate([{$match:{dept:"Admin"}}]);
Output:
{ "_id" : ObjectId("54982fac2e9b4b54ec384a0d"), "name" : "Adma", "dept" : "Admin", "languages" : [
"german", "french", "english", "hindi" ], "age" : 30, "totalExp" : 10 }
{ "_id" : ObjectId("54982fc92e9b4b54ec384a0e"), "name" : "Anna", "dept" : "Admin", "languages" : [
"english", "hindi" ], "age" : 35, "totalExp" : 11 }

prohect： 用于填充特定字段的值

除非指定禁用，否则项目阶段将自动包含 _id 字段。

db.employees.aggregate([{$match:{dept:"Admin"}}, {$project:{"name":1, "dept":1}}]);
Output:
{ "_id" : ObjectId("54982fac2e9b4b54ec384a0d"), "name" : "Adma", "dept" : "Admin" }
{ "_id" : ObjectId("54982fc92e9b4b54ec384a0e"), "name" : "Anna", "dept" : "Admin" }
db.employees.aggregate({$project: {'_id':0, 'name': 1}})
Output:
{ "name" : "Adma" }
{ "name" : "Anna" }
{ "name" : "Bob" }
{ "name" : "Cathy" }
{ "name" : "Mike" }
{ "name" : "Jenny" }

Group: $group用于按特定字段对文档进行分组，此处按 “dept ”字段的值对文档进行分组。另一个有用的功能是可以按空进行分组，这意味着所有文档都将汇总为一个文档。

db.employees.aggregate([{$group:{"_id":"$dept"}}]);
{ "_id" : "HR" }
{ "_id" : "Facilities" }
{ "_id" : "Admin" }
db.employees.aggregate([{$group:{"_id":null, "totalAge":{$sum:"$age"}}}]);
Output:
{ "_id" : null, "noOfEmployee" : 183 }

Sum: $sum 用于对组内的值进行计数或求和。

db.employees.aggregate([{$group:{"_id":"$dept", "noOfDept":{$sum:1}}}]);
Output:
{ "_id" : "HR", "noOfDept" : 2 }
{ "_id" : "Facilities", "noOfDept" : 2 }
{ "_id" : "Admin", "noOfDept" : 2 }

Average: 计算每组特定字段的平均值。

db.employees.aggregate([{$group:{"_id":"$dept", "noOfEmployee":{$sum:1},
"avgExp":{$avg:"$totalExp"}}}]);
Output:
{ "_id" : "HR", "noOfEmployee" : 2, "totalExp" : 3 }
{ "_id" : "Facilities", "noOfEmployee" : 2, "totalExp" : 9 }
{ "_id" : "Admin", "noOfEmployee" : 2, "totalExp" : 10.5 }

Minimum: 查找每个组中字段的最小值。

db.employees.aggregate([{$group:{"_id":"$dept", "noOfEmployee":{$sum:1},
"minExp":{$min:"$totalExp"}}}]);
Output:
{ "_id" : "HR", "noOfEmployee" : 2, "totalExp" : 3 }
{ "_id" : "Facilities", "noOfEmployee" : 2, "totalExp" : 4 }
{ "_id" : "Admin", "noOfEmployee" : 2, "totalExp" : 10 }

Maximum: 查找每个组中字段的最大值。

db.employees.aggregate([{$group:{"_id":"$dept", "noOfEmployee":{$sum:1},
"maxExp":{$max:"$totalExp"}}}]);
Output:
{ "_id" : "HR", "noOfEmployee" : 2, "totalExp" : 3 }
{ "_id" : "Facilities", "noOfEmployee" : 2, "totalExp" : 14 }
{ "_id" : "Admin", "noOfEmployee" : 2, "totalExp" : 11 }

从每组的第一个和最后一个文档中获取特定字段的值： 在文档结果排序时效果很好。

db.employees.aggregate([{$group:{"_id":"$age", "lasts":{$last:"$name"},
"firsts":{$first:"$name"}}}]);
Output:
{ "_id" : 25, "lasts" : "Jenny", "firsts" : "Jenny" }
{ "_id" : 26, "lasts" : "Mike", "firsts" : "Mike" }
{ "_id" : 35, "lasts" : "Cathy", "firsts" : "Anna" }
{ "_id" : 30, "lasts" : "Adma", "firsts" : "Adma" }

最小值和最大值：

db.employees.aggregate([{$group:{"_id":"$dept", "noOfEmployee":{$sum:1},
"maxExp":{$max:"$totalExp"}, "minExp":{$min: "$totalExp"}}}]);
Output:
{ "_id" : "HR", "noOfEmployee" : 2, "maxExp" : 3, "minExp" : 3 }
{ "_id" : "Facilities", "noOfEmployee" : 2, "maxExp" : 14, "minExp" : 4 }
{ "_id" : "Admin", "noOfEmployee" : 2, "maxExp" : 11, "minExp" : 10 }

Push 和 addToSet： Push 将组中每个文档的字段值添加到数组中，用于以数组格式预测数据。

db.employees.aggregate([{$group:{"_id":"dept", "arrPush":{$push:"$age"}, "arrSet":
{$addToSet:"$age"}}}]);
Output:
{ "_id" : "dept", "arrPush" : [ 30, 35, 35, 35, 26, 25 ], "arrSet" : [ 25, 26, 35, 30 ] }

Unwind： 用于为指定数组类型字段中的每个值创建多个内存文件，然后我们可以根据这些值进一步进行聚合。

db.employees.aggregate([{$match:{"name":"Adma"}}, {$unwind:"$languages"}]);
Output:
{ "_id" : ObjectId("54982fac2e9b4b54ec384a0d"), "name" : "Adma", "dept" : "HR", "languages" :
"german", "age" : 30, "totalExp" : 10 }
{ "_id" : ObjectId("54982fac2e9b4b54ec384a0d"), "name" : "Adma", "dept" : "HR", "languages" :
"french", "age" : 30, "totalExp" : 10 }
{ "_id" : ObjectId("54982fac2e9b4b54ec384a0d"), "name" : "Adma", "dept" : "HR", "languages" :
"english", "age" : 30, "totalExp" : 10 }
{ "_id" : ObjectId("54982fac2e9b4b54ec384a0d"), "name" : "Adma", "dept" : "HR", "languages" :
"hindi", "age" : 30, "totalExp" : 10 }

排序：

db.employees.aggregate([{$match:{dept:"Admin"}}, {$project:{"name":1, "dept":1}}, {$sort: {name:
1}}]);
Output:
{ "_id" : ObjectId("57ff3e553dedf0228d4862ac"), "name" : "Adma", "dept" : "Admin" }
{ "_id" : ObjectId("57ff3e5e3dedf0228d4862ad"), "name" : "Anna", "dept" : "Admin" }
db.employees.aggregate([{$match:{dept:"Admin"}}, {$project:{"name":1, "dept":1}}, {$sort: {name:
-1}}]);
Output:
{ "_id" : ObjectId("57ff3e5e3dedf0228d4862ad"), "name" : "Anna", "dept" : "Admin" }
{ "_id" : ObjectId("57ff3e553dedf0228d4862ac"), "name" : "Adma", "dept" : "Admin" }

略过：

db.employees.aggregate([{$match:{dept:"Admin"}}, {$project:{"name":1, "dept":1}}, {$sort: {name:
-1}}, {$skip:1}]);
Output:
{ "_id" : ObjectId("57ff3e553dedf0228d4862ac"), "name" : "Adma", "dept" : "Admin" }

限制：

db.employees.aggregate([{$match:{dept:"Admin"}}, {$project:{"name":1, "dept":1}}, {$sort: {name:
-1}}, {$limit:1}]);
Output:
{ "_id" : ObjectId("57ff3e5e3dedf0228d4862ad"), "name" : "Anna", "dept" : "Admin" }

预测中的比较运算符：

db.employees.aggregate([{$match:{dept:"Admin"}}, {$project:{"name":1, "dept":1, age: {$gt: ["$age",
30]}}}]);
Output:
{ "_id" : ObjectId("57ff3e553dedf0228d4862ac"), "name" : "Adma", "dept" : "Admin", "age" : false }
{ "_id" : ObjectId("57ff3e5e3dedf0228d4862ad"), "name" : "Anna", "dept" : "Admin", "age" : true }

匹配中的比较运算符：

db.employees.aggregate([{$match:{dept:"Admin", age: {$gt:30}}}, {$project:{"name":1, "dept":1}}]);
Output:
{ "_id" : ObjectId("57ff3e5e3dedf0228d4862ad"), "name" : "Anna", "dept" : "Admin" }

比较运算符列表： $cmp、$eq、$gt、$gte、$lt、$lte 和 $ne

预测中的布尔聚集操作符：

db.employees.aggregate([{$match:{dept:"Admin"}}, {$project:{"name":1, "dept":1, age: { $and: [ {$gt: [ "$age", 30 ] }, { $lt: [ "$age", 36 ] } ] }}}]);
Output:
{ "_id" : ObjectId("57ff3e553dedf0228d4862ac"), "name" : "Adma", "dept" : "Admin", "age" : false }
{ "_id" : ObjectId("57ff3e5e3dedf0228d4862ad"), "name" : "Anna", "dept" : "Admin", "age" : true }

匹配中的布尔聚合操作符：

db.employees.aggregate([{$match:{dept:"Admin", $and: [{age: { $gt: 30 }}, {age: {$lt: 36 }} ] }},
{$project:{"name":1, "dept":1, age: { $and: [ { $gt: [ "$age", 30 ] }, { $lt: [ "$age", 36 ] } ]
}}}]);
Output:
{ "_id" : ObjectId("57ff3e5e3dedf0228d4862ad"), "name" : "Anna", "dept" : "Admin", "age" : true }

布尔聚集操作符列表： $and、$or 和 $not。

完整引用： https://docs.mongodb.com/v3.2/reference/operator/aggregation/

Section 8.6: 匹配

如何编写查询，以获取所有部门平均年龄大于等于35岁且薪水小于等于 $70000 的员工？

为此，我们需要编写一个查询，以匹配工资小于或等于 $70000 的员工。然后添加聚合阶段，按部门对员工进行分组。然后添加一个累加器，其中包含一个名为 average_age 的字段，以便使用 $avg 累加器查找每个部门的平均年龄，并在现有的 $match 和 $group 聚合体下面添加另一个 $match 聚合体，这样我们就只能检索平均年龄大于或等于 35 的结果。

db.employees.aggregate([
    {"$match": {"salary": {"$lte": 70000}}},
    {"$group": {"_id": "$dept",
                "average_age": {"$avg": "$age"}
                }
    },
    {"$match": {"average_age": {"$gte": 35}}}
])

结果是

{
    "_id": "IT",
    "average_age": 31
}
{
    "_id": "Customer Service",
    "average_age": 34.5
}
{
    "_id": "Finance",
    "average_age": 32.5
}

Section 8.7: 获取样本数据

要从特定集合中获取随机数据，请参阅 $sample aggregation。

db.emplyees.aggregate({ $sample: { size:1 } })

其中 size 代表要选择的项目数。

Section 8.8: 删除集合中有重复字段的文档（重复计算）

请注意，allowDiskUse: true 选项是可选的，但它有助于减少内存不足的问题，因为如果集合规模较大，这种聚合操作可能会占用大量内存，所以我建议始终使用该选项。

var duplicates = [];
db.transactions.aggregate([
    { $group: {
    _id: { cr_dr: "$cr_dr"},
    dups: { "$addToSet": "$_id" },
    count: { "$sum": 1 }
    }
},
{ $match: {
count: { "$gt": 1 }
}}
],allowDiskUse: true}
)
.result
.forEach(function(doc) {
doc.dups.shift();
doc.dups.forEach( function(dupId){
duplicates.push(dupId);
}
)
})
// printjson(duplicates);
// Remove all duplicates in one go
db.transactions.remove({_id:{$in:duplicates}})

Section 8.9: 带聚合功能的左外连接 ( `$Lookup`)

let col_1 = db.collection('col_1');
let col_2 = db.collection('col_2');
col_1 .aggregate([
    { $match: { "_id": 1 } },
    {
        $lookup: {
            from: "col_2",
            localField: "id",
            foreignField: "id",
            as: "new_document"
        }
    }
],function (err, result){
    res.send(result);
});

该功能是在 mongodb 3.2 版本中新发布的，它为用户提供了一个将一个集合与另一个集合中的匹配属性连接起来的平台Mongodb $LookUp 文档

Section 8.10: 服务器聚合

Andrew Mao 的解决方案 Meteor 中的平均聚合查询

Meteor.publish("someAggregation", function (args) {
    var sub = this;
    // This works for Meteor 0.6.5
    var db = MongoInternals.defaultRemoteCollectionDriver().mongo.db;
    // Your arguments to Mongo's aggregation. Make these however you want.
    var pipeline = [
        { $match: doSomethingWith(args) },
        { $group: {
            _id: whatWeAreGroupingWith(args),
            count: { $sum: 1 }
        }}
    ];
    db.collection("server_collection_name").aggregate(
        pipeline,
        // Need to wrap the callback so it gets called in a Fiber.
        Meteor.bindEnvironment(
            function(err, result) {
                // Add each of the results to the subscription.
                _.each(result, function(e) {
                    // Generate a random disposable id for aggregated documents
                    sub.added("client_collection_name", Random.id(), {
                        key: e._id.somethingOfInterest,
                        count: e.count
                    });
                });
                sub.ready();
            },
            function(error) {
                Meteor._debug( "Error doing aggregation: " + error);
            }
        )
    );
});

Section 8.11: 服务器方法中的聚合

另一种聚合方法是使用 Mongo.Collection#rawCollection() 这只能在服务器上运行。

下面是一个可以在 Meteor 1.3 及更高版本中使用的示例：

Meteor.methods({
    'aggregateUsers'(someId) {
        const collection = MyCollection.rawCollection()
        const aggregate = Meteor.wrapAsync(collection.aggregate, collection)

        const match = { age: { $gte: 25 } }
        const group = { _id:'$age', totalUsers: { $sum: 1 } }

        const results = aggregate([
            { $match: match },
            { $group: group }
        ])

        return results
    }
})

Section 8.12: Java 和 Spring 示例

这是一个使用 Spring Data 在 MongoDB 中创建和执行聚合查询的示例代码。

try {
    MongoClient mongo = new MongoClient();
    DB db = mongo.getDB("so");
    DBCollection coll = db.getCollection("employees");
    //Equivalent to $match
    DBObject matchFields = new BasicDBObject();
    matchFields.put("dept", "Admin");
    DBObject match = new BasicDBObject("$match", matchFields);
    //Equivalent to $project
    DBObject projectFields = new BasicDBObject();
    projectFields.put("_id", 1);
    projectFields.put("name", 1);
    projectFields.put("dept", 1);
    projectFields.put("totalExp", 1);
    projectFields.put("age", 1);
    projectFields.put("languages", 1);
    DBObject project = new BasicDBObject("$project", projectFields);
    //Equivalent to $group
    DBObject groupFields = new BasicDBObject("_id", "$dept");
    groupFields.put("ageSet", new BasicDBObject("$addToSet", "$age"));
    DBObject employeeDocProjection = new BasicDBObject("$addToSet", new
    BasicDBObject("totalExp", "$totalExp").append("age", "$age").append("languages",
    "$languages").append("dept", "$dept").append("name", "$name"));
    groupFields.put("docs", employeeDocProjection);
    DBObject group = new BasicDBObject("$group", groupFields);
    //Sort results by age
    DBObject sort = new BasicDBObject("$sort", new BasicDBObject("age", 1));
    List<DBObject> aggregationList = new ArrayList<>();
    aggregationList.add(match);
    aggregationList.add(project);
    aggregationList.add(group);
    aggregationList.add(sort);

    AggregationOutput output = coll.aggregate(aggregationList);
    for (DBObject result : output.results()) {
        BasicDBList employeeList = (BasicDBList) result.get("docs");
        BasicDBObject employeeDoc = (BasicDBObject) employeeList.get(0);
        String name = employeeDoc.get("name").toString();
        System.out.println(name);
    }
}catch (Exception ex){
    ex.printStackTrace();
}

请参阅 JSON 格式的 “resultSet ”值，了解输出格式：

[{
    "_id": "Admin",
    "ageSet": [35.0, 30.0],
    "docs": [{
        "totalExp": 11.0,
        "age": 35.0,
        "languages": ["english", "hindi"],
        "dept": "Admin",
        "name": "Anna"
    }, {
        "totalExp": 10.0,
        "age": 30.0,
        "languages": ["german", "french", "english", "hindi"],
        "dept": "Admin",
        "name": "Adma"
    }]
}]

resultSet “包含每个组的一个条目，ageSet 包含该组每个员工的年龄列表，_id 包含用于分组的字段值，docs 包含该组每个员工的数据，可用于我们自己的代码和用户界面。

mongo进阶笔记--Chapter 8: 聚合

Section 8.1: 计数

Section 8.2: 求和

Section 8.3: 平均值

Section 8.4: 数组的操作

Section 8.5: 对工作和学习有用的聚合查询示例

Section 8.6: 匹配

Section 8.7: 获取样本数据

Section 8.8: 删除集合中有重复字段的文档（重复计算）

Section 8.9: 带聚合功能的左外连接 ( `$Lookup`)

Section 8.10: 服务器聚合

Section 8.11: 服务器方法中的聚合

Section 8.12: Java 和 Spring 示例

阅读量高的博客

关于pycharm连接gitlab需要令牌的操作步骤

virtualbox安装fnOS

qemu使用的简易教程

站点正式发布的首篇文章

Section 8.1: 计数

Section 8.2: 求和

Section 8.3: 平均值

Section 8.4: 数组的操作

Section 8.5: 对工作和学习有用的聚合查询示例

Section 8.6: 匹配

Section 8.7: 获取样本数据

Section 8.8: 删除集合中有重复字段的文档（重复计算）

Section 8.9: 带聚合功能的左外连接 ( $Lookup)

Section 8.10: 服务器聚合

Section 8.11: 服务器方法中的聚合

Section 8.12: Java 和 Spring 示例

阅读量高的博客

关于pycharm连接gitlab需要令牌的操作步骤

virtualbox安装fnOS

qemu使用的简易教程

站点正式发布的首篇文章

Section 8.9: 带聚合功能的左外连接 ( `$Lookup`)