MongoDB的聚合框架筛选(match)投影(project)分组(group)排序(sort)限制(limit)跳过(skip) mongodb
MongoDB的聚合框架，主要用来对集合中的文档进行变换和组合，从而对数据进行分析以加以利用。
聚合框架的基本思路是：采用多个构件来创建一个管道，用于对一连串的文档进行处理。
这些构件包括：
筛选(filtering:match)、投影(projecting:project)、
分组(grouping:group)、排序(sorting:sort)、限制(limiting:limit).跳过(skipping:skip)。
使用聚合框架的方式
db.集合.aggregate（构件1，构件2…）
注意：由于聚合的结果要返回到客户端，因此聚合结果必须限制在16M以内，这是MongoDB支持的最大响应消息的大小。
准备样例数据
for(var i=0;i<100;i++){
for(var j=0;j<4;j++){
db.scores.insert({"studentId":"s"+i,"course":"课程"+j,"score":Math.random()*100});
}
}
管道操作符简介
每个操作符接受一系列的文档，对这些文档做相应的处理，然后把转换后的文档作为结果传递给下一个操作符。最后一个操作符会将结果返回。
不同的管道操作符，可以按照任意顺序，任意个数组合在一起使用。
$match
用于对文档集合进行筛选，里面可以使用所有常规的查询操作符。通常会放置在管道最前面的位置，理由如下：
1：快速将不需要的文档过滤，减少后续操作的数据量
2：在投影和分组之前做筛选，查询可以使用索引
多个条件
db.scores.aggregate({$match:{score:{$gt:60,$lt:90},course:"kecheng2"}});
{ "_id" : ObjectId("5b90ded3146c07c65a0a862a"), "stuid" : "s0", "course" : "kecheng2", "score" : 83.3734825235373 }
{ "_id" : ObjectId("5b90ded3146c07c65a0a8652"), "stuid" : "s10", "course" : "kecheng2", "score" : 77.92121094890862 }
{ "_id" : ObjectId("5b90ded3146c07c65a0a8656"), "stuid" : "s11", "course" : "kecheng2", "score" : 67.72690280424726 }
{ "_id" : ObjectId("5b90ded3146c07c65a0a8676"), "stuid" : "s19", "course" : "kecheng2", "score" : 63.0804503701044 }
{ "_id" : ObjectId("5b90ded3146c07c65a0a8686"), "stuid" : "s23", "course" : "kecheng2", "score" : 65.55027259693675 }
{ "_id" : ObjectId("5b90ded3146c07c65a0a868a"), "stuid" : "s24", "course" : "kecheng2", "score" : 86.64454755814518 }
{ "_id" : ObjectId("5b90ded3146c07c65a0a8696"), "stuid" : "s27", "course" : "kecheng2", "score" : 79.98331604973653 }
{ "_id" : ObjectId("5b90ded3146c07c65a0a869e"), "stuid" : "s29", "course" : "kecheng2", "score" : 68.38454886253326 }
{ "_id" : ObjectId("5b90ded3146c07c65a0a86a6"), "stuid" : "s31", "course" : "kecheng2", "score" : 79.23617474142844 }
$project
用来从文档中提取字段，可以指定包含和排除字段，也可以重命名字段。管道操作符还可以使用表达式，以满足更复杂的需求。
当表的字段在操作符内引用的时候 需要使用 "$ziduan" 双引号包起来 ， 前置标识字段 或 新增的替换字段名称 不需要引号包起来
比如要将studentId改为sid，如下：
db.scores.aggregate({"$project":{"sid":"$studentId"}})
$project的数学表达式
比如给成绩集体加20分，如下：
db.scores.aggregate({"$project":{"newScore":{$add:["$score",20]}}})
支持的操作符和相应语法：
$add:[expr1[,expr2,…exprn]] 加 ， 可用是多个值 score:{$add:["$score",40]} score:{$add:["$score",40,10]}
$subtract:[expr1,expr2] 减 score:{$subtract:["$score",40]}
$multiply:[expr1[,expr2,…exprn]] 乘 score:{$multiply:["$score",2]}
$divice:[expr1,expr2] 除
$mod:[expr1,expr2] 取余
分数的值 比原来增加了 40 + 10
db.scores.aggregate({$project:{"_id":0,course:1,score:1,"stid":"$stuid",score:{$add:["$score",40,10]}}},{$sort:{score:-1}});
{ "course" : "kecheng3", "score" : 149.78304494770788, "stid" : "s85" }
{ "course" : "kecheng2", "score" : 149.66312264196068, "stid" : "s39" }
{ "course" : "kecheng0", "score" : 149.60487455191281, "stid" : "s45" }
{ "course" : "kecheng0", "score" : 149.46439996457752, "stid" : "s68" }
{ "course" : "kecheng1", "score" : 149.3113895333866, "stid" : "s95" }
{ "course" : "kecheng3", "score" : 149.07042481331212, "stid" : "s34" }
{ "course" : "kecheng1", "score" : 148.91261172456902, "stid" : "s98" }
$project的日期表达式
聚合框架包含了一些用于提取日期信息的表达式， 注意：这些只能操作日期型的字段，不能操作数据，使用示例：
$year、$month、$week、$dayOfMonth、$dayOfWeek、$dayOfYear、$hour、$minute、$second 。
{ "_id" : ObjectId("5b921df0520c0acfe87cc3e0"), "index" : 2, "time" : ISODate("2018-09-07T06:42:56.440Z") }
我们来分别展示其 字段的年份 与 月份
db.timetest.aggregate({$project:{showyear:{$year:"$time"},showmonth:{$month:"$time"}}});
{ "_id" : ObjectId("5b921df0520c0acfe87cc3e0"), "showyear" : 2018, "showmonth" : 9 }
$project的字符串表达式 ， 只能操作字符串 对于数组类型 ， 数据库存储为 double类型
$substr : [expr,开始位置,要取的字节个数] 字符串截取
$concat:[expr1[,expr2,…exprn]] 链接字符串【可用多个字符串】
$toLower:expr 转化为小写
$toUpper:expr 转化为大写
例如：{"$project":{"sid":{$concat:["$studentId","cc"]}}}
db.temp.find();
{ "_id" : ObjectId("5b8f3974dfb2a160dbb01a8c"), "name" : "www", "age" : 22, "addr" : "china tangshan lubei" }
{ "_id" : ObjectId("5b8f3983dfb2a160dbb01a8d"), "name" : "3111", "age" : 32, "addr" : "china tangshan lubei" }
{ "_id" : ObjectId("5b8f61b0914cf734f786d9e0"), "addr" : "666644rrrr" }
{ "_id" : ObjectId("5b8f6a75914cf734f786d9e1"), "item" : "boo4k", "qty" : 11, "fenshu" : 98 }
{ "_id" : ObjectId("5b8f982e4275a0cef716d145"), "name" : "www", "age" : 52, "addr" : "china" }
{ "_id" : ObjectId("5b8f997f4275a0cef716d146"), "name" : "ggg", "age" : 15 }
{ "_id" : ObjectId("5b8f99874275a0cef716d147"), "name" : "www", "age" : 15 }
{ "_id" : ObjectId("5b8fa0d4e23974df0f5530eb"), "fenshu" : [ 44, 55, 22, 88 ] }
对含有name属性的文档记录，字段值 前面加上 “start_” 后面加上 “_end”
db.temp.aggregate({$match:{name:{$exists:1}}}, {$project:{name:{$concat:["start_","$name","_end"]}}});
{ "_id" : ObjectId("5b8f3974dfb2a160dbb01a8c"), "name" : "start_www_end" }
{ "_id" : ObjectId("5b8f3983dfb2a160dbb01a8d"), "name" : "start_3111_end" }
{ "_id" : ObjectId("5b8f982e4275a0cef716d145"), "name" : "start_www_end" }
{ "_id" : ObjectId("5b8f997f4275a0cef716d146"), "name" : "start_ggg_end" }
{ "_id" : ObjectId("5b8f99874275a0cef716d147"), "name" : "start_www_end" }
下面这组测试， 我们可用发现 对于同一个属性的投影 前面的效果会被后面的效果所覆盖
db.temp.aggregate({$match:{name:{$exists:1}}}, {$project:{name:{$concat:["start_","$name","_end"]}}});
{ "_id" : ObjectId("5b8f3974dfb2a160dbb01a8c"), "name" : "start_www_end" } 此时name 属性为 start_name_end
{ "_id" : ObjectId("5b8f3983dfb2a160dbb01a8d"), "name" : "start_3111_end" }
{ "_id" : ObjectId("5b8f982e4275a0cef716d145"), "name" : "start_www_end" }
{ "_id" : ObjectId("5b8f997f4275a0cef716d146"), "name" : "start_ggg_end" }
{ "_id" : ObjectId("5b8f99874275a0cef716d147"), "name" : "start_www_end" }
> db.temp.aggregate({$match:{name:{$exists:1}}}, {$project:{name:{$concat:["start_","$name","_end"]},name:{$toUpper:"$name"}}});
{ "_id" : ObjectId("5b8f3974dfb2a160dbb01a8c"), "name" : "WWW" } 此时属性 被覆盖了，且不会在一个管道符 内进行 传递
{ "_id" : ObjectId("5b8f3983dfb2a160dbb01a8d"), "name" : "3111" }
{ "_id" : ObjectId("5b8f982e4275a0cef716d145"), "name" : "WWW" }
{ "_id" : ObjectId("5b8f997f4275a0cef716d146"), "name" : "GGG" }
{ "_id" : ObjectId("5b8f99874275a0cef716d147"), "name" : "WWW" }
> db.temp.aggregate({$match:{name:{$exists:1}}}, {$project:{newname:{$concat:["start_","$name","_end"]},name:{$toUpper:"$name"}}});
{ "_id" : ObjectId("5b8f3974dfb2a160dbb01a8c"), "newname" : "start_www_end", "name" : "WWW" } 我们为前面的属性 重命名展示出来， 则此时可用显示出两个值
{ "_id" : ObjectId("5b8f3983dfb2a160dbb01a8d"), "newname" : "start_3111_end", "name" : "3111" }
{ "_id" : ObjectId("5b8f982e4275a0cef716d145"), "newname" : "start_www_end", "name" : "WWW" }
{ "_id" : ObjectId("5b8f997f4275a0cef716d146"), "newname" : "start_ggg_end", "name" : "GGG" }
{ "_id" : ObjectId("5b8f99874275a0cef716d147"), "newname" : "start_www_end", "name" : "WWW" }
> db.temp.aggregate({$match:{name:{$exists:1}}}, {$project:{newname:{$concat:["start_","$name","_end"]},name:{$toUpper:"$newname"}}});
{ "_id" : ObjectId("5b8f3974dfb2a160dbb01a8c"), "newname" : "start_www_end", "name" : "" } 这个实验表示 数据在一个管道符内不会传递
{ "_id" : ObjectId("5b8f3983dfb2a160dbb01a8d"), "newname" : "start_3111_end", "name" : "" }
{ "_id" : ObjectId("5b8f982e4275a0cef716d145"), "newname" : "start_www_end", "name" : "" }
{ "_id" : ObjectId("5b8f997f4275a0cef716d146"), "newname" : "start_ggg_end", "name" : "" }
{ "_id" : ObjectId("5b8f99874275a0cef716d147"), "newname" : "start_www_end", "name" : "" }
db.temp.aggregate({$match:{name:{$exists:1}}}, {$project:{newname:{$concat:["start_","$name","_end"]},newname:{$toUpper:"$newname"}}});
{ "_id" : ObjectId("5b8f3974dfb2a160dbb01a8c"), "newname" : "" } 同事将name 修改为 newname 那么属性都被覆盖了 且无法传递
{ "_id" : ObjectId("5b8f3983dfb2a160dbb01a8d"), "newname" : "" }
{ "_id" : ObjectId("5b8f982e4275a0cef716d145"), "newname" : "" }
{ "_id" : ObjectId("5b8f997f4275a0cef716d146"), "newname" : "" }
{ "_id" : ObjectId("5b8f99874275a0cef716d147"), "newname" : "" }
$project的逻辑表达式
$cmp:[expr1,expr2] :比较两个表达式，0表示相等，正数前面的大，负数后面的大
db.temp.find()
{ "_id" : ObjectId("5b8f3974dfb2a160dbb01a8c"), "name" : "www", "age" : 22, "addr" : "china tangshan lubei" }
{ "_id" : ObjectId("5b8f3983dfb2a160dbb01a8d"), "name" : "3111", "age" : 32, "addr" : "china tangshan lubei" }
{ "_id" : ObjectId("5b8f61b0914cf734f786d9e0"), "addr" : "666644rrrr" }
{ "_id" : ObjectId("5b8f6a75914cf734f786d9e1"), "item" : "boo4k", "qty" : 11, "fenshu" : 98 }
{ "_id" : ObjectId("5b8f982e4275a0cef716d145"), "name" : "www", "age" : 52, "addr" : "china" }
{ "_id" : ObjectId("5b8f997f4275a0cef716d146"), "name" : "ggg", "age" : 15 }
{ "_id" : ObjectId("5b8f99874275a0cef716d147"), "name" : "www", "age" : 15 }
{ "_id" : ObjectId("5b8fa0d4e23974df0f5530eb"), "fenshu" : [ 44, 55, 22, 88 ] }
> db.temp.aggregate({$match:{name:{$exists:1}}}, {$project:{name:{$cmp:["$name","hh"]}}});
{ "_id" : ObjectId("5b8f3974dfb2a160dbb01a8c"), "name" : 1 }
{ "_id" : ObjectId("5b8f3983dfb2a160dbb01a8d"), "name" : -1 }
{ "_id" : ObjectId("5b8f982e4275a0cef716d145"), "name" : 1 }
{ "_id" : ObjectId("5b8f997f4275a0cef716d146"), "name" : -1 }
{ "_id" : ObjectId("5b8f99874275a0cef716d147"), "name" : 1 }
$strcasecmp:[string1,string2] :比较两个字符串，区分大小写，只对由罗马字符组成的字符串有效
> db.temp.aggregate({$match:{name:{$exists:1}}}, {$project:{name:{$strcasecmp:["$name","hh"]}}});
{ "_id" : ObjectId("5b8f3974dfb2a160dbb01a8c"), "name" : 1 }
{ "_id" : ObjectId("5b8f3983dfb2a160dbb01a8d"), "name" : -1 }
{ "_id" : ObjectId("5b8f982e4275a0cef716d145"), "name" : 1 }
{ "_id" : ObjectId("5b8f997f4275a0cef716d146"), "name" : -1 }
{ "_id" : ObjectId("5b8f99874275a0cef716d147"), "name" : 1 }
$eq、$ne、$gt、$gte、$lt、$lte ：[expr1,expr2] 等于 不等于 大于 大于等于 小于 小于等于
db.temp.aggregate({$match:{name:{$exists:1}}}, {$project:{name:{$eq:["$name","www"]}}}); 是否相等
{ "_id" : ObjectId("5b8f3974dfb2a160dbb01a8c"), "name" : true }
{ "_id" : ObjectId("5b8f3983dfb2a160dbb01a8d"), "name" : false }
{ "_id" : ObjectId("5b8f982e4275a0cef716d145"), "name" : true }
{ "_id" : ObjectId("5b8f997f4275a0cef716d146"), "name" : false }
{ "_id" : ObjectId("5b8f99874275a0cef716d147"), "name" : true }
db.temp.aggregate({$match:{name:{$exists:1}}}, {$project:{name:{$gt:["$name","hh"]}}});
{ "_id" : ObjectId("5b8f3974dfb2a160dbb01a8c"), "name" : true }
{ "_id" : ObjectId("5b8f3983dfb2a160dbb01a8d"), "name" : false }
{ "_id" : ObjectId("5b8f982e4275a0cef716d145"), "name" : true }
{ "_id" : ObjectId("5b8f997f4275a0cef716d146"), "name" : false }
{ "_id" : ObjectId("5b8f99874275a0cef716d147"), "name" : true }
$and、$or、$not :{ $and: [ { <expression1> }, { <expression2> } ] } 与 或者 否
db.temp.aggregate({$match:{name:{$exists:1}}}, {$project:{newname:{$concat:["$name","_new"]},name:{$and:[{$ne:["$name","bb"]},{$gte:["$name","ww"]}]}}});
{ "_id" : ObjectId("5b8f3974dfb2a160dbb01a8c"), "newname" : "www_new", "name" : true }
{ "_id" : ObjectId("5b8f3983dfb2a160dbb01a8d"), "newname" : "3111_new", "name" : false }
{ "_id" : ObjectId("5b8f982e4275a0cef716d145"), "newname" : "www_new", "name" : true }
{ "_id" : ObjectId("5b8f997f4275a0cef716d146"), "newname" : "ggg_new", "name" : false }
{ "_id" : ObjectId("5b8f99874275a0cef716d147"), "newname" : "www_new", "name" : true }
$cond:[booleanExpr,trueExpr,falseExpr]: 如果boolean表达式为true，返回true表达式，否则返回false表达式 【三元运算符类型，但只返回true、false】
db.temp.aggregate({$match:{name:{$exists:1}}}, {$project:{newname:{$concat:["$name","_new"]},name:{$cond:[{$ne:["$name","www"]},"dd","xx"]}}});
{ "_id" : ObjectId("5b8f3974dfb2a160dbb01a8c"), "newname" : "www_new", "name" : "xx" }
{ "_id" : ObjectId("5b8f3983dfb2a160dbb01a8d"), "newname" : "3111_new", "name" : "dd" }
{ "_id" : ObjectId("5b8f982e4275a0cef716d145"), "newname" : "www_new", "name" : "xx" }
{ "_id" : ObjectId("5b8f997f4275a0cef716d146"), "newname" : "ggg_new", "name" : "dd" }
{ "_id" : ObjectId("5b8f99874275a0cef716d147"), "newname" : "www_new", "name" : "xx" }
$ifNull:[expr,otherExpr]: 如果expr为null，返回otherExpr，否则返回expr 【三元运算符类似】
db.temp.aggregate({$match:{name:{$exists:1}}}, {$project:{newname:{$concat:["$name","_new"]},name:{$ifNull:["$name","hasnoname param"]}}});
{ "_id" : ObjectId("5b8f3974dfb2a160dbb01a8c"), "newname" : "www_new", "name" : "www" }
{ "_id" : ObjectId("5b8f3983dfb2a160dbb01a8d"), "newname" : "3111_new", "name" : "3111" }
{ "_id" : ObjectId("5b8f982e4275a0cef716d145"), "newname" : "www_new", "name" : "www" }
{ "_id" : ObjectId("5b8f997f4275a0cef716d146"), "newname" : "ggg_new", "name" : "ggg" }
{ "_id" : ObjectId("5b8f99874275a0cef716d147"), "newname" : "www_new", "name" : "www" }
> db.temp.aggregate({$match:{}}, {$project:{newname:{$concat:["$name","_new"]},name:{$ifNull:["$name","hasnoname param"]}}});
{ "_id" : ObjectId("5b8f3974dfb2a160dbb01a8c"), "newname" : "www_new", "name" : "www" }
{ "_id" : ObjectId("5b8f3983dfb2a160dbb01a8d"), "newname" : "3111_new", "name" : "3111" }
{ "_id" : ObjectId("5b8f61b0914cf734f786d9e0"), "newname" : null, "name" : "hasnoname param" }
{ "_id" : ObjectId("5b8f6a75914cf734f786d9e1"), "newname" : null, "name" : "hasnoname param" }
{ "_id" : ObjectId("5b8f982e4275a0cef716d145"), "newname" : "www_new", "name" : "www" }
{ "_id" : ObjectId("5b8f997f4275a0cef716d146"), "newname" : "ggg_new", "name" : "ggg" }
{ "_id" : ObjectId("5b8f99874275a0cef716d147"), "newname" : "www_new", "name" : "www" }
{ "_id" : ObjectId("5b8fa0d4e23974df0f5530eb"), "newname" : null, "name" : "hasnoname param" }
$group
用来将文档依据特定字段的不同值进行分组。选定了分组字段过后，就可以把这些字段传递给$group函数的“_id”字段了。例如：
{ $group: { _id: <expression>, <field1>: { <accumulator1> : <expression1> }, ... } }
db.scores.aggregate({“$group”:{“_id”:“$studentId”}}); 或者是 【一个条件】
db.scores.aggregate({"$group":{"_id":{"sid":"$studentId","score":"$score"}}}); 【多个条件】
$group支持的操作符
1：$sum:value ：对于每个文档，将value与计算结果相加
db.user.find()
{ "_id" : ObjectId("5b8660e8295c86cc5adba7cb"), "name" : "wj", "age" : 44 }
{ "_id" : ObjectId("5b8c8ae398cd08437710c6f9"), "name" : "ggg", "age" : 55 }
{ "_id" : ObjectId("5b8ca98a98cd08437710c6fa"), "name" : "jjj", "age" : 54, "addr" : "河北大街333号" }
{ "_id" : ObjectId("5b8caa8d98cd08437710c6fb"), "name" : "wwwkjfd", "age" : 22 }
{ "_id" : ObjectId("5b8caa9d98cd08437710c6fc"), "name" : "w543rr", "age" : 32 }
{ "_id" : ObjectId("5b8cc36198cd08437710c6fd"), "name" : "wj", "age" : 33, "addr" : "unit states" }
{ "_id" : ObjectId("5b8cc3d798cd08437710c6fe"), "name" : "wj555", "age" : 33, "addr" : "unit states" }
{ "_id" : ObjectId("5b8cc40798cd08437710c6ff"), "name" : "wj878ee", "age" : 33, "addr" : "unit states" }
{ "_id" : ObjectId("5b8cc40798cd08437710c700"), "name" : "wj", "age" : 67, "addr" : "russain", "log" : true }
{ "_id" : ObjectId("5b8f33a9dfb2a160dbb01a84"), "name" : "newuser0", "age" : 23, "addr" : "china lubei" }
{ "_id" : ObjectId("5b8f33b5dfb2a160dbb01a85"), "name" : "newuser", "age" : 88, "addr" : "china tangshan " }
{ "_id" : ObjectId("5b8f33b5dfb2a160dbb01a86"), "name" : "newuser2", "age" : 22, "addr" : "china qianxi " }
{ "_id" : ObjectId("5b8f3913dfb2a160dbb01a88"), "name" : "1111", "age" : 44, "addr" : "china" }
{ "_id" : ObjectId("5b8f392adfb2a160dbb01a89"), "name" : "2111", "age" : 44, "addr" : "china tangshan" }
{ "_id" : ObjectId("5b8f393fdfb2a160dbb01a8a"), "name" : "3111", "age" : 32, "addr" : "china tangshan lubei" }
{ "_id" : ObjectId("5b8f3950dfb2a160dbb01a8b"), "name" : "www", "age" : 22, "addr" : "china tangshan lubei" }
> db.user.aggregate({$group:{"_id":"$name",count:{$sum:1}}});
{ "_id" : "wwwkjfd", "count" : 1 }
{ "_id" : "jjj", "count" : 1 }
{ "_id" : "ggg", "count" : 1 }
{ "_id" : "wj878ee", "count" : 1 }
{ "_id" : "1111", "count" : 1 }
{ "_id" : "wj", "count" : 3 }
{ "_id" : "newuser2", "count" : 1 }
{ "_id" : "w543rr", "count" : 1 }
{ "_id" : "3111", "count" : 1 }
{ "_id" : "wj555", "count" : 1 }
{ "_id" : "www", "count" : 1 }
{ "_id" : "2111", "count" : 1 }
{ "_id" : "newuser0", "count" : 1 }
2：$avg:value ：返回每个分组的平均值
db.user.aggregate({$group:{"_id":"$name",pingjuzhi:{$avg:"$age"}}});
{ "_id" : "wwwkjfd", "pingjuzhi" : 22 }
{ "_id" : "jjj", "pingjuzhi" : 54 }
{ "_id" : "ggg", "pingjuzhi" : 55 }
{ "_id" : "wj878ee", "pingjuzhi" : 33 }
{ "_id" : "1111", "pingjuzhi" : 44 }
{ "_id" : "wj", "pingjuzhi" : 48 }
{ "_id" : "newuser2", "pingjuzhi" : 22 }
{ "_id" : "w543rr", "pingjuzhi" : 32 }
{ "_id" : "3111", "pingjuzhi" : 32 }
{ "_id" : "wj555", "pingjuzhi" : 33 }
{ "_id" : "www", "pingjuzhi" : 22 }
{ "_id" : "2111", "pingjuzhi" : 44 }
{ "_id" : "newuser0", "pingjuzhi" : 23 }
{ "_id" : "newuser", "pingjuzhi" : 88 }
3：$max:expr ：返回分组内的最大值
4：$min:expr ：返回分组内的最小值
db.user.aggregate({$group:{"_id":"$name",pingjuzhi:{$max:"$age"}}});
{ "_id" : "wwwkjfd", "pingjuzhi" : 22 }
{ "_id" : "jjj", "pingjuzhi" : 54 }
{ "_id" : "ggg", "pingjuzhi" : 55 }
{ "_id" : "wj878ee", "pingjuzhi" : 33 }
{ "_id" : "1111", "pingjuzhi" : 44 }
{ "_id" : "wj", "pingjuzhi" : 67 }
{ "_id" : "newuser2", "pingjuzhi" : 22 }
{ "_id" : "w543rr", "pingjuzhi" : 32 }
{ "_id" : "3111", "pingjuzhi" : 32 }
{ "_id" : "wj555", "pingjuzhi" : 33 }
{ "_id" : "www", "pingjuzhi" : 22 }
{ "_id" : "2111", "pingjuzhi" : 44 }
{ "_id" : "newuser0", "pingjuzhi" : 23 }
{ "_id" : "newuser", "pingjuzhi" : 88 }
5：$first:expr ：返回分组的第一个值，忽略其他的值，一般只有排序后，明确知道数据顺序的时候，这个操作才有意义
6：$last:expr ：与上面一个相反，返回分组的最后一个值
db.user.aggregate({$group:{"_id":"$name",firstwendang:{$first:"$age"}}});
{ "_id" : "wwwkjfd", "firstwendang" : 22 }
{ "_id" : "jjj", "firstwendang" : 54 }
{ "_id" : "ggg", "firstwendang" : 55 }
{ "_id" : "wj878ee", "firstwendang" : 33 }
{ "_id" : "1111", "firstwendang" : 44 }
{ "_id" : "wj", "firstwendang" : 44 }
{ "_id" : "newuser2", "firstwendang" : 22 }
{ "_id" : "w543rr", "firstwendang" : 32 }
{ "_id" : "3111", "firstwendang" : 32 }
{ "_id" : "wj555", "firstwendang" : 33 }
{ "_id" : "www", "firstwendang" : 22 }
{ "_id" : "2111", "firstwendang" : 44 }
{ "_id" : "newuser0", "firstwendang" : 23 }
{ "_id" : "newuser", "firstwendang" : 88 }
7：$addToSet:expr ：如果当前数组中不包含expr，那就将它加入到数组中 会过滤掉重复的数据 行变列
8：$push:expr：把expr加入到数组中 不会过滤掉重复的数据
db.user.aggregate({$group:{"_id":"$name",zuzhuang_set:{$addToSet:"$age"}}});
{ "_id" : "wwwkjfd", "zuzhuang_set" : [ 22 ] }
{ "_id" : "jjj", "zuzhuang_set" : [ 54 ] }
{ "_id" : "ggg", "zuzhuang_set" : [ 55 ] }
{ "_id" : "wj878ee", "zuzhuang_set" : [ 33 ] }
{ "_id" : "1111", "zuzhuang_set" : [ 44 ] }
{ "_id" : "wj", "zuzhuang_set" : [ 67, 33, 44 ] }
{ "_id" : "newuser2", "zuzhuang_set" : [ 22 ] }
{ "_id" : "w543rr", "zuzhuang_set" : [ 32 ] }
{ "_id" : "3111", "zuzhuang_set" : [ 32 ] }
{ "_id" : "wj555", "zuzhuang_set" : [ 33 ] }
{ "_id" : "www", "zuzhuang_set" : [ 22 ] }
{ "_id" : "2111", "zuzhuang_set" : [ 44 ] }
{ "_id" : "newuser0", "zuzhuang_set" : [ 23 ] }
{ "_id" : "newuser", "zuzhuang_set" : [ 88 ] }
拆分命令：$unwind
用来把数组中的每个值拆分成为单独的文档。 列变行
$sort 排序命令：
可以根据任何字段进行排序，与普通查询中的语法相同。如果要对大量的文档进行排序，强烈建议在管道的第一个阶段进行排序，这时可以使用索引。
常见聚合函数
1：count：用于返回集合中文档的数量
2：distinct：找出给定键的所有不同值，使用时必须指定集合和键，例如：
db.runCommand({"distinct":"users","key":"userId"});
找出考80分以上的课程门数最多的3个学生
是用聚合框架来完成功能的步骤
1：找到所有考了80分以上的学生，不区分课程
{"$match":{"score":{$gte:80}}}
2：将每个学生的名字投影出来
{"$project":{"studentId":1}}
3：对学生的名字排序，某个学生的名字出现一次，就给他加1
{"$group":{"_id":"$studentId","count":{"$sum":1}}}
4：对结果集按照count进行降序排列
{"$sort":{"count":-1}}
5：返回前面的3条数据
{"$limit":3}
最终执行的语句就是：
db.scores.aggregate({$match:{score:{$gt:80}}},{$project:{stuid:1}},{$group:{"_id":"$stuid",count:{$sum:1}}},{$sort:{count:-1}},{$limit:3});
{ "_id" : "s62", "count" : 3 }
{ "_id" : "s79", "count" : 2 }
{ "_id" : "s85", "count" : 2 }
如有疑问 请留言 欢迎提供建议
评论已有 0 条