06.聚合操作

# 01.聚合操作

# 1.1 聚合操作说明

Pipeline:速度快于MapReduce，单个的聚合操作耗费的内存不能超过20%，返回的结果集：限制在16M
MapReduce：多个Server上并行计算

# 1.2 $match和$project，只显示指定列

#1、$match和$project
$match: 过滤进入PipeLine的数据
$project:指定提取的列，其中： 1表示提取列  0不提取
    
#查询部门id=10，只显示ename、sal、deptno
db.emp.aggregate(
  {$match:{"deptno":{$eq:10}}},
  {$project:{"ename":1,"sal":1,"deptno":1}}
);

'''
{ "_id" : 7782, "ename" : "CLARK", "sal" : 2450, "deptno" : 10 }
{ "_id" : 7839, "ename" : "KING", "sal" : 8000, "deptno" : 10 }
{ "_id" : 7934, "ename" : "MILLER", "sal" : 1300, "deptno" : 10 }
'''

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 1.3 使用$group: 求每个部门的工资总额

db.emp.aggregate(
  {$project:{"sal":1,"deptno":1}},
  {$group:{"_id":"$deptno",salTotal:{$sum:"$sal"}}}
);

'''
{ "_id" : 10, "salTotal" : 11750 }
{ "_id" : 30, "salTotal" : 9400 }
{ "_id" : 20, "salTotal" : 10875 }
'''

1
2
3
4
5
6
7
8
9
10

# 1.4 按照部门，不同的职位求工资总额

#3、按照部门，不同的职位求工资总额
#select deptno,job,sum(sal) from emp group by deptno,job;
db.emp.aggregate(
  {$project:{"job":1,"sal":1,"deptno":1}},
  {$group:{"_id":{"deptno":"$deptno","job":"$job"},salTotal:{$sum:"$sal"}}}
); 

'''
{ "_id" : { "deptno" : 20, "job" : "ANALYST" }, "salTotal" : 6000 }
{ "_id" : { "deptno" : 30, "job" : "SALESMAN" }, "salTotal" : 5600 }
{ "_id" : { "deptno" : 20, "job" : "CLERK" }, "salTotal" : 1900 }
'''

1
2
3
4
5
6
7
8
9
10
11
12

上次更新: 2024/4/1 16:53:26

← 05.复合查询 01.TiDB语法→