mongodbaggregatemapReduceandgroup
Aggregate
专注于为中小企业提供网站设计、成都网站制作服务,电脑端+手机端+微信端的三站合一,更高效的管理,为中小企业奇台免费做网站提供优质的服务。我们立足成都,凝聚了一批互联网行业人才,有力地推动了上千企业的稳健成长,帮助中小企业通过网站建设实现规模扩充和转变。
MongoDB中聚合(aggregate)主要用于处理数据(诸如统计平均值,求和等),并返回计算后的数据结果,类似sql语句中的 count(*)
语法如下:
db.collection.aggregate()
db.collection.aggregate(pipeline,options)
db.runCommand({
aggregate: "
pipeline: [
explain:
allowDiskUse:
cursor:
})
在使用aggregate实现聚合操作之前,我们首先来认识下几个常用的聚合操作符。
$project::可以对结果集中的键 重命名,控制键是否显示,对列进行计算。
$match: 过滤结果集,只输出符合条件的文档。
$skip: 在显示结果的时候跳过前几行,并返回余下的文档。
$sort: 对即将显示的结果集排序
$limit: 控制结果集的大小
$unwind:将文档中的某一个数组类型字段拆分成多条,每条包含数组中的一个值。
$geoNear:输出接近某一地理位置的有序文档。
$group: 分组,聚合,求和,平均数,最大值,最小值,第一个,最后一个,等
表达式 描述 实例
$sum 计算总和 db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$sum : "$likes"}}}])
$avg 计算平均值 db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$avg : "$likes"}}}])
$min 获取集合中所有文档对应值得最小值 db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$min : "$likes"}}}])
$max 获取集合中所有文档对应值得最大值 db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$max : "$likes"}}}])
$push 在结果文档中插入值到一个数组中 db.mycol.aggregate([{$group : {_id : "$by_user", url : {$push: "$url"}}}])
$addToSet在结果文档中插入值到一个数组中,但不创建副本 db.mycol.aggregate([{$group : {_id : "$by_user", url : {$addToSet : "$url"}}}])
$first 根据资源文档的排序获取第一个文档数据 db.mycol.aggregate([{$group : {_id : "$by_user", first_url : {$first : "$url"}}}])
$last 根据资源文档的排序获取最后一个文档数据 db.mycol.aggregate([{$group : {_id : "$by_user", last_url : {$last : "$url"}}}])
实例:
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 } > 分组前过滤数据,并排序,然后再分组计算 (本示例无意义)
Group
基本语法如下:
db.runCommand({group:{
ns:集合名称,
key:分组的键对象,
initial:初始化累加器,
$reduce:组分解器,
condition:条件,
finalize:组完成器}})
分组首先会按照key进行分组,每组的每个文档全要执行$reduce方法,该方法接收2 个参数:一个是组内本条记录,一个是累加器数据
实例:
按照部门分组,计算每个部门的工资总和,如下所示:
> db.runCommand( ... {group:{ns:"emp",key:{"department":true},initial:{salct:0}, ... $reduce:function(oriDoc,prev){ prev.salct+=oriDoc.salary} ... }} ... ) { "waitedMS" : NumberLong(0), "retval" : [ { "department" : "Sales", "salct" : 14000 }, { "department" : "HR", "salct" : 10500 }, { "department" : "Development", "salct" : 21500 }, { "department" : "Planning", "salct" : 5000 } ], "count" : NumberLong(8), "keys" : NumberLong(4), "ok" : 1 } > 统计每个部门的员工总量和工资总和,如下所示: > db.runCommand( {group:{ns:"emp",key:{"department":true},initial:{salct:0,count:0}, $reduce:function(oriDoc,prev){ prev.salct+=oriDoc.salary;prev.count+=1} }} ) { "waitedMS" : NumberLong(0), "retval" : [ { "department" : "Sales", "salct" : 14000, "count" : 2 }, { "department" : "HR", "salct" : 10500, "count" : 2 }, { "department" : "Development", "salct" : 21500, "count" : 3 }, { "department" : "Planning", "salct" : 5000, "count" : 1 } ], "count" : NumberLong(8), "keys" : NumberLong(4), "ok" : 1 } > 统计每个部门的员工总量、工资总和及平均值,如下所示: > db.runCommand( {group:{ns:"emp",key:{"department":true},initial:{salct:0,count:0,avg:0}, $reduce:function(oriDoc,prev){ prev.salct+=oriDoc.salary;prev.count+=1; prev.avg=(prev.salct/prev.count).toFixed(2) } }} ) { "waitedMS" : NumberLong(0), "retval" : [ { "department" : "Sales", "salct" : 14000, "count" : 2, "avg" : "7000.00" }, { "department" : "HR", "salct" : 10500, "count" : 2, "avg" : "5250.00" }, { "department" : "Development", "salct" : 21500, "count" : 3, "avg" : "7166.67" }, { "department" : "Planning", "salct" : 5000, "count" : 1, "avg" : "5000.00" } ], "count" : NumberLong(8), "keys" : NumberLong(4), "ok" : 1 } > 统计每个部门的最高工资是多少,如下所示: > db.runCommand( {group:{ns:"emp",key:{"department":true},initial:{salct:0}, $reduce:function(oriDoc,prev){ if(oriDoc.salary>prev.salct){prev.salct=oriDoc.salary}} }} ) { "waitedMS" : NumberLong(0), "retval" : [ { "department" : "Sales", "salct" : 8000 }, { "department" : "HR", "salct" : 6000 }, { "department" : "Development", "salct" : 8000 }, { "department" : "Planning", "salct" : 5000 } ], "count" : NumberLong(8), "keys" : NumberLong(4), "ok" : 1 } > 统计每个部门的最高工资,并对结果过滤,只显示大于5000的部门,如下所示: > db.runCommand( {group:{ns:"emp",key:{"department":true},initial:{salct:0}, $reduce:function(oriDoc,prev){ if(oriDoc.salary>prev.salct){prev.salct=oriDoc.salary}},condition:{salary:{$gt:5000}} }} ) { "waitedMS" : NumberLong(0), "retval" : [ { "department" : "Sales", "salct" : 8000 }, { "department" : "Development", "salct" : 8000 }, { "department" : "HR", "salct" : 6000 } ], "count" : NumberLong(6), "keys" : NumberLong(3), "ok" : 1 } > 将统计后的结果加上描述,如下所示: > db.runCommand( {group:{ns:"emp",key:{"department":true},initial:{salct:0}, ... $reduce:function(oriDoc,prev){ if(oriDoc.salary>prev.salct){prev.salct=oriDoc.salary}}, ... condition:{salary:{$gt:5000}}, ... finalize:function(prev){prev.salct="Department of the highest salary is "+prev.salct} ... }}) { "waitedMS" : NumberLong(0), "retval" : [ { "department" : "Sales", "salct" : "Department of the highest salary is 8000" }, { "department" : "Development", "salct" : "Department of the highest salary is 8000" }, { "department" : "HR", "salct" : "Department of the highest salary is 6000" } ], "count" : NumberLong(6), "keys" : NumberLong(3), "ok" : 1 } > 用函数格式化分组的键:如果集合中出现键Department和department同时存在,那么分组有点麻烦,解决方法如下: > db.emp.insert({ ... "_id":9,"ename":"sophie","age":28,"Department":"HR","salary":18000 ... }) WriteResult({ "nInserted" : 1 }) > db.emp.find() { "_id" : 1, "ename" : "tom", "age" : 25, "department" : "Sales", "salary" : 6000 } { "_id" : 2, "ename" : "eric", "age" : 24, "department" : "HR", "salary" : 4500 } { "_id" : 3, "ename" : "robin", "age" : 30, "department" : "Sales", "salary" : 8000 } { "_id" : 4, "ename" : "jack", "age" : 28, "department" : "Development", "salary" : 8000 } { "_id" : 5, "ename" : "Mark", "age" : 22, "department" : "Development", "salary" : 6500 } { "_id" : 6, "ename" : "marry", "age" : 23, "department" : "Planning", "salary" : 5000 } { "_id" : 7, "ename" : "hellen", "age" : 32, "department" : "HR", "salary" : 6000 } { "_id" : 8, "ename" : "sarah", "age" : 24, "department" : "Development", "salary" : 7000 } { "_id" : 9, "ename" : "sophie", "age" : 28, "Department" : "HR", "salary" : 18000 } > > db.runCommand( {group:{ns:"emp", ... $keyf:function(oriDoc){if(oriDoc.Department){return{department:oriDoc.Department}}else{return{department:oriDoc.department}}}, ... initial:{salct:0}, ... $reduce:function(oriDoc,prev){ if(oriDoc.salary>prev.salct){prev.salct=oriDoc.salary}}, ... condition:{salary:{$gt:5000}}, ... finalize:function(prev){prev.salct="Department of the highest salary is "+prev.salct} ... }} ) { "waitedMS" : NumberLong(0), "retval" : [ { "department" : "Sales", "salct" : "Department of the highest salary is 8000" }, { "department" : "Development", "salct" : "Department of the highest salary is 8000" }, { "department" : "HR", "salct" : "Department of the highest salary is 18000" } ], "count" : NumberLong(7), "keys" : NumberLong(3), "ok" : 1 } >
当前名称:mongodbaggregatemapReduceandgroup
当前链接:http://pwwzsj.com/article/jsggcj.html