[mongoDB] Aggregate Pipeline

Aggregate pipeline

https://docs.mongodb.com/manual/reference/operator/aggregation-pipeline/
dept collection에서 전체 다 가져와라
pipeline들이 array화해서 들어감

db.aggrgate([
    	$project : [select] 내가 원하는 컬럼만 가져오겠다. 
        $limit 	 : [n개 top(n)]
        $match   : [where]
        $group 	 : [group by]
        $sort 	 : [order by]
        $lookup  : Performs a left outer join
        
        	   from	: [외부 Collection]	
               	   localFiled : [원본.field]
                   foreignFiled : [외부.field]
                   as "신규 field명"
                    
        $out 	 : collection 생성 
        ])

1. project : 원하는 컬럼만 선택

> db.dept.aggregate([ { '$project': {'dname':1 } }]);

{ "_id" : ObjectId("61e6354bac1bdb3f3eff3476"), "dname" : "SALES" }
{ "_id" : ObjectId("61e6354bac1bdb3f3eff3477"), "dname" : "RESEARCH" }
{ "_id" : ObjectId("61e6354bac1bdb3f3eff3478"), "dname" : "OPERATIONS" }
{ "_id" : ObjectId("61e6354bac1bdb3f3eff3479"), "dname" : "IT" }
{ "_id" : ObjectId("61e6354bac1bdb3f3eff347a"), "dname" : "ACCOUNTING" }
>

> db.dept.aggregate([ { '$project':{'deptno':1,'dname':1,'_id':0 } } ] );
{ "deptno" : 30, "dname" : "SALES" }
{ "deptno" : 20, "dname" : "RESEARCH" }
{ "deptno" : 40, "dname" : "OPERATIONS" }
{ "deptno" : 50, "dname" : "IT" }
{ "deptno" : 10, "dname" : "ACCOUNTING" }
>

2. limit : 원하는 개수만 확인

> db.dept.aggregate([ { '$project':{'dname':1,'_id':0 } },{'$limit':3} ] );
{ "dname" : "SALES" }
{ "dname" : "RESEARCH" }
{ "dname" : "OPERATIONS" }
>

> db.dept.aggregate([ { '$project':{'deptno':1,'dname':1,'_id':0 }},{'$limit':3}]);
{ "deptno" : 30, "dname" : "SALES" }
{ "deptno" : 20, "dname" : "RESEARCH" }
{ "deptno" : 40, "dname" : "OPERATIONS" }
>

3. count : grouping + count

> db.dept.aggregate([ { '$group' : {'_id':0, 'count':{$sum:1} }}] );
{ "_id" : 0, "count" : 5 }

> db.emp.aggregate([ { '$group' : {'_id':0, 'count':{$sum:1}  } } ] );
{ "_id" : 0, "count" : 14 }
>

• Q1. count별 직원이 몇 명인지 구함

> db.emp.aggregate([ { '$group' : {'_id':'$empno', 'count':{$sum:1}  } } ] );
{ "_id" : 7566, "count" : 1 }
{ "_id" : 7839, "count" : 1 }
{ "_id" : 7782, "count" : 1 }
{ "_id" : 7844, "count" : 1 }
{ "_id" : 7788, "count" : 1 }
{ "_id" : 7902, "count" : 1 }
{ "_id" : 7900, "count" : 1 }
{ "_id" : 7654, "count" : 1 }
{ "_id" : 7876, "count" : 1 }
{ "_id" : 7698, "count" : 1 }
{ "_id" : 7934, "count" : 1 }
{ "_id" : 7521, "count" : 1 }
{ "_id" : 7499, "count" : 1 }
{ "_id" : 7369, "count" : 1 }
>

• Q2. job별 인원수

> db.emp.aggregate([ { '$group' : {'_id':'$job', 'count':{'$sum':1}  } } ] );
{ "_id" : "PRESIDENT", "count" : 1 }
{ "_id" : "SALESMAN", "count" : 4 }
{ "_id" : "MANAGER", "count" : 3 }
{ "_id" : "CLERK", "count" : 4 }
{ "_id" : "ANALYST", "count" : 2 }

