我们可以使用group或mapReduce来实现分组和统计的功能,参考:
https://docs.mongodb.com/manual/reference/method/db.collection.group/
1.group测试:
db.order.insert({id:123,mount:500})
yooo:PRIMARY> db.order.find()
{ "_id" : ObjectId("5b387105c0298213b42c35de"), "id" : 123, "mount" : 500 }
{ "_id" : ObjectId("5b387113c0298213b42c35df"), "id" : 123, "mount" : 250 }
{ "_id" : ObjectId("5b387124c0298213b42c35e0"), "id" : 212, "mount" : 200 }
{ "_id" : ObjectId("5b387131c0298213b42c35e1"), "id" : 123, "mount" : 300 }
{ "_id" : ObjectId("5b387a62c0298213b42c35e2"), "id" : 212, "mount" : 275 }
yooo:PRIMARY> db.order.group(
... {
... key: {id:1}, ----以id字段进行分组
... reduce: function( doc, result ) {
... result.total += doc.mount; ---每分组统计mount
... result.count++; ---每分组统计文档个数
... },
... initial: { total : 0,count: 0 } ,
... finalize: function(result) {
... result.avg=result.total/result.count ---每分组的总mount/分组文档个数
... }
... }
... )
[
{ "id" : 123, "total" : 1050, "count" : 3, "avg" : 350 }
{ "id" : 212, "total" : 475, "count" : 2, "avg" : 237.5 }
]
实现了SQL语句同样的功能:
SELECT id ,SUM(mount) as total,COUNT(id) as count, (SUM(mount)/COUNT(id)) as avg FROM order GROUP BY id
2.mapReduce测试:
https://docs.mongodb.com/manual/tutorial/map-reduce-examples/
>分片集群环境需要用mapReduce不能使用group:
The db.collection.group() method does not work with sharded clusters.
① map:为映射函数,里面会调用emit(key,value),集合会按照指定的key进行映射分组
② reduce:为简化函数,会对map分组后的数据进行分组简化,在reduce(key,value)中
的key就是emit中的key,vlaue则为emit分组后的emit(value)的集合
③ mapReduce:是最后执行的函数了,参数为map,reduce和其他可选参数
1>分组并统计mount总和
var mf=function() {emit(this.id,this.mount);};
var rf=function(key, valuemount) {return Array.sum(valuemount); };
db.order.mapReduce(mf,rf, { out: "mrout" } )
yooo:PRIMARY> db.order.mapReduce(mf,rf, { out: "mrout" } )
{
"result" : "mrout", ---输出结果到mtout的集合中
"timeMillis" : 58,
"counts" : {
"input" : 5, ----输入的文档个数
"emit" : 5, ----emit函数调用次数
"reduce" : 2, ----reduce函数调用次数
"output" : 2 ----返回输出文档个数
},
"ok" : 1,
"operationTime" : Timestamp(1530441013, 4),
"$clusterTime" : {
"clusterTime" : Timestamp(1530441013, 4),
"signature" : {
"hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
"keyId" : NumberLong(0)
}
}
}
yooo:PRIMARY> db.mrout.find()
{ "_id" : 123, "value" : 1050 }
{ "_id" : 212, "value" : 475 }
2>分组并统计每组文档个数:
下面的写法结果都是一样:
var mf2=function(){emit(this.id,1)};
var rf2=function(key,value){var i=0;value.forEach(function(x){i+=x});return i;};
db.order.mapReduce(mf2,rf2, { out: "mrout2" } )
var mf3=function(){emit(this.id,1)};
var rf3=function(key, value) {return Array.sum(value); };
db.order.mapReduce(mf3,rf3, { out: "mrout3" } )
var mf7=function(){emit(this.id,{count:1})};
var rf7=function(key,value){
var result={num:0};
value.forEach(function(x){result.num+=x.count});
return result;
};
db.order.mapReduce(mf7,rf7, { out: "mrout7" } )
yooo:PRIMARY> db.mrout7.find()
{ "_id" : 123, "value" : { "num" : 3 } }
{ "_id" : 212, "value" : { "num" : 2 } }
3.以id字段分组,并统计每组mount总和和每组的文档个数:
var mf0=function(){
emit(this.id,{mount:this.mount,count:1})
};
map函数的结果是:
第一组:{key:123,values:[{mount:500,count:1},{mount:250,count:1},{mount:300,count:1}]}
第二组:{key:212,values:[{mount:200,count:1},{mount:275,count:1}}
var rf0=function(key,value){
var result={total:0,num:0};
for(var i=0;i<value.length;i++){
result.total+=value[i].mount;
result.num+=value[i].count;
}
return result;
};
var finalff0 = function (key, result) {
result.avg=result.total/result.num;
return result;
};
db.order.mapReduce(mf0,rf0, { out: "mrout0",finalize:finalff0 } )
yooo:PRIMARY> db.mrout0.find()
{ "_id" : 123, "value" : { "total" : 1050, "num" : 3, "avg" : 350 } }
{ "_id" : 212, "value" : { "total" : 475, "num" : 2, "avg" : 237.5 } }
至此mapReduce输出了和上面group一样分组统计结果。