|
Aggregate MongoDB中聚合(aggregate)主要用于处理数据(诸如统计平均值,求和等)，并返回计算后的数据结果。有点类似sql语句中的 count(*) 语法如下: db.collection.aggregate() db.collection.aggregate(pipeline,options) db.runCommand({ aggregate: "<collection>", pipeline: [ <stage>, <...> ], explain: <boolean>, allowDiskUse: <boolean>, cursor: <document> })
在使用aggregate实现聚合操作之前,我们首先来认识下几个常用的聚合操作符。 $project:：可以对结果集中的键 重命名，控制键是否显示,对列进行计算。 $match: 过滤结果集 $group: 分组，聚合，求和，平均数，等 $skip: 在显示结果的时候跳过前几行 $sort: 对即将显示的结果集排序 $limit: 控制结果集的大小
例： db.createCollection("emp") db.emp.insert({_id:1,"ename":"tom","age":25,"department":"Sales","salary":6000}) db.emp.insert({_id:2,"ename":"eric","age":24,"department":"HR","salary":4500}) db.emp.insert({_id:3,"ename":"robin","age":30,"department":"Sales","salary":8000}) db.emp.insert({_id:4,"ename":"jack","age":28,"department":"Development","salary":8000}) db.emp.insert({_id:5,"ename":"Mark","age":22,"department":"Development","salary":6500}) db.emp.insert({_id:6,"ename":"marry","age":23,"department":"Planning","salary":5000}) db.emp.insert({_id:7,"ename":"hellen","age":32,"department":"HR","salary":6000}) db.emp.insert({_id:8,"ename":"sarah","age":24,"department":"Development","salary":7000})
> use company switched to db company > db.emp.aggregate( ... {$group:{_id:"$department",dpct:{$sum:1}}} ... ) { "_id" : "Development", "dpct" : 3 } { "_id" : "HR", "dpct" : 2 } { "_id" : "Planning", "dpct" : 1 } { "_id" : "Sales", "dpct" : 2 } > db.emp.aggregate( ... {$group:{_id:"$department",salct:{$sum:"$salary"},salavg:{$avg:"$salary"}}} ... ) { "_id" : "Development", "salct" : 21500, "salavg" : 7166.666666666667 } { "_id" : "HR", "salct" : 10500, "salavg" : 5250 } { "_id" : "Planning", "salct" : 5000, "salavg" : 5000 } { "_id" : "Sales", "salct" : 14000, "salavg" : 7000 } > db.emp.aggregate( ... {$match:{age:{$lt:25}}} ... ) { "_id" : 2, "ename" : "eric", "age" : 24, "department" : "HR", "salary" : 4500 } { "_id" : 5, "ename" : "Mark", "age" : 22, "department" : "Development", "salary" : 6500 } { "_id" : 6, "ename" : "marry", "age" : 23, "department" : "Planning", "salary" : 5000 } { "_id" : 8, "ename" : "sarah", "age" : 24, "department" : "Development", "salary" : 7000 } > db.emp.aggregate( ... {$match:{age:{$gt:25}}}, ... {$group:{_id:"$department",salct:{$sum:"$salary"},salavg:{$avg:"$salary"}}} ... ) { "_id" : "HR", "salct" : 6000, "salavg" : 6000 } { "_id" : "Development", "salct" : 8000, "salavg" : 8000 } { "_id" : "Sales", "salct" : 8000, "salavg" : 8000 } > db.emp.aggregate( ... {$group:{_id:"$department",salct:{$sum:"$salary"},salavg:{$avg:"$salary"}}}, ... {$match:{salavg:{$gt:6000}}} ... ) { "_id" : "Development", "salct" : 21500, "salavg" : 7166.666666666667 } { "_id" : "Sales", "salct" : 14000, "salavg" : 7000 } > > db.emp.aggregate( ... {$sort:{age:1}},{$limit:3} ... ) { "_id" : 5, "ename" : "Mark", "age" : 22, "department" : "Development", "salary" : 6500 } { "_id" : 6, "ename" : "marry", "age" : 23, "department" : "Planning", "salary" : 5000 } { "_id" : 2, "ename" : "eric", "age" : 24, "department" : "HR", "salary" : 4500 } > db.emp.aggregate( {$sort:{age:-1}},{$limit:3} ) { "_id" : 7, "ename" : "hellen", "age" : 32, "department" : "HR", "salary" : 6000 } { "_id" : 3, "ename" : "robin", "age" : 30, "department" : "Sales", "salary" : 8000 } { "_id" : 4, "ename" : "jack", "age" : 28, "department" : "Development", "salary" : 8000 } > db.emp.aggregate( {$sort:{age:-1}},{$skip:4} ) { "_id" : 2, "ename" : "eric", "age" : 24, "department" : "HR", "salary" : 4500 } { "_id" : 8, "ename" : "sarah", "age" : 24, "department" : "Development", "salary" : 7000 } { "_id" : 6, "ename" : "marry", "age" : 23, "department" : "Planning", "salary" : 5000 } { "_id" : 5, "ename" : "Mark", "age" : 22, "department" : "Development", "salary" : 6500 } > > db.emp.aggregate( {$project:{"姓名":"$ename","年龄":"$age","部门":"$department","工资":"$salary",_id:0}}) { "姓名" : "tom", "年龄" : 25, "部门" : "Sales", "工资" : 6000 } { "姓名" : "eric", "年龄" : 24, "部门" : "HR", "工资" : 4500 } { "姓名" : "robin", "年龄" : 30, "部门" : "Sales", "工资" : 8000 } { "姓名" : "jack", "年龄" : 28, "部门" : "Development", "工资" : 8000 } { "姓名" : "Mark", "年龄" : 22, "部门" : "Development", "工资" : 6500 } { "姓名" : "marry", "年龄" : 23, "部门" : "Planning", "工资" : 5000 } { "姓名" : "hellen", "年龄" : 32, "部门" : "HR", "工资" : 6000 } { "姓名" : "sarah", "年龄" : 24, "部门" : "Development", "工资" : 7000 } > db.emp.aggregate( {$project:{"姓名":"$ename","年龄":"$age","部门":"$department","工资":"$salary",_id:0}},{$match:{"工资":{$gt:6000}}}) { "姓名" : "robin", "年龄" : 30, "部门" : "Sales", "工资" : 8000 } { "姓名" : "jack", "年龄" : 28, "部门" : "Development", "工资" : 8000 } { "姓名" : "Mark", "年龄" : 22, "部门" : "Development", "工资" : 6500 } { "姓名" : "sarah", "年龄" : 24, "部门" : "Development", "工资" : 7000 } >
Map Reduce Map-Reduce是一种计算模型，简单的说就是将大批量的工作（数据）分解（MAP）执行，然后再将结果合并成最终结果（REDUCE）。 MongoDB提供的Map-Reduce非常灵活，对于大规模数据分析也相当实用。 以下是MapReduce的基本语法：
>db.collection.mapReduce( function() {emit(key,value);}, //map 函数 function(key,values) {return reduceFunction}, //reduce 函数 { out: collection, query: document, sort: document, limit: number } ) 使用 MapReduce 要实现两个函数 Map 函数和 Reduce 函数,Map 函数调用 emit(key, value), 遍历 collection 中所有的记录, 将key 与 value 传递给 Reduce 函数进行处理。 Map 函数必须调用 emit(key, value) 返回键值对。 参数说明: map ：映射函数 (生成键值对序列,作为 reduce 函数参数)。 reduce 统计函数，reduce函数的任务就是将key-values变成key-value，也就是把values数组变成一个单一的值value。。 out 统计结果存放集合 (不指定则使用临时集合,在客户端断开后自动删除)。 query 一个筛选条件，只有满足条件的文档才会调用map函数。（query。limit，sort可以随意组合） sort 和limit结合的sort排序参数（也是在发往map函数前给文档排序），可以优化分组机制 limit 发往map函数的文档数量的上限（要是没有limit，单独使用sort的用处不大）
> db.emp.mapReduce( function() { emit(this.department,1); }, function(key,values) { return Array.sum(values) }, { out:"depart_summary" } ).find() { "_id" : "Development", "value" : 3 } { "_id" : "HR", "value" : 2 } { "_id" : "Planning", "value" : 1 } { "_id" : "Sales", "value" : 2 } 利用内置的sum函数返回每个部门的人数
> db.emp.mapReduce( function() { emit(this.department,this.salary); }, function(key,values) { return Array.avg(values) }, { out:"depart_summary" } ).find() { "_id" : "Development", "value" : 7166.666666666667 } { "_id" : "HR", "value" : 5250 } { "_id" : "Planning", "value" : 5000 } { "_id" : "Sales", "value" : 7000 } 利用内置的avg函数返回每个部门的工资平均数
> db.emp.mapReduce( function() { emit(this.department,this.salary); }, function(key,values) { return Array.avg(values).toFixed(2) }, { out:"depart_summary" } ).find() { "_id" : "Development", "value" : "7166.67" } { "_id" : "HR", "value" : "5250.00" } { "_id" : "Planning", "value" : 5000 } { "_id" : "Sales", "value" : "7000.00" } > 保留两位小数
> db.emp.mapReduce( function() { emit(this.department,this.salary); }, function(key,values) { return Array.sum(values) }, { out:"depart_summary" } ).find() { "_id" : "Development", "value" : 21500 } { "_id" : "HR", "value" : 10500 } { "_id" : "Planning", "value" : 5000 } { "_id" : "Sales", "value" : 14000 } > 利用内置的sum函数返回每个部门的工资总和
> db.emp.mapReduce( function() { emit(this.department,{count:1}); }, function(key,values) { var sum=0; values.forEach(function(val){sum+=val.count}); return sum; }, { out:"depart_summary" } ).find() { "_id" : "Development", "value" : 3 } { "_id" : "HR", "value" : 2 } { "_id" : "Planning", "value" : { "count" : 1 } } { "_id" : "Sales", "value" : 2 } > 手工计算每个部门的员工总数
> db.emp.mapReduce( function() { emit(this.department,{salct:this.salary,count:1}); }, function(key,values) { var res={salct:0,sum:0}; values.forEach(function(val){res.sum+=val.count;res.salct+=val.salct}); return res; }, { out:"depart_summary" } ).find() { "_id" : "Development", "value" : { "salct" : 21500, "sum" : 3 } } { "_id" : "HR", "value" : { "salct" : 10500, "sum" : 2 } } { "_id" : "Planning", "value" : { "salct" : 5000, "count" : 1 } } { "_id" : "Sales", "value" : { "salct" : 14000, "sum" : 2 } } > 手工计算每个部门的员工总数和工资总数
> db.emp.mapReduce( function() { emit(this.department,{salct:this.salary,count:1}); }, function(key,values) { var res={salct:0,sum:0}; values.forEach(function(val){res.sum+=val.count;res.salct+=val.salct}); return res.salct/res.sum; }, { out:"depart_summary" } ).find() { "_id" : "Development", "value" : 7166.666666666667 } { "_id" : "HR", "value" : 5250 } { "_id" : "Planning", "value" : { "salct" : 5000, "count" : 1 } } { "_id" : "Sales", "value" : 7000 } > 手工计算每个部门的工资平均值
> db.emp.mapReduce( function() { emit(this.department,this.salary); }, function(key,values) { return Array.avg(values) }, { out:"depart_summary" } ).find({value:{$gt:5000}}) { "_id" : "Development", "value" : 7166.666666666667 } { "_id" : "HR", "value" : 5250 } { "_id" : "Sales", "value" : 7000 } 将分组计算后的值进行过滤显示，只显示工资平均数大于5000的部门
> db.emp.mapReduce( function() { emit(this.department,this.salary); }, function(key,values) { return Array.avg(values) }, { out:"depart_summary" } ).find({value:{$gt:5000}}).sort({value:1}) { "_id" : "HR", "value" : 5250 } { "_id" : "Sales", "value" : 7000 } { "_id" : "Development", "value" : 7166.666666666667 } 将分组计算后的值进行排序，默认为升序
> db.emp.mapReduce( function() { emit(this.department,this.salary); }, function(key,values) { return Array.avg(values) }, { out:"depart_summary" } ).find({value:{$gt:5000}}).sort({value:-1}) { "_id" : "Development", "value" : 7166.666666666667 } { "_id" : "Sales", "value" : 7000 } { "_id" : "HR", "value" : 5250 } > 将分组计算后的值进行排序，手工指定降序
> db.emp.mapReduce( function() { emit(this.department,this.salary); }, function(key,values) { return Array.avg(values) }, { out:"depart_summary" } ).find({value:{$gt:5000}}).sort({value:-1}).limit(2) { "_id" : "Development", "value" : 7166.666666666667 } { "_id" : "Sales", "value" : 7000 } > 将分组计算后的值进行降序排序后，取其中的两个值
> db.emp.mapReduce( function() { emit(this.department,{count:1}); }, function(key,values) { var sum=0; values.forEach(function(val){sum+=val.count}); return sum; }, { out:"depart_summary",query:{age:{$gt:25}} } ).find() { "_id" : "Development", "value" : { "count" : 1 } } { "_id" : "HR", "value" : { "count" : 1 } } { "_id" : "Sales", "value" : { "count" : 1 } } > 分组前过滤数据，然后再分组计算
> db.emp.mapReduce( function() { emit(this.department,{count:1}); }, function(key,values) { var sum=0; values.forEach(function(val){sum+=val.count}); return sum; }, { out:"depart_summary",query:{age:{$gt:22}},sort:{age:1} } ).find() { "_id" : "Development", "value" : 2 } { "_id" : "HR", "value" : 2 } { "_id" : "Planning", "value" : { "count" : 1 } } { "_id" : "Sales", "value" : 2 } > 分组前过滤数据，并排序，然后再分组计算 (本示例无意义)
| 
QQ群⑧：
窥视卡
雷达卡
发表于 2016-8-11 09:55:03
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