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: 过滤结果集，只输出符合条件的文档。
$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
}
>
