MongoDB | 聚合 aggregate
来源:互联网 发布:淘宝内部优惠群怎么做 编辑:程序博客网 时间:2024/05/25 19:56
聚合 aggregate
mongoDB官网
mongoDB中文网
- 聚合(aggregate)主要用于计算数据,类似sql中的sum()、avg()
- 语法
db.集合名称.aggregate([{管道:{表达式}}])
管道
- 管道在Unix和Linux中一般用于将当前命令的输出结果作为下一个命令的输入
ps -aux | grep mongo
- 在mongodb中,管道具有同样的作用,文档处理完毕后,通过管道进行下一次处理,管道操作是可以重复的。
- 常用管道
- $group:将集合中的文档分组,可用于统计结果
- $match:过滤数据,只输出符合条件的文档
- $project:修改输入文档的结构,如重命名、增加、删除字段、创建计算结果
- $sort:将输入文档排序后输出
- $limit:限制聚合管道返回的文档数
- $skip:跳过指定数量的文档,并返回余下的文档
- $unwind:将数组类型的字段进行拆分
表达式
- 处理输入文档并输出
- 表达式是无状态的,只能用于计算当前聚合管道的文档,不能处理其它的文档。
- 语法
#列名要加$表达式:'$列名'
- 常用表达式
- $sum:计算总和,$sum:1同count表示计数
- $avg:计算平均值
- $min:获取最小值
- $max:获取最大值
- $push:在结果文档中插入值到一个数组中
- $first:根据资源文档的排序获取第一个文档数据
- $last:根据资源文档的排序获取最后一个文档数据
示例数据
> db.stu.find(){ "_id" : ObjectId("59b8ed20fdefef4e475a942f"), "name" : "立夏", "gender" : 0, "age" : 25 }{ "_id" : ObjectId("59b8ed4efdefef4e475a9430"), "name" : "王子", "gender" : 1, "age" : 20 }{ "_id" : ObjectId("59b8eda8fdefef4e475a9431"), "name" : "上官玉", "gender" : 0, "age" : 20 }{ "_id" : ObjectId("59b8ededfdefef4e475a9432"), "name" : "于萌", "gender" : 0, "age" : 15 }{ "_id" : ObjectId("59b8fc98fdefef4e475a9433"), "name" : "夏至", "gender" : 1, "age" : 27 }{ "_id" : ObjectId("59b8fdd2fdefef4e475a9434"), "name" : 0, "gender" : 0, "age" : 21 }{ "_id" : ObjectId("59b8fdd2fdefef4e475a9435"), "name" : 2, "gender" : 1, "age" : 22 }{ "_id" : ObjectId("59b8fdd2fdefef4e475a9436"), "name" : 3, "gender" : 0, "age" : 23 }{ "_id" : ObjectId("59b8fdd2fdefef4e475a9437"), "name" : 4, "gender" : 1, "age" : 25 }{ "_id" : ObjectId("59b8fdd2fdefef4e475a9438"), "name" : 5, "gender" : 0, "age" : 30 }{ "_id" : ObjectId("59b8fdd2fdefef4e475a9439"), "name" : 6 }{ "_id" : ObjectId("59b8fdd2fdefef4e475a943a"), "name" : 7 }{ "_id" : ObjectId("59b8fdd2fdefef4e475a943b"), "name" : 8 }{ "_id" : ObjectId("59b8fdd2fdefef4e475a943c"), "name" : 9 }{ "_id" : ObjectId("59b93a99fdefef4e475a943d"), "name" : 1 }{ "_id" : ObjectId("59ba38110686052694339d66"), "name" : "123" }{ "_id" : ObjectId("59bb76d898a8f12a368878ef"), "gender" : 1, "age" : 20, "name" : "heey" }$group
- 将集合中的文档分组,可用于统计结果
- _id表示分组的依据,使用某个字段的格式为 '$字段'
- $sum:1 表示count计数
- 例:男生、女生的总人数
> db.stu.aggregate([ {$group:{ _id:'$gender', count:{$sum:1}} } ])#结果{ "_id" : null, "count" : 6 }{ "_id" : 1, "count" : 5 }{ "_id" : 0, "count" : 6 }- 例:男生、女生的总年龄
> db.stu.aggregate([ {$group:{ _id:'$gender', sumage:{$sum:'$age'}} } ])#结果{ "_id" : null, "sumage" : 0 }{ "_id" : 1, "sumage" : 114 }{ "_id" : 0, "sumage" : 134 }- 例:男生、女生的平均年龄
> db.stu.aggregate([ {$group:{ _id:'$gender', avg_age:{$avg:'$age'}} } ])#结果{ "_id" : null, "avg_age" : null }{ "_id" : 1, "avg_age" : 22.8 }{ "_id" : 0, "avg_age" : 22.333333333333332 }- 例:男生、女生的最小年龄
> db.stu.aggregate([ {$group:{ _id:'$gender', min_age:{$min:'$age'}} } ])#结果{ "_id" : null, "min_age" : null }{ "_id" : 1, "min_age" : 20 }{ "_id" : 0, "min_age" : 15 }Group by null
- 将集合中所有文档分为一组
- 例:求学生总人数、平均年龄
> db.stu.aggregate([ {$group:{ _id:null, count:{$sum:1}, avg_age:{$avg:'$age'}} } ])#结果{ "_id" : null, "count" : 17, "avg_age" : 22.545454545454547 }透视数据 $push
- 例:统计学生性别及学生姓名
> db.stu.aggregate([ {$group:{ _id:'$gender', name:{$push:'$name'}} } ])#结果{ "_id" : null, "name" : [ 6, 7, 8, 9, 1, "123" ] }{ "_id" : 1, "name" : [ "王子", "夏至", 2, 4, "heey" ] }{ "_id" : 0, "name" : [ "立夏", "上官玉", "于萌", 0, 3, 5 ] }- 使用$$ROOT可以将文档所有内容加入到结果集的数组中,代码如下
> db.stu.aggregate([ { $group:{ _id:'$gender', name:{$push:'$$ROOT'}} } ]).pretty()#结果{"_id" : null,"name" : [{"_id" : ObjectId("59b8fdd2fdefef4e475a9439"),"name" : 6},{"_id" : ObjectId("59b8fdd2fdefef4e475a943a"),"name" : 7},......]}{"_id" : 1,"name" : [{"_id" : ObjectId("59b8ed4efdefef4e475a9430"),"name" : "王子","gender" : 1,"age" : 20},{"_id" : ObjectId("59b8fc98fdefef4e475a9433"),"name" : "夏至","gender" : 1,"age" : 27},......]}{"_id" : 0,"name" : [{"_id" : ObjectId("59b8ed20fdefef4e475a942f"),"name" : "立夏","gender" : 0,"age" : 25},{"_id" : ObjectId("59b8ededfdefef4e475a9432"),"name" : "于萌","gender" : 0,"age" : 15},......]}$match
- 用于过滤数据,只输出符合条件的文档
- 使用MongoDB的标准查询操作
- 例:查询年龄大于20的学生
> db.stu.aggregate([ {$match:{ age:{$gt:20}} } ])#结果{ "_id" : ObjectId("59b8ed20fdefef4e475a942f"), "name" : "立夏", "gender" : 0, "age" : 25 }{ "_id" : ObjectId("59b8fc98fdefef4e475a9433"), "name" : "夏至", "gender" : 1, "age" : 27 }{ "_id" : ObjectId("59b8fdd2fdefef4e475a9434"), "name" : 0, "gender" : 0, "age" : 21 }{ "_id" : ObjectId("59b8fdd2fdefef4e475a9435"), "name" : 2, "gender" : 1, "age" : 22 }{ "_id" : ObjectId("59b8fdd2fdefef4e475a9436"), "name" : 3, "gender" : 0, "age" : 23 }{ "_id" : ObjectId("59b8fdd2fdefef4e475a9437"), "name" : 4, "gender" : 1, "age" : 25 }{ "_id" : ObjectId("59b8fdd2fdefef4e475a9438"), "name" : 5, "gender" : 0, "age" : 30 }- 例:查询年龄大于20的男生、女生人数
> db.stu.aggregate([ {$match:{age:{$gt:20}}}, {$group:{_id:'$gender',count:{$sum:1}}} ])#结果{ "_id" : 1, "count" : 3 }{ "_id" : 0, "count" : 4 }$project
- 修改输入文档的结构,如重命名、增加、删除字段、创建计算结果
- 例:查询学生的姓名、年龄,_id是默认显示
> db.stu.aggregate([ {$project:{ _id:0, name:1, age:1} }])#结果{ "name" : "立夏", "age" : 25 }{ "name" : "王子", "age" : 20 }{ "name" : "上官玉", "age" : 20 }{ "name" : "于萌", "age" : 15 }{ "name" : "夏至", "age" : 27 }{ "name" : 0, "age" : 21 }{ "name" : 2, "age" : 22 }{ "name" : 3, "age" : 23 }{ "name" : 4, "age" : 25 }{ "name" : 5, "age" : 30 }......- 例:查询男生、女生人数,输出人数
> db.stu.aggregate([ {$group:{_id:'$gender',count:{$sum:1}}}, {$project:{_id:0}}])#结果{ "count" : 6 }{ "count" : 5 }{ "count" : 6 }> db.stu.aggregate([ {$group:{_id:'$gender',count:{$sum:1}}}, {$project:{_id:0,count:1}}])#结果{ "count" : 6 }{ "count" : 5 }{ "count" : 6 }$sort
- 将输入文档排序后输出
- 1:升序 -1:降序
- 例:查询学生信息,按年龄升序
> db.stu.aggregate([ {$sort:{age:1}}])#结果{ "_id" : ObjectId("59b8fdd2fdefef4e475a9439"), "name" : 6 }{ "_id" : ObjectId("59b8fdd2fdefef4e475a943a"), "name" : 7 }{ "_id" : ObjectId("59b8fdd2fdefef4e475a943b"), "name" : 8 }{ "_id" : ObjectId("59b8fdd2fdefef4e475a943c"), "name" : 9 }{ "_id" : ObjectId("59b93a99fdefef4e475a943d"), "name" : 1 }{ "_id" : ObjectId("59ba38110686052694339d66"), "name" : "123" }{ "_id" : ObjectId("59b8ededfdefef4e475a9432"), "name" : "于萌", "gender" : 0, "age" : 15 }{ "_id" : ObjectId("59b8ed4efdefef4e475a9430"), "name" : "王子", "gender" : 1, "age" : 20 }{ "_id" : ObjectId("59b8eda8fdefef4e475a9431"), "name" : "上官玉", "gender" : 0, "age" : 20 }{ "_id" : ObjectId("59bb76d898a8f12a368878ef"), "gender" : 1, "age" : 20, "name" : "heey" }{ "_id" : ObjectId("59b8fdd2fdefef4e475a9434"), "name" : 0, "gender" : 0, "age" : 21 }{ "_id" : ObjectId("59b8fdd2fdefef4e475a9435"), "name" : 2, "gender" : 1, "age" : 22 }{ "_id" : ObjectId("59b8fdd2fdefef4e475a9436"), "name" : 3, "gender" : 0, "age" : 23 }{ "_id" : ObjectId("59b8ed20fdefef4e475a942f"), "name" : "立夏", "gender" : 0, "age" : 25 }{ "_id" : ObjectId("59b8fdd2fdefef4e475a9437"), "name" : 4, "gender" : 1, "age" : 25 }{ "_id" : ObjectId("59b8fc98fdefef4e475a9433"), "name" : "夏至", "gender" : 1, "age" : 27 }{ "_id" : ObjectId("59b8fdd2fdefef4e475a9438"), "name" : 5, "gender" : 0, "age" : 30 }- 例:查询男生、女生人数,按人数降序
> db.stu.aggregate([ {$group:{_id:'$gender',count:{$sum:1}}}, {$sort:{count:-1}}])#结果{ "_id" : null, "count" : 6 }{ "_id" : 0, "count" : 6 }{ "_id" : 1, "count" : 5 }$limit
- 限制聚合管道返回的文档数
- 例:查询2条学生信息
> db.stu.aggregate([ {$limit:2}])#结果{ "_id" : ObjectId("59b8ed20fdefef4e475a942f"), "name" : "立夏", "gender" : 0, "age" : 25 }{ "_id" : ObjectId("59b8ed4efdefef4e475a9430"), "name" : "王子", "gender" : 1, "age" : 20 }$skip
- 跳过指定数量的文档,并返回余下的文档
- 例:查询从第3条开始的学生信息
> db.stu.aggregate([ {$skip:3}])#结果{ "_id" : ObjectId("59b8ededfdefef4e475a9432"), "name" : "于萌", "gender" : 0, "age" : 15 }{ "_id" : ObjectId("59b8fc98fdefef4e475a9433"), "name" : "夏至", "gender" : 1, "age" : 27 }{ "_id" : ObjectId("59b8fdd2fdefef4e475a9434"), "name" : 0, "gender" : 0, "age" : 21 }{ "_id" : ObjectId("59b8fdd2fdefef4e475a9435"), "name" : 2, "gender" : 1, "age" : 22 }......- 例:查询从第2条开始的5条学生信息
> db.stu.aggregate([ {$skip:1}, {$limit:5}])#结果{ "_id" : ObjectId("59b8ed4efdefef4e475a9430"), "name" : "王子", "gender" : 1, "age" : 20 }{ "_id" : ObjectId("59b8eda8fdefef4e475a9431"), "name" : "上官玉", "gender" : 0, "age" : 20 }{ "_id" : ObjectId("59b8ededfdefef4e475a9432"), "name" : "于萌", "gender" : 0, "age" : 15 }{ "_id" : ObjectId("59b8fc98fdefef4e475a9433"), "name" : "夏至", "gender" : 1, "age" : 27 }{ "_id" : ObjectId("59b8fdd2fdefef4e475a9434"), "name" : 0, "gender" : 0, "age" : 21 }- 例:统计男生、女生人数,按人数升序,取第二条数据
> db.stu.aggregate([ {$group:{_id:'$gender',count:{$sum:1}}}, {$sort:{count:1}} ])#结果{ "_id" : 1, "count" : 5 }{ "_id" : null, "count" : 6 }{ "_id" : 0, "count" : 6 }> db.stu.aggregate([ {$group:{_id:'$gender',count:{$sum:1}}}, {$sort:{count:1}}, {$skip:1}, {$limit:2} ])#结果{ "_id" : null, "count" : 6 }{ "_id" : 0, "count" : 6 }- 注意顺序:先写skip,再写limit
- 使用sort与skip和limit合用时,结果有时会错乱
- 例
> db.stu.aggregate([{$sort:{age:1}},{$skip:2},{$limit:3}])#正确结果应该是 name:8 9 1{ "_id" : ObjectId("59b93a99fdefef4e475a943d"), "name" : 1 }{ "_id" : ObjectId("59b8fdd2fdefef4e475a943c"), "name" : 9 }{ "_id" : ObjectId("59b8fdd2fdefef4e475a9439"), "name" : 6 }$unwind
- 将文档中的某一个数组类型字段拆分成多条,每条包含数组中的一个值
语法1
- 对某字段值进行拆分
db.集合名称.aggregate([{$unwind:'$字段名称'}])
- 构造数据
db.t2.insert({_id:1,item:'t-shirt',size:['S','M','L','XL']})> db.t1.find(){ "_id" : 1, "item" : "t-shirt", "size" : [ "S", "M", "L" ,"XL"] }
- 查询
> db.t2.aggregate({$unwind:'$size'}){ "_id" : 1, "item" : "t-shirt", "size" : "S" }{ "_id" : 1, "item" : "t-shirt", "size" : "M" }{ "_id" : 1, "item" : "t-shirt", "size" : "L" }{ "_id" : 1, "item" : "t-shirt", "size" : "XL" }语法2
- 对某字段值进行拆分
- 处理空数组、非数组、无字段、null情况
db.inventory.aggregate([{ $unwind:{ path:'$字段名称', preserveNullAndEmptyArrays:#防止数据丢失 }}])- 构造数据
db.t3.insert([{ "_id" : 1, "item" : "a", "size": [ "S", "M", "L"] },{ "_id" : 2, "item" : "b", "size" : [ ] },{ "_id" : 3, "item" : "c", "size": "M" },{ "_id" : 4, "item" : "d" },{ "_id" : 5, "item" : "e", "size" : null }])- 使用语法1查询
> db.t3.aggregate([{$unwind:'$size'}]){ "_id" : 1, "item" : "a", "size" : "S" }{ "_id" : 1, "item" : "a", "size" : "M" }{ "_id" : 1, "item" : "a", "size" : "L" }{ "_id" : 3, "item" : "c", "size" : "M" }- 查看查询结果,发现对于空数组、无字段、null的文档,都被丢弃了
- 问:如何能不丢弃呢?
- 答:使用语法2查询
> db.t3.aggregate([{$unwind:{path:'$sizes',preserveNullAndEmptyArrays:true}}]){ "_id" : 1, "item" : "a", "size" : [ "S", "M", "L" ] }{ "_id" : 2, "item" : "b", "size" : [ ] }{ "_id" : 3, "item" : "c", "size" : "M" }{ "_id" : 4, "item" : "d" }{ "_id" : 5, "item" : "e", "size" : null }
阅读全文
0 0
- mongodb aggregate 聚合操作
- MongoDB aggregate聚合
- MongoDB aggregate 聚合管道
- mongodb 聚合aggregate 乘法
- MongoDB中的聚合 aggregate
- MongoDB aggregate() 聚合
- MongoDB | 聚合 aggregate
- MongoDB的聚合(aggregate)
- MongoDB的聚合函数 Aggregate
- MongoDB的聚合函数 Aggregate
- mongodb-aggregate(聚合函数)
- mongodb-aggregate聚合日期分组
- ThinkPHP支持MongoDb Aggregate方法聚合管道
- MongoDB aggregate聚合函数的使用
- MongoDB中聚合(aggregate)的使用
- MongoDB aggregate,mapreduce,聚合命令的区别
- Mongodb中数据聚合之聚合管道aggregate
- Mongodb中数据聚合之聚合管道aggregate
- tensorflow学习1
- Springboot架构设计(二)封装
- Linux rpm包制作
- EasyNVR RTSP转RTMP-HLS流媒体服务器前端构建之:通过接口获取实时信息
- Rust 2017 Survey Results
- MongoDB | 聚合 aggregate
- 如何写SysV服务管理脚本
- 图片高度自适应
- SkinSharp函数文档
- SSH远程连接服务器
- 2017 ACM-ICPC 亚洲区(西安赛区)网络赛 Maximum Flow
- CentOS 7 部署zabbix-3.4
- 仿着锤子科技官网进行的一个angular4.0项目~~~
- Git学习日记(4)