美文网首页
MongoDB聚合aggregate

MongoDB聚合aggregate

作者: Eve0 | 来源:发表于2017-08-27 13:48 被阅读0次

数据准备

db.emps.insertMany([
{name:'王铁锤',job:'职员',salary:3000,deptno:1001},
{name:'王磊',job:'职员',salary:3000,deptno:1001},
{name:'李丹',job:'经理',salary:9000,deptno:1001},
{name:'赵晓梅',job:'经理',salary:9560,deptno:1001},
{name:'钱忠国',job:'职员',salary:4000,deptno:1001},
{name:'孙悟空',job:'职员',salary:6000,deptno:1001},
{name:'张翠花',job:'职员',salary:3000,deptno:1002},
{name:'岳绮',job:'职员',salary:5000,deptno:1002},
{name:'小张',job:'经理',salary:7000,deptno:1002},
{name:'王明',job:'经理',salary:7560,deptno:1002},
{name:'张伟',job:'职员',salary:6000,deptno:1002},
{name:'冯小帅',job:'职员',salary:5000,deptno:1002},
{name:'周世博',job:'职员',salary:5000,deptno:1002},
{name:'王强',job:'老板',salary:14000,deptno:0},
]);

$group

  • 查询每个职位的总工资:
    db.emps.aggregate([{"$group":{_id:'$job',total:{$sum:'$salary'}}}]);
    显示结果:
{ "_id" : "老板", "total" : 14000 }
{ "_id" : "经理", "total" : 33120 }
{ "_id" : "职员", "total" : 40000 }
  • 每个职位的平均工资:
    db.emps.aggregate([{"$group":{_id:'$job',avg:{$avg:'$salary'}}}]);
    显示结果:
{ "_id" : "老板", "avg" : 14000 }
{ "_id" : "经理", "avg" : 8280 }
{ "_id" : "职员", "avg" : 4444.444444444444 }
  • 求出每个职位的最高与最低工资:
    db.emps.aggregate([{"$group":{_id:"$job",max:{"$max":'$salary'},min:{"$min":'$salary'}}}]);
    显示结果:
{ "_id" : "老板", "max" : 14000, "min" : 14000 }
{ "_id" : "经理", "max" : 9560, "min" : 7000 }
{ "_id" : "职员", "max" : 6000, "min" : 3000 }
  • 计算出每个职位的工资数据(数组形式显示)
    db.emps.aggregate([{"$group":{_id:"$job",salary:{'$push':'$salary'}}}]);
    显示结果:
{ "_id" : "老板", "salary" : [ 14000 ] }
{ "_id" : "经理", "salary" : [ 9000, 9560, 7000, 7560 ] }
{ "_id" : "职员", "salary" : [ 3000, 3000, 4000, 6000, 3000, 5000, 
6000, 5000, 5000 ] }
  • 计算每个职位的人员
    db.emps.aggregate([{"$group":{_id:"$job",persons:{"$push":"$name"}}}]);
    显示结果:
{ "_id" : "老板", "persons" : [ "王强" ] }
{ "_id" : "经理", "persons" : [ "李丹", "赵晓梅", "小张", "王明" ] }
{ "_id" : "职员", "persons" : [ "王铁锤", "王磊", "钱忠国", "孙悟空",
 "张翠花", "岳绮", "张伟", "冯小帅", "周世博" ] }

$project

  • 显示工资大于4000小于9000的员工,显示name,salary,job字段
 db.emps.aggregate([
  {$match:{"salary":{"$gt":4000,"$lt":9000}}}, 
  {"$project":{_id:0,name:1,salary:1,job:1}}
]);

相关文章

网友评论

      本文标题:MongoDB聚合aggregate

      本文链接:https://www.haomeiwen.com/subject/mhxvdxtx.html