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 생성
])
> 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" }
>
> 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" }
>
> 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 }
>
> 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 }
>
> 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 }
> db.emp.aggregate([ { '$group' : {'_id':'$deptno', 'sal':{'$sum':"$sal"} } } ] );
{ "_id" : 20, "sal" : 10875 }
{ "_id" : 30, "sal" : 9400 }
{ "_id" : 10, "sal" : 8750 }
>
> 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 }
>
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'} } } } ] );