• Q3. deptno별 급여합계

> db.emp.aggregate([ { '$group' : {'_id':'$deptno', 'sal':{'$sum':"$sal"}  } } ] );
{ "_id" : 20, "sal" : 10875 }
{ "_id" : 30, "sal" : 9400 }
{ "_id" : 10, "sal" : 8750 }
>

• Q4. job별 급여 평균

> db.emp.aggregate([ { '$group' : {'_id':'$job', 'avg_sal':{'$avg':"$sal"}  } } ] );
{ "_id" : "SALESMAN", "avg_sal" : 1400 }
{ "_id" : "ANALYST", "avg_sal" : 3000 }
{ "_id" : "CLERK", "avg_sal" : 1037.5 }
{ "_id" : "MANAGER", "avg_sal" : 2758.3333333333335 }
{ "_id" : "PRESIDENT", "avg_sal" : 5000 }
>

4. lookup & out ( join 역할)

• dept.deptno -- left join --> emp.deptno

db.dept.aggregate( [ { $lookup : { from : 'emp',
                                   localFiled : 'deptno',
                                   foreignFiled : 'deptno',
			      	   as : "dno" } },

		     { $out :'dept_emp_join' } ] ) ; 

>
db.dept.aggregate( [ { $lookup: { from: 'empdb',  localField: 'deptno', foreignField: 'deptno',  as: "dno" }  } , { $out: 'deJoin' } ] );
> db.deJoin.find()
{ "_id" : ObjectId("61e5bdc373501b26566bcd49"), "Deptno" : 10, "dname" : "ACCOUNTING", "loc" : "NEW YORK", "dno" : [ ] }
{ "_id" : ObjectId("61e5bdc373501b26566bcd4a"), "Deptno" : 20, "dname" : "RESEARCH", "loc" : "DALLAS", "dno" : [ ] }
{ "_id" : ObjectId("61e5bdc373501b26566bcd4b"), "Deptno" : 30, "dname" : "SALES", "loc" : "CHICAGO", "dno" : [ ] }
{ "_id" : ObjectId("61e5bdc373501b26566bcd4c"), "Deptno" : 40, "dname" : "OPERATIONS", "loc" : "BOSTON", "dno" : [ ] }
{ "_id" : ObjectId("61e5bdc373501b26566bcd4d"), "Deptno" : 50, "dname" : "", "loc" : "50", "dno" : [ ] }
>

Q1. ACCOUNTING 부서 소속 직원들의 사번, 이름, 급여?

[MongoDB NOSQL]

-1-
FROM dejoin
(WHERE dname='ACCOUNTING')
(SELECT emptno, ename, sal)

-2-
db.deJoin.aggregate(
(WHERE dname='ACCOUNTING') <- 조건 (where, $match)
(SELECT emptno, ename, sal) <- 선택 (select, $project)

-3-
db.deJoin.aggregate(
{$match : {dname : 'ACCOUNTING'}} <- key, value
{$project : {emptno:1, ename:1, sal:1 }} <- key, value 
)

-4-
db.deJoin.aggregate( [{'$match' : {'dname' : 'ACCOUNTING'}} , {'$project' : {'dno.emptno':1, 'dno.ename':1, 'dno.sal':1 } } ] );


[RDB SQL]
SELECT deptno, dname, sal
FROM dejoin
WHERE dname='ACCOUNTING';

[R]
dejoin
	%>% select deptno, dname, sal
	%>% where dname='ACCOUNTING'

Q2. deJoin에서 dname 개수?

select deptno, count('korea')
from emp
group by deptno;

-1-
select : sum

select sum(값)
from deJoin
group by 1

-2-
$group : count

select count(값)
from deJoin
group by 1

-3-
from : db.deJoin.aggregate([ {$group : {'_id':0, 'count' :{ $sum:1 } } } ] );

db.deJoin.count();
db.deJoin.countDocuments();

> db.deJoin.aggregate([ { '$group' : {'_id':'$dname', 'count':{'$sum':1}  } } ] );
{ "_id" : "RESEARCH", "count" : 1 }
{ "_id" : "SALES", "count" : 1 }
{ "_id" : "OPERATIONS", "count" : 1 }
{ "_id" : "", "count" : 1 }
{ "_id" : "ACCOUNTING", "count" : 1 }
>

> y = [{a:100},{b:200},{c:'hi'},{d:[{e1:11},{e2:22},{e3:33}]},{f:'end'}]
[
        { "a" : 100},
        { "b" : 200 },
        { "c" : "hi"},
        { "d" : [{ "e1" : 11 },{"e2" : 22}, {"e3" : 33 } ]},
        { "f" : "end"}
]

> y[3]
{ "d" : [ {"e1" : 11}, {"e2" : 22},{ "e3" : 33}] }

> y[3].d
[ { "e1" : 11 }, { "e2" : 22 }, { "e3" : 33 } ]
> y[3].d.length
3
> y[3].d[2]
{ "e3" : 33 }
> y[3].d["e3"]
> y[3].d.e3
> y[3].d[2].33
uncaught exception: SyntaxError: unexpected token: numeric literal :
@(shell):1:9
> y[3].d[2].e3
33
>
> db.deJoin.find({'dname':"ACCOUNTING"},{});
{ "_id" : ObjectId("61e5bdc373501b26566bcd49"), "Deptno" : 10, "dname" : "ACCOUNTING", "loc" : "NEW YORK", "dno" : [ ] }
>

Q2-1. 직원들 인원수

unwind : 저 위치에 간다 

db.deJoin.aggregate([ 
	{$unwind : '$dno'},
	{$group : {'_id': 0, 'count' :{ $sum: 1 } } } ] );

db.deJoin.aggregate([ {$unwind: '$dno'},{$group : {'_id':0, 'count' :{ $sum:1} } } ] );

Q3. deJoin에서 dname 별 인원수

db.deJoin.aggregate([ 
	{$unwind : '$dno'},
	{$group : {'_id': '$dname', 'count' :{ $sum: 1 } } } ] );

db.deJoin.aggregate([  {$unwind : '$dno'}, {$group : {'_id': '$dname', 'count' :{ $sum: 1 } } } ] );

> db.deJoin.aggregate([ {$unwind : '$dno'}, {$group : {'_id': '$dname', 'count' :{ $sum: 1 } } } ] );

{ "_id" : "RESEARCH", "count" : 15 }
{ "_id" : "SALES", "count" : 15 }
{ "_id" : "OPERATIONS", "count" : 15 }
{ "_id" : "", "count" : 15 }
{ "_id" : "ACCOUNTING", "count" : 15 }
>

> db.deJoin.aggregate([   {$group : {'_id': '$dname', 'count' :{ '$sum':{'$size':'$dno'} } } } ] );

{ "_id" : "RESEARCH", "count" : 15 }
{ "_id" : "SALES", "count" : 15 }
{ "_id" : "OPERATIONS", "count" : 15 }
{ "_id" : "", "count" : 15 }
{ "_id" : "ACCOUNTING", "count" : 15 }
>

Q4. deJoin에서 dname 별 급여합계

unwind: 뒤에 dno를 가리킨다는 의미 
sum : 1은 1씩 더하라는 의미 => dno.sal을 하나씩 더해라로 변경

> db.deJoin.aggregate([ {$unwind: '$dno'},{$group : {'_id':'$dname', 'count' :{ $sum:'$dno.sal'} } } ] );

둘의 차이 -> layer의 차이 

위치를 인식하지 못해 count가 제대로 안됨 
db.deJoin.aggregate([ {$group : {'_id':'$dname','count' :{ '$sum' : '$dno.sal'} } } ] );

위치를 인식하게 해 count를 제대로 인식 
db.deJoin.aggregate([ {$group : {'_id':'$dname','count' :{ '$sum' : {'$sum':'$dno.sal'} } } } ] );